Cayenne
  1. Cayenne
  2. CAY-1582

EJBQL queries are broken for vertical inheritance entities

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 3.1 (final)
    • Fix Version/s: Undefined future
    • Component/s: Core Library
    • Labels:
      None

      Description

      EJBQLQuery doesn't pay attention to vertical inheritance attributes, so queries results are different from equivalent SelectQuery results. Some EJBQL queries with flattened attributes involving inherited entities are failing at all due to invalid SQL expression generated.

      1. vertical_inheritance_ejbql_test.patch
        23 kB
        Victor Antonovich
      2. vertical_inheritance_ejbql_test_2.patch
        24 kB
        Victor Antonovich
      3. vertical_inheritance_ejbql_fix.patch
        5 kB
        Victor Antonovich
      4. vertical_inheritance_ejbql_fix_2.patch
        7 kB
        Victor Antonovich
      5. org.apache.cayenne.access.VerticalInheritanceTest.txt
        5 kB
        Victor Antonovich

        Activity

        Hide
        Victor Antonovich added a comment -

        Vertical inheritance EQBQLQuery tests patch and failed tests output are attached.

        Show
        Victor Antonovich added a comment - Vertical inheritance EQBQLQuery tests patch and failed tests output are attached.
        Hide
        Victor Antonovich added a comment -

        Quick-and-dirty bug fix patch is attached.

        Show
        Victor Antonovich added a comment - Quick-and-dirty bug fix patch is attached.
        Hide
        Victor Antonovich added a comment -

        Added better patch with additional fix in EJBQLConditionTranslator and testcase for it.

        Show
        Victor Antonovich added a comment - Added better patch with additional fix in EJBQLConditionTranslator and testcase for it.
        Hide
        Andrus Adamchik added a comment -

        Applied the latest set of patches unchanged. Thanks a lot!

        Show
        Andrus Adamchik added a comment - Applied the latest set of patches unchanged. Thanks a lot!
        Hide
        Andrus Adamchik added a comment -

        Unfortunately I have to undo my earlier commits (undo commit is r1150338) and reopen this issue. Testing with all dbs other than HSQLDB causes exceptions in the new test cases. The exceptions are all different (see below), but all seem to be related to duplicate joins and table aliases in generated queries. E.g.: t2 and t1 are joined twice:

        SELECT t0.NAME AS ec0_0, t0.DISCRIMINATOR AS ec0_1, t1.SUB1_NAME AS ec0_2, t2.SUB2_NAME AS ec0_3, t2.SUB2_ATTR AS ec0_4, t4.SUB1_SUB1_NAME AS ec0_5, t0.ID AS ec0_6
        FROM IV_ROOT t0
        LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID)
        LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID)
        LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID)
        LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID)
        LEFT OUTER JOIN IV_SUB1 t3 ON (t0.ID = t3.ID)
        LEFT OUTER JOIN IV_SUB1_SUB1 t4 ON (t3.ID = t4.ID)

        HSQLDB seems to be ok with this, but others complain:

        Derby:

        INFO: SELECT t0.NAME AS ec0_0, t0.DISCRIMINATOR AS ec0_1, t1.SUB1_NAME AS ec0_2, t2.SUB2_NAME AS ec0_3, t2.SUB2_ATTR AS ec0_4, t4.SUB1_SUB1_NAME AS ec0_5, t0.ID AS ec0_6 FROM IV_ROOT t0 LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB1 t3 ON (t0.ID = t3.ID) LEFT OUTER JOIN IV_SUB1_SUB1 t4 ON (t3.ID = t4.ID)
        Jul 24, 2011 1:52:44 PM org.apache.cayenne.log.CommonsJdbcEventLogger logQueryError
        INFO: *** error.
        java.sql.SQLSyntaxErrorException: Column name 'T1.SUB1_NAME' is in more than one table in the FROM list.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)

        H2
        Jul 24, 2011 1:40:55 PM org.apache.cayenne.log.CommonsJdbcEventLogger logQuery
        INFO: SELECT t0.NAME AS ec0_0, t0.DISCRIMINATOR AS ec0_1, t1.SUB1_NAME AS ec0_2, t2.SUB2_NAME AS ec0_3, t2.SUB2_ATTR AS ec0_4, t4.SUB1_SUB1_NAME AS ec0_5, t0.ID AS ec0_6 FROM IV_ROOT t0 LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB1 t3 ON (t0.ID = t3.ID) LEFT OUTER JOIN IV_SUB1_SUB1 t4 ON (t3.ID = t4.ID)
        Jul 24, 2011 1:40:55 PM org.apache.cayenne.log.CommonsJdbcEventLogger logQueryError
        INFO: *** error.
        org.h2.jdbc.JdbcSQLException: Table "IV_ROOT" not found; SQL statement:
        SELECT t0.NAME AS ec0_0, t0.DISCRIMINATOR AS ec0_1, t1.SUB1_NAME AS ec0_2, t2.SUB2_NAME AS ec0_3, t2.SUB2_ATTR AS ec0_4, t4.SUB1_SUB1_NAME AS ec0_5, t0.ID AS ec0_6 FROM IV_ROOT t0 LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB1 t3 ON (t0.ID = t3.ID) LEFT OUTER JOIN IV_SUB1_SUB1 t4 ON (t3.ID = t4.ID) [42102-154]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
        at org.h2.message.DbException.get(DbException.java:167)
        at org.h2.message.DbException.get(DbException.java:144)
        at org.h2.command.Parser.readTableOrView(Parser.java:4624)

        PostgreSQL
        INFO: SELECT t0.NAME AS ec0_0, t0.DISCRIMINATOR AS ec0_1, t1.SUB1_NAME AS ec0_2, t2.SUB2_NAME AS ec0_3, t2.SUB2_ATTR AS ec0_4, t4.SUB1_SUB1_NAME AS ec0_5, t0.ID AS ec0_6 FROM IV_ROOT t0 LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB1 t3 ON (t0.ID = t3.ID) LEFT OUTER JOIN IV_SUB1_SUB1 t4 ON (t3.ID = t4.ID)
        Jul 24, 2011 1:54:10 PM org.apache.cayenne.log.CommonsJdbcEventLogger logQueryError
        INFO: *** error.
        org.postgresql.util.PSQLException: ERROR: table name "t2" specified more than once
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)

        Show
        Andrus Adamchik added a comment - Unfortunately I have to undo my earlier commits (undo commit is r1150338) and reopen this issue. Testing with all dbs other than HSQLDB causes exceptions in the new test cases. The exceptions are all different (see below), but all seem to be related to duplicate joins and table aliases in generated queries. E.g.: t2 and t1 are joined twice: SELECT t0.NAME AS ec0_0, t0.DISCRIMINATOR AS ec0_1, t1.SUB1_NAME AS ec0_2, t2.SUB2_NAME AS ec0_3, t2.SUB2_ATTR AS ec0_4, t4.SUB1_SUB1_NAME AS ec0_5, t0.ID AS ec0_6 FROM IV_ROOT t0 LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB1 t3 ON (t0.ID = t3.ID) LEFT OUTER JOIN IV_SUB1_SUB1 t4 ON (t3.ID = t4.ID) HSQLDB seems to be ok with this, but others complain: Derby: INFO: SELECT t0.NAME AS ec0_0, t0.DISCRIMINATOR AS ec0_1, t1.SUB1_NAME AS ec0_2, t2.SUB2_NAME AS ec0_3, t2.SUB2_ATTR AS ec0_4, t4.SUB1_SUB1_NAME AS ec0_5, t0.ID AS ec0_6 FROM IV_ROOT t0 LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB1 t3 ON (t0.ID = t3.ID) LEFT OUTER JOIN IV_SUB1_SUB1 t4 ON (t3.ID = t4.ID) Jul 24, 2011 1:52:44 PM org.apache.cayenne.log.CommonsJdbcEventLogger logQueryError INFO: *** error. java.sql.SQLSyntaxErrorException: Column name 'T1.SUB1_NAME' is in more than one table in the FROM list. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) H2 Jul 24, 2011 1:40:55 PM org.apache.cayenne.log.CommonsJdbcEventLogger logQuery INFO: SELECT t0.NAME AS ec0_0, t0.DISCRIMINATOR AS ec0_1, t1.SUB1_NAME AS ec0_2, t2.SUB2_NAME AS ec0_3, t2.SUB2_ATTR AS ec0_4, t4.SUB1_SUB1_NAME AS ec0_5, t0.ID AS ec0_6 FROM IV_ROOT t0 LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB1 t3 ON (t0.ID = t3.ID) LEFT OUTER JOIN IV_SUB1_SUB1 t4 ON (t3.ID = t4.ID) Jul 24, 2011 1:40:55 PM org.apache.cayenne.log.CommonsJdbcEventLogger logQueryError INFO: *** error. org.h2.jdbc.JdbcSQLException: Table "IV_ROOT" not found; SQL statement: SELECT t0.NAME AS ec0_0, t0.DISCRIMINATOR AS ec0_1, t1.SUB1_NAME AS ec0_2, t2.SUB2_NAME AS ec0_3, t2.SUB2_ATTR AS ec0_4, t4.SUB1_SUB1_NAME AS ec0_5, t0.ID AS ec0_6 FROM IV_ROOT t0 LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB1 t3 ON (t0.ID = t3.ID) LEFT OUTER JOIN IV_SUB1_SUB1 t4 ON (t3.ID = t4.ID) [42102-154] at org.h2.message.DbException.getJdbcSQLException(DbException.java:327) at org.h2.message.DbException.get(DbException.java:167) at org.h2.message.DbException.get(DbException.java:144) at org.h2.command.Parser.readTableOrView(Parser.java:4624) PostgreSQL INFO: SELECT t0.NAME AS ec0_0, t0.DISCRIMINATOR AS ec0_1, t1.SUB1_NAME AS ec0_2, t2.SUB2_NAME AS ec0_3, t2.SUB2_ATTR AS ec0_4, t4.SUB1_SUB1_NAME AS ec0_5, t0.ID AS ec0_6 FROM IV_ROOT t0 LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB2 t2 ON (t0.ID = t2.ID) LEFT OUTER JOIN IV_SUB1 t1 ON (t0.ID = t1.ID) LEFT OUTER JOIN IV_SUB1 t3 ON (t0.ID = t3.ID) LEFT OUTER JOIN IV_SUB1_SUB1 t4 ON (t3.ID = t4.ID) Jul 24, 2011 1:54:10 PM org.apache.cayenne.log.CommonsJdbcEventLogger logQueryError INFO: *** error. org.postgresql.util.PSQLException: ERROR: table name "t2" specified more than once at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)

          People

          • Assignee:
            Andrus Adamchik
            Reporter:
            Victor Antonovich
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development