Derby EJBQL syntax
------------------
Key: CAY-897
URL: https://issues.apache.org/cayenne/browse/CAY-897
Project: Cayenne
Issue Type: Bug
Components: Cayenne Core Library
Affects Versions: 3.0
Reporter: Andrus Adamchik
Assignee: Andrus Adamchik
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)
-- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
This archive was generated by hypermail 2.0.0 : Sun Oct 21 2007 - 16:19:50 EDT