We are having problems with the EJB-QL SQL-92 generator and EJB-QL queries using MEMBER OF.
Initially, we were using the old JDBCEJBQLCompiler for our project, and all our queries at the time worked. We recently switched our project to using the new EJBQLToSQL92Compiler so that we could get a new (complicated) EJB-QL NOT NULL query working (which required us migrating from Oracle 8i to 10g to get SQL-92 support). The switch to SQL-92 got all our queries working, except for 4. The 4 that were failing all have a similar structure, just with different tables. Manually setting these 4 queries to use the old generator makes them work again, but we would really like, for ease of maintenance if nothing else, to use the same generator for everything.
A representative example follows:
SELECT OBJECT(u) FROM User u, Customer cust WHERE cust = ?1 AND cust MEMBER OF u.customers AND u.active = TRUE
SQL92 generator: this tends to return more data than it should (also returns the row for the other active user in our test data that doesn't have the specified customer)
SELECT t0_u.username, t0_u.password, t0_u.active, t0_u.defaultVenue, t0_u.defaultCustomer FROM CU_CUSTOMER t1_cust, SEC_USER t0_u WHERE (t1_cust.name=?) AND EXISTS ( SELECT t2_u_local.username FROM SEC_USER t2_u_local INNER JOIN CU_CUSTOMER_USER t4_u_customers_local_RELATION_ ON t2_u_local.username=t4_u_customers_local_RELATION_.user_username INNER JOIN CU_CUSTOMER t3_u_customers_local ON t3_u_customers_local.name=t4_u_customers_local_RELATION_.customer_name WHERE t1_cust.name=t3_u_customers_local.name ) AND t0_u.active = 1
old SQL generator: this one works
SELECT t0_u.username FROM SEC_USER t0_u, CU_CUSTOMER t1_cust WHERE ( (t1_cust.name=?) AND EXISTS ( SELECT t3_u_customers_RELATION_TABLE.customer_name FROM CU_CUSTOMER_USER t3_u_customers_RELATION_TABLE WHERE t0_u.username=t3_u_customers_RELATION_TABLE.user_username AND t1_cust.name=t3_u_customers_RELATION_TABLE.customer_name ) AND t0_u.active = 1 )
We're not sure if this problem is because the SQL-92 that JBoss is generating is incorrect, or if Oracle 10g's SQL-92 support is less than perfect (they did just add support for SQL-92 joins sometime in the 9 series, so it hasn't been around forever).
If you need any more information, just ask (w/r/t deployment descriptors, db schema, test data).
JBoss 4.0.0 final
Oracle 10g 10.1.0.2.0