openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "David Wisneski (JIRA)" <j...@apache.org>
Subject [jira] Closed: (OPENJPA-52) bad sql pushdown, puts group by in outer select
Date Thu, 01 Feb 2007 01:21:05 GMT

     [ https://issues.apache.org/jira/browse/OPENJPA-52?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

David Wisneski closed OPENJPA-52.
---------------------------------

    Resolution: Duplicate

duplicate of OPENJPA-28

> bad sql pushdown, puts group by in outer select
> -----------------------------------------------
>
>                 Key: OPENJPA-52
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-52
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: query
>         Environment: Windows xp, derby, db2
>            Reporter: George Hongell
>         Attachments: failureEntities.jar
>
>
> 457 bad sql pushdown, puts group by in outer select
>  TEST457; select e.name, e.salary from EmpBean e where (e.name = Any(select e1.name from
EmpBean e1 group by e1.name )) order by e.name 
> Column reference 'T0.NAME' is invalid.  For a SELECT list with a GROUP BY, the list may
only contain grouping columns and valid aggregate expressions.   {SELECT t0.name, t0.salary
FROM EmpBean t0 JOIN EmpBean t1 ON (1 = 1) WHERE (t0.name = ANY((SELECT t2.name FROM EmpBean
t2 GROUP BY t2.name))) GROUP BY t1.name ORDER BY t0.name ASC} [code=30000, state=42Y36] 
> s/b
> select t0.name, t0.salary FROM EmpBean t0 WHERE (t0.name = ANY((select t2.name FROM EmpBean
t2 GROUP BY t2.name))) ORDER BY t0.name ASC
> <0|false|0.0.0> org.apache.openjpa.persistence.PersistenceException: Column reference
'T0.NAME' is invalid.  For a SELECT list with a GROUP BY, the list may only contain grouping
columns and valid aggregate expressions.   {SELECT t0.name, t0.salary FROM EmpBean t0 JOIN
EmpBean t1 ON (1 = 1) WHERE (t0.name = ANY((SELECT t2.name FROM EmpBean t2 GROUP BY t2.name)))
GROUP BY t1.name ORDER BY t0.name ASC} [code=30000, state=42Y36]
> 	at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3713)
> 	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:94)
> 	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:80)
> 	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:56)
> 	at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:152)
> 	at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:37)
> 	at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161)
> 	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936)
> 	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746)
> 	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716)
> 	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712)
> 	at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512)
> 	at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:216)
> 	at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:254)
> 	at com.ibm.ws.query.utils.JFLoopQueryTestcase.createAndRunQuery(JFLoopQueryTestcase.java:187)
> 	at com.ibm.ws.query.utils.JFLoopQueryTestcase.testFileQuery(JFLoopQueryTestcase.java:536)
> 	at com.ibm.ws.query.utils.JFLoopQueryTestcase.testRunQueryLoopImpl(JFLoopQueryTestcase.java:591)
> 	at com.ibm.ws.query.tests.JFLoopQueryTest.testRunQueryLoop(JFLoopQueryTest.java:265)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> 	at java.lang.reflect.Method.invoke(Method.java:615)
> 	at junit.extensions.jfunc.TestletWrapper.runBare(TestletWrapper.java:116)
> 	at junit.extensions.jfunc.TestletWrapper$1.protect(TestletWrapper.java:106)
> 	at junit.framework.TestResult.runProtected(Unknown Source)
> 	at junit.extensions.jfunc.TestletWrapper.run(TestletWrapper.java:109)
> 	at junit.framework.TestSuite.runTest(Unknown Source)
> 	at junit.framework.TestSuite.run(Unknown Source)
> 	at junit.extensions.jfunc.JFuncSuite.run(JFuncSuite.java:134)
> 	at junit.extensions.jfunc.textui.JFuncRunner.doRun(JFuncRunner.java:76)
> 	at junit.extensions.jfunc.textui.JFuncRunner.start(JFuncRunner.java:398)
> 	at junit.extensions.jfunc.textui.JFuncRunner.main(JFuncRunner.java:218)
> Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: Column reference 'T0.NAME'
is invalid.  For a SELECT list with a GROUP BY, the list may only contain grouping columns
and valid aggregate expressions.   {SELECT t0.name, t0.salary FROM EmpBean t0 JOIN EmpBean
t1 ON (1 = 1) WHERE (t0.name = ANY((SELECT t2.name FROM EmpBean t2 GROUP BY t2.name))) GROUP
BY t1.name ORDER BY t0.name ASC} [code=30000, state=42Y36]
> 	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:193)
> 	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$6(LoggingConnectionDecorator.java:189)
> 	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:217)
> 	at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160)
> 	at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:137)
> 	at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160)
> 	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1305)
> 	at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:149)
> 	at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:463)
> 	at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:443)
> 	at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:322)
> 	at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:295)
> 	at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:397)
> 	at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:208)
> 	at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:198)
> 	at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:91)
> 	at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:31)
> 	... 26 more
> NestedThrowables:
> ERROR 42Y36: Column reference 'T0.NAME' is invalid.  For a SELECT list with a GROUP BY,
the list may only contain grouping columns and valid aggregate expressions.  
> 	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.VerifyAggregateExpressionsVisitor.visit(Unknown
Source)
> 	at org.apache.derby.impl.sql.compile.QueryTreeNode.accept(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.ResultColumn.accept(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.QueryTreeNodeVector.accept(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.FromSubquery.bindExpressions(Unknown Source)
> 	at org.apache.derby.impl.sql.compile.FromList.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.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.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:185)
> 	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:278)
> 	at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:162)
> 	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:214)
> 	at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160)
> 	at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:137)
> 	at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:160)
> 	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1305)
> 	at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:149)
> 	at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:463)
> 	at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:443)
> 	at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:322)
> 	at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:295)
> 	at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:397)
> 	at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:208)
> 	at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:198)
> 	at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:91)
> 	at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:31)
> 	at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1161)
> 	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:936)
> 	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:746)
> 	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:716)
> 	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:712)
> 	at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:512)
> 	at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:216)
> 	at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:254)
> 	at com.ibm.ws.query.utils.JFLoopQueryTestcase.createAndRunQuery(JFLoopQueryTestcase.java:187)
> 	at com.ibm.ws.query.utils.JFLoopQueryTestcase.testFileQuery(JFLoopQueryTestcase.java:536)
> 	at com.ibm.ws.query.utils.JFLoopQueryTestcase.testRunQueryLoopImpl(JFLoopQueryTestcase.java:591)
> 	at com.ibm.ws.query.tests.JFLoopQueryTest.testRunQueryLoop(JFLoopQueryTest.java:265)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> 	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64)
> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> 	at java.lang.reflect.Method.invoke(Method.java:615)
> 	at junit.extensions.jfunc.TestletWrapper.runBare(TestletWrapper.java:116)
> 	at junit.extensions.jfunc.TestletWrapper$1.protect(TestletWrapper.java:106)
> 	at junit.framework.TestResult.runProtected(Unknown Source)
> 	at junit.extensions.jfunc.TestletWrapper.run(TestletWrapper.java:109)
> 	at junit.framework.TestSuite.runTest(Unknown Source)
> 	at junit.framework.TestSuite.run(Unknown Source)
> 	at junit.extensions.jfunc.JFuncSuite.run(JFuncSuite.java:134)
> 	at junit.extensions.jfunc.textui.JFuncRunner.doRun(JFuncRunner.java:76)
> 	at junit.extensions.jfunc.textui.JFuncRunner.start(JFuncRunner.java:398)
> 	at junit.extensions.jfunc.textui.JFuncRunner.main(JFuncRunner.java:218)
> actual  TEST457; select e.name, e.salary from EmpBean e where (e.name = Any(select e1.name
from EmpBean e1 group by e1.name )) order by e.name 
>                                                                                     
                                                                                         
  e.name                                                                                 
                                                                                         
      
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> Column reference 'T0.NAME' is invalid.  For a SELECT list with a GROUP BY, the list may
only contain grouping columns and valid aggregate expressions.   {SELECT t0.name, t0.salary
FROM EmpBean t0 JOIN EmpBean t1 ON (1 = 1) WHERE (t0.name = ANY((SELECT t2.name FROM EmpBean
t2 GROUP BY t2.name))) GROUP BY t1.name ORDER BY t0.name ASC} [code=30000, state=42Y36] 
>  TEST457; 1 tuple

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message