JPA: "Member Of" Expression - OpenSQLException

[Former Member]

Hi,

I'm trying to use two "Member Of" expressions for a predicate, as in the following query:

SELECT si FROM SubIn si WHERE (:topicA MEMBER si.topics) OR (:topicB MEMBER si.topics)

The single items (topicA, topicB) which should be tested for membership in a collection are passed as parameters to the query and are proper entities. However, it seems that the constructed query contains syntax errors; I attached the "formatted" error message below. Please let me know if my formatting of the message is distracting and I can attach the plain message. Any hints as to why the final query is not well-formed would be greatly appreciated.

Cheers,

Felix

Caused by: com.sap.sql.log.OpenSQLException: The SQL statement 

"SELECT ... 
WHERE ((?) IN 
(SELECT "A_1"."ID" FROM "TOPICS" "A_1", "J_SI_T" "A_2" WHERE "A_1"."ID" = "A_2"."T_ID" AND "SI"."ID" = "A_2"."SI_ID") 
OR (?) IN 
(SELECT "A_3"."ID" FROM "TOPICS" "A_3", "J_SI_T" "A_4" WHERE "A_3"."ID" = "A_4"."T_ID" AND "SI"."ID" = "A_4"."SI_ID"))" 

contains the syntax error[s]: 
- 1:251 - the IN predicate 
? IN (SELECT "A_1"."ID" FROM "TOPICS" "A_1", "J_SI_T" "A_2" WHERE "A_1"."ID" = "A_2"."T_ID" AND "SI"."ID" = "A_2"."SI_ID")
contains a host variable (parameter marker) as the test value.

- 1:419 - the IN predicate 
? IN (SELECT "A_3"."ID" FROM "TOPICS" "A_3", "J_SI_T" "A_4" WHERE "A_3"."ID" = "A_4"."T_ID" AND "SI"."ID" = "A_4"."SI_ID")
contains a host variable (parameter marker) as the test value.

[adrian_goerler · Accepted solution]

Hi Felix,

thanks for spotting this issue: the generated SQL hits a limitation of Open SQL. I think we'd better generate an EXISTS-subquery instead of the IN-subquery.

As a workaround, I suggest that you use a native query instead:

... where exists (select TOPIC.ID 
                  from TOPIC join J_SI_T on TOPIC.ID = J_SI_T.T_ID 
                  where J_SI_T.SI_ID = SI.ID and TOPIC.ID = ?) or 
          exists (select TOPIC.ID 
                  from TOPIC join J_SI_T on TOPIC.ID = J_SI_T.T_ID 
                  where J_SI_T.SI_ID = SI.ID and TOPIC.ID = ?)

I think this could be simplified to

... where exists (select J_SI_T.SI_ID from where J_SI_T.SI_ID = SI.ID and J_SI_T.T_ID IN (?, ?))

We'll consider to fix this issue.

I hope this helps,

Adrian

[Former Member · Accepted solution]

Thanks, that works -- was afraid I had to use a native query.

~felix