Return-Path: Delivered-To: apmail-incubator-open-jpa-dev-archive@locus.apache.org Received: (qmail 46718 invoked from network); 1 Feb 2007 01:21:28 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 1 Feb 2007 01:21:28 -0000 Received: (qmail 5618 invoked by uid 500); 1 Feb 2007 01:21:33 -0000 Delivered-To: apmail-incubator-open-jpa-dev-archive@incubator.apache.org Received: (qmail 5512 invoked by uid 500); 1 Feb 2007 01:21:33 -0000 Mailing-List: contact open-jpa-dev-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: open-jpa-dev@incubator.apache.org Delivered-To: mailing list open-jpa-dev@incubator.apache.org Received: (qmail 5501 invoked by uid 99); 1 Feb 2007 01:21:33 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 31 Jan 2007 17:21:33 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO brutus.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 31 Jan 2007 17:21:25 -0800 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id A308071429F for ; Wed, 31 Jan 2007 17:21:05 -0800 (PST) Message-ID: <20818726.1170292865665.JavaMail.jira@brutus> Date: Wed, 31 Jan 2007 17:21:05 -0800 (PST) From: "David Wisneski (JIRA)" To: open-jpa-dev@incubator.apache.org Subject: [jira] Closed: (OPENJPA-52) bad sql pushdown, puts group by in outer select In-Reply-To: <9647611.1158628702260.JavaMail.jira@brutus> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org [ 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.(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.(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.(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.(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.