EJBQLToSQL92Compiler, Oracle 10g, and MEMBER OF

[csmmoss]

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:

ELB-QL:

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).

System specs:
JBoss 4.0.0 final
Oracle 10g 10.1.0.2.0
Windows 2000
Java 1.4.2_03