glassfish
  1. glassfish
  2. GLASSFISH-3581

Complex JPA query (EJBQL?) generates syntatically wrong SQL queries

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 9.1peur1
    • Fix Version/s: not determined
    • Component/s: entity-persistence
    • Labels:
      None
    • Environment:

      Operating System: All
      Platform: All

    • Issuezilla Id:
      3,581
    • Status Whiteboard:
      Hide

      HIGH

      Show
      HIGH

      Description

      JPA seems to generate invaid SQL statement for the following type of complex JPA
      query specified in orm.xml:

      <query>
      SELECT draft.itemId
      FROM DbItemDraft draft, DbItem itm
      WHERE itm.id = draft.itemId
      AND itm.itemType = :type
      AND draft.creator = :dbuser
      AND draft.creationTimestamp in
      (SELECT MAX(subdraft.creationTimestamp)
      FROM DbItemDraft subdraft, DbItem subitm
      WHERE subitm.id = subdraft.itemId
      AND subitm.itemType = :type
      AND subdraft.creator = :dbuser
      GROUP BY subdraft.itemId)
      ORDER BY draft.creationTimestamp DESC
      </query

      The SQL query generated tries to reference table names that are not actually
      defined in the query first. You may try to reproduce this easily with any
      complex query that has inturn sub-queries in them.

      1. DbItem.java
        8 kB
        girixkumar
      2. DbItemVersion.java
        9 kB
        girixkumar
      3. DbWowUser.java
        10 kB
        girixkumar

        Activity

        Hide
        gfbugbridge added a comment -

        <BT6601393>

        Show
        gfbugbridge added a comment - <BT6601393>
        Hide
        tware added a comment -

        What are the objects involved in the query? How are they mapped?

        What SQL is generated? What is wrong with it?

        Show
        tware added a comment - What are the objects involved in the query? How are they mapped? What SQL is generated? What is wrong with it?
        Hide
        girixkumar added a comment -

        Created an attachment (id=1140)
        DbItem (Table name ITEM)

        Show
        girixkumar added a comment - Created an attachment (id=1140) DbItem (Table name ITEM)
        Hide
        girixkumar added a comment -

        Created an attachment (id=1141)
        DbItemVersion (table name ITEM_VERSION)

        Show
        girixkumar added a comment - Created an attachment (id=1141) DbItemVersion (table name ITEM_VERSION)
        Hide
        girixkumar added a comment -

        Created an attachment (id=1142)
        DbWowUser (table name WOW_USER)

        Show
        girixkumar added a comment - Created an attachment (id=1142) DbWowUser (table name WOW_USER)
        Hide
        girixkumar added a comment -

        Here is the extra information you needed. I have also attached the relevant JPA
        classes (3 classes). These classes were generated by Netbeans Entity bean from
        database wizard.

        This is the EJBQL:
        <named-query name="DatabaseItem.getRecentlyPublishedItems">
        <query>
        SELECT ver.itemId
        FROM DbItemVersion ver
        WHERE ver.creator = :dbuser
        AND ver.creationTimestamp in
        (SELECT MAX(subver.creationTimestamp)
        FROM DbItemVersion subver
        WHERE subver.creator = :dbuser
        GROUP BY subver.itemId)
        ORDER BY ver.creationTimestamp DESC
        </query>
        </named-query>

        And this is the exception that is thrown:

        Internal Exception: SQL Exception: Column 'T3.ID' is either not in any table in
        the FROM list or appears within a join specification and is outside the scope of
        the join specification or appears in a HAVING clause and is not in the GROUP BY
        list. If this is a CREATE or ALTER TABLE statement then 'T3.ID' is not a column
        in the target table.
        Error Code: 30000
        Call: SELECT t0.ID, t0.URI, t0.CREATED_TIMESTAMP, t0.ITEM_TYPE, t0.SECURED FROM
        ITEM_VERSION t1 LEFT OUTER JOIN ITEM t0 ON (t0.ID = t1.ITEM_ID) WHERE
        ((t1.CREATOR = ?) AND t1.CREATION_TIMESTAMP IN (SELECT
        MAX(t2.CREATION_TIMESTAMP) FROM ITEM_VERSION t2 WHERE (t2.CREATOR = ?) GROUP BY
        t3.ID, t3.URI, t3.CREATED_TIMESTAMP, t3.ITEM_TYPE, t3.SECURED)) ORDER BY
        t1.CREATION_TIMESTAMP DESC
        bind => [23421, 23421]
        Query: ReportQuery(org.webonweb.runtime.impl.repository.db.jpa.DbItemVersion)
        Local Exception Stack:
        Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0 (Build b53-rc
        (06/27/2007))): oracle.toplink.essentials.exceptions.DatabaseException
        Internal Exception: SQL Exception: Column 'T3.ID' is either not in any table in
        the FROM list or appears within a join specification and is outside the scope of
        the join specification or appears in a HAVING clause and is not in the GROUP BY
        list. If this is a CREATE or ALTER TABLE statement then 'T3.ID' is not a column
        in the target table.
        Error Code: 30000
        Call: SELECT t0.ID, t0.URI, t0.CREATED_TIMESTAMP, t0.ITEM_TYPE, t0.SECURED FROM
        ITEM_VERSION t1 LEFT OUTER JOIN ITEM t0 ON (t0.ID = t1.ITEM_ID) WHERE
        ((t1.CREATOR = ?) AND t1.CREATION_TIMESTAMP IN (SELECT
        MAX(t2.CREATION_TIMESTAMP) FROM ITEM_VERSION t2 WHERE (t2.CREATOR = ?) GROUP BY
        t3.ID, t3.URI, t3.CREATED_TIMESTAMP, t3.ITEM_TYPE, t3.SECURED)) ORDER BY
        t1.CREATION_TIMESTAMP DESC
        bind => [23421, 23421]
        Query: ReportQuery(org.webonweb.runtime.impl.repository.db.jpa.DbItemVersion)
        at
        oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)
        at
        oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:566)
        at
        oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:452)
        at
        oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:480)
        at
        oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
        at
        oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:214)
        at
        oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:285)
        at
        oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:615)
        at
        oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2416)
        at
        oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2382)
        at
        oracle.toplink.essentials.queryframework.ReportQuery.executeDatabaseQuery(ReportQuery.java:802)
        at
        oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:628)
        at
        oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:692)
        at
        oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:746)
        at
        oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2233)
        at
        oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:952)
        at
        oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:924)
        at
        oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:367)
        at
        oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:478)
        at
        org.webonweb.runtime.impl.repository.db.DatabaseItem.getRecentItems(DatabaseItem.java:522)

        Show
        girixkumar added a comment - Here is the extra information you needed. I have also attached the relevant JPA classes (3 classes). These classes were generated by Netbeans Entity bean from database wizard. This is the EJBQL: <named-query name="DatabaseItem.getRecentlyPublishedItems"> <query> SELECT ver.itemId FROM DbItemVersion ver WHERE ver.creator = :dbuser AND ver.creationTimestamp in (SELECT MAX(subver.creationTimestamp) FROM DbItemVersion subver WHERE subver.creator = :dbuser GROUP BY subver.itemId) ORDER BY ver.creationTimestamp DESC </query> </named-query> And this is the exception that is thrown: Internal Exception: SQL Exception: Column 'T3.ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T3.ID' is not a column in the target table. Error Code: 30000 Call: SELECT t0.ID, t0.URI, t0.CREATED_TIMESTAMP, t0.ITEM_TYPE, t0.SECURED FROM ITEM_VERSION t1 LEFT OUTER JOIN ITEM t0 ON (t0.ID = t1.ITEM_ID) WHERE ((t1.CREATOR = ?) AND t1.CREATION_TIMESTAMP IN (SELECT MAX(t2.CREATION_TIMESTAMP) FROM ITEM_VERSION t2 WHERE (t2.CREATOR = ?) GROUP BY t3.ID, t3.URI, t3.CREATED_TIMESTAMP, t3.ITEM_TYPE, t3.SECURED)) ORDER BY t1.CREATION_TIMESTAMP DESC bind => [23421, 23421] Query: ReportQuery(org.webonweb.runtime.impl.repository.db.jpa.DbItemVersion) Local Exception Stack: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0 (Build b53-rc (06/27/2007))): oracle.toplink.essentials.exceptions.DatabaseException Internal Exception: SQL Exception: Column 'T3.ID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T3.ID' is not a column in the target table. Error Code: 30000 Call: SELECT t0.ID, t0.URI, t0.CREATED_TIMESTAMP, t0.ITEM_TYPE, t0.SECURED FROM ITEM_VERSION t1 LEFT OUTER JOIN ITEM t0 ON (t0.ID = t1.ITEM_ID) WHERE ((t1.CREATOR = ?) AND t1.CREATION_TIMESTAMP IN (SELECT MAX(t2.CREATION_TIMESTAMP) FROM ITEM_VERSION t2 WHERE (t2.CREATOR = ?) GROUP BY t3.ID, t3.URI, t3.CREATED_TIMESTAMP, t3.ITEM_TYPE, t3.SECURED)) ORDER BY t1.CREATION_TIMESTAMP DESC bind => [23421, 23421] Query: ReportQuery(org.webonweb.runtime.impl.repository.db.jpa.DbItemVersion) at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319) at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:566) at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:452) at oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:480) at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228) at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:214) at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:285) at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:615) at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2416) at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2382) at oracle.toplink.essentials.queryframework.ReportQuery.executeDatabaseQuery(ReportQuery.java:802) at oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:628) at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:692) at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:746) at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2233) at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:952) at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:924) at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:367) at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:478) at org.webonweb.runtime.impl.repository.db.DatabaseItem.getRecentItems(DatabaseItem.java:522)
        Hide
        tware added a comment -

        Confirming issue...

        As a workaround, you could use a native query.

        Show
        tware added a comment - Confirming issue... As a workaround, you could use a native query.
        Hide
        pkrogh added a comment -

        With an existing workaround, this will not hold up the release. Needs to be
        looked at shortly after release.

        Show
        pkrogh added a comment - With an existing workaround, this will not hold up the release. Needs to be looked at shortly after release.
        Hide
        Tom Mueller added a comment -

        Bulk change to set fix version to "not determined" where the issue is open but the value is for a released version.

        Show
        Tom Mueller added a comment - Bulk change to set fix version to "not determined" where the issue is open but the value is for a released version.

          People

          • Assignee:
            tware
            Reporter:
            girixkumar
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: