0 Replies Latest reply on Oct 4, 2004 12:41 PM by csmmoss

    EJBQLToSQL92Compiler, Oracle 10g, and MEMBER OF

    csmmoss Newbie

      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