You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by "Andrus Adamchik (JIRA)" <de...@cayenne.apache.org> on 2007/10/21 22:18:50 UTC
[JIRA] Created: (CAY-897) Derby EJBQL syntax
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.
[jira] Closed: (CAY-897) Derby EJBQL syntax
Posted by "Andrus Adamchik (JIRA)" <de...@cayenne.apache.org>.
[ https://issues.apache.org/cayenne/browse/CAY-897?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Andrus Adamchik closed CAY-897.
-------------------------------
Resolution: Cannot Reproduce
Fix Version/s: 3.0
Looks like Derby upgrade to 10.3.* fixed this issue
> 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
> Fix For: 3.0
>
>
> 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.