Uploaded image for project: 'Cayenne'
  1. Cayenne
  2. CAY-897

Derby EJBQL syntax

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Cannot Reproduce
    • 3.0
    • 3.0
    • Core Library
    • None

    Description

      1. Derby database fails DataContextEJBQLGroupByHavingTest test with a number of similar errors:

      INFO: SELECT t0.ESTIMATED_PRICE AS sc0, COUNT AS sc1 FROM PAINTING t0 GROUP BY t0.ESTIMATED_PRICE HAVING COUNT > ? [bind: 2]
      Oct 21, 2007 11:12:06 PM org.apache.cayenne.access.QueryLogger logQueryError
      INFO: *** error.
      ERROR 42X04: Column 'T0.SC0' 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 'T0.SC0' is not a column in the target table.
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown Source)
      at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(Unknown Source)
      at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(Unknown Source)
      at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source)
      at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source)
      at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
      at org.apache.derby.impl.sql.compile.ReadCursorNode.bind(Unknown Source)
      at org.apache.derby.impl.sql.compile.CursorNode.bind(Unknown Source)
      at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
      at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
      at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
      at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
      at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:274)
      at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:280)
      at org.apache.cayenne.access.TransactionConnectionDecorator.prepareStatement(TransactionConnectionDecorator.java:179)
      at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:128)
      at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:107)
      at org.apache.cayenne.access.jdbc.EJBQLAction.performAction(EJBQLAction.java:96)
      at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:57)
      at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:236)
      at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:446)
      at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:67)
      at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:418)
      at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:853)
      at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:415)
      at org.apache.cayenne.access.UnitTestDomainQueryAction.runQueryInTransaction(UnitTestDomainQueryAction.java:53)
      at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:119)
      at org.apache.cayenne.access.UnitTestDomainQueryAction.execute(UnitTestDomainQueryAction.java:48)
      at org.apache.cayenne.access.UnitTestDomain.onQuery(UnitTestDomain.java:62)
      at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:282)
      at org.apache.cayenne.access.DataContextQueryAction.execute(DataContextQueryAction.java:59)
      at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1323)
      at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1312)
      at org.apache.cayenne.access.DataContextEJBQLGroupByHavingTest.testGroupByHavingOnAggregate(DataContextEJBQLGroupByHavingTest.java:127)

      2. Subqueries with multiple columns generate an error (DataContextEJBQLSubqueryTest), so they should be rewritten to maybe just select an ID?

      NFO: SELECT DISTINCT t0.PAINTING_TITLE AS PAINTING_TITLE, t0.ARTIST_ID AS ARTIST_ID, t0.PAINTING_DESCRIPTION AS PAINTING_DESCRIPTION, t0.ESTIMATED_PRICE AS ESTIMATED_PRICE, t0.GALLERY_ID AS GALLERY_ID, t0.PAINTING_ID AS PAINTING_ID FROM PAINTING t0 WHERE EXISTS (SELECT DISTINCT t1.PAINTING_TITLE, t1.ARTIST_ID, t1.PAINTING_DESCRIPTION, t1.ESTIMATED_PRICE, t1.GALLERY_ID, t1.PAINTING_ID FROM PAINTING t1 WHERE t1.PAINTING_TITLE = t0.PAINTING_TITLE AND t0.ESTIMATED_PRICE <> t1.ESTIMATED_PRICE)
      Oct 21, 2007 11:15:48 PM org.apache.cayenne.access.QueryLogger logQueryError
      INFO: *** error.
      ERROR 42X39: Subquery is only allowed to return a single column.
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.impl.sql.compile.SubqueryNode.bindExpression(Unknown Source)
      at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source)
      at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source)
      at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
      at org.apache.derby.impl.sql.compile.ReadCursorNode.bind(Unknown Source)
      at org.apache.derby.impl.sql.compile.CursorNode.bind(Unknown Source)
      at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
      at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
      at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
      at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
      at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:274)
      at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:280)
      at org.apache.cayenne.access.TransactionConnectionDecorator.prepareStatement(TransactionConnectionDecorator.java:179)
      at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:128)
      at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:107)
      at org.apache.cayenne.access.jdbc.EJBQLAction.performAction(EJBQLAction.java:96)
      at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:57)
      at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:236)
      at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:446)
      at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:67)
      at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:418)
      at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:853)
      at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:415)
      at org.apache.cayenne.access.UnitTestDomainQueryAction.runQueryInTransaction(UnitTestDomainQueryAction.java:53)
      at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:119)
      at org.apache.cayenne.access.UnitTestDomainQueryAction.execute(UnitTestDomainQueryAction.java:48)
      at org.apache.cayenne.access.UnitTestDomain.onQuery(UnitTestDomain.java:62)
      at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:282)
      at org.apache.cayenne.access.DataContextQueryAction.execute(DataContextQueryAction.java:59)
      at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1323)
      at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1312)
      at org.apache.cayenne.access.DataContextEJBQLSubqueryTest.testExists(DataContextEJBQLSubqueryTest.java:48)

      Attachments

        Activity

          People

            andrus Andrus Adamchik
            andrus Andrus Adamchik
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: