openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Marc Siegel" <marc.siegel...@gmail.com>
Subject Re: JPQL error claims "no such column t2.id"
Date Fri, 07 Dec 2007 16:57:09 GMT
Hi Folks,

These problems I was having were caused by my own misunderstanding of
how to navigate multiple collection-based Entity relationships in
JPQL.

The book _Enterprise JavaBeans 3.0_ (Burke & Monson-Haefel, 5th
Edition 2006) explains on pg 181-182 the usage of JPQL operator "IN",
aka "INNER JOIN".

The WHERE clauses I was using in the subqueries below were not correct
JPQL, and needed to be replaced by sequences of IN clauses in the
subquery FROM clauses.

Hope this helps someone else some day
-Marc

On Dec 6, 2007 8:26 PM, Marc Siegel <marc.siegel.17@gmail.com> wrote:
> Hi folks,
> I got an indecipherable JPQL error that I was hoping someone could help me with.
>
> The JPQL query causing the error is:
>   String jpql = "SELECT g FROM GaAdGroup g " +
>             "WHERE g.name LIKE '[" + exGroup.getName() + " #%]' " +
>             "AND " + MAX_KEYWORDS_ADGROUP + " > " +
>             "(SELECT COUNT(k) FROM GaKeyword k " +
>             " WHERE k.gaAdGroup = g " +
>             " AND (k.status IS NULL OR k.status <> :kwDeleted)) " +
>             "AND " + MAX_KEYWORDS_CAMPAIGN + " > " +
>             "(SELECT COUNT(k) FROM GaKeyword k " +
>             " WHERE k.gaAdGroup.gaCampaign = g.gaCampaign " +
>             " AND (k.status IS NULL OR k.status <> :kwDeleted)) " +
>             "AND " + MAX_KEYWORDS_ACCOUNT + " > " +
>             "(SELECT COUNT(k) FROM GaKeyword k " +
>             " WHERE k.gaAdGroup.gaCampaign.gaAccount =
> g.gaCampaign.gaAccount " +
>             " AND (k.status IS NULL OR k.status <> :kwDeleted)) " +
>             "ORDER BY g.name ASC ";
>
> I am running on SQLiteJDBC, so if there is a modification needed to my
> custom DBDictionary subclass please let me know.
>
> Thanks,
> -Marc
>
>
> testNewExEmptyGa(com.leadgenllc.lib.GaSynchToGoogleTest)  Time
> elapsed: 0.13 sec  <<< ERROR!
> <openjpa-1.0.1-r420667:592145 nonfatal general error>
> org.apache.openjpa.persistence.PersistenceException: no such column:
> t2.id {SELECT t0.id, t0.createdAt, t0.updatedAt, t0.isChanged,
> t0.synchronizedAt, t9.id, t9.createdAt, t9.updatedAt, t9.isChanged,
> t9.synchronizedAt, t9.dailyBudget, t9.endDay, t10.id, t10.createdAt,
> t10.updatedAt, t10.isChanged, t10.synchronizedAt, t10.clientEmail,
> t10.customerId, t10.descriptiveName, t9.googleId, t9.name,
> t9.startDay, t9.status, t0.googleId, t0.maxCpc, t0.name, t0.status
> FROM GaAdGroup t0 LEFT OUTER JOIN GaCampaign t9 ON t0.gaCampaignId =
> t9.id LEFT OUTER JOIN GaAccount t10 ON t9.gaAccountId = t10.id WHERE
> (t0.name LIKE ? ESCAPE '\' AND ? > (SELECT COUNT(t2.id) FROM GaKeyword
> t1 WHERE (t1.gaAdGroupId = t0.id AND (t1.status IS NULL OR t1.status
> <> ?))) AND ? > (SELECT COUNT(t2.id) FROM GaKeyword t3, GaAdGroup t4
> WHERE (t4.gaCampaignId = t0.gaCampaignId AND (t3.status IS NULL OR
> t3.status <> ?)) AND t3.gaAdGroupId = t4.id) AND ? > (SELECT
> COUNT(t2.id) FROM GaKeyword t5, GaAdGroup t6, GaCampaign t7,
> GaCampaign t8 WHERE (t7.gaAccountId = t8.gaAccountId AND (t5.status IS
> NULL OR t5.status <> ?)) AND t0.gaCampaignId = t8.id AND
> t5.gaAdGroupId = t6.id AND t6.gaCampaignId = t7.id)) ORDER BY t0.name
> ASC} [code=0, state=null]
>         at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:3938)
>         at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:97)
>         at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:83)
>         at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:59)
>         at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.handleCheckedException(SelectResultObjectProvider.java:155)
>         at org.apache.openjpa.lib.rop.RangeResultObjectProvider.handleCheckedException(RangeResultObjectProvider.java:130)
>         at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:40)
>         at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1223)
>         at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:990)
>         at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:842)
>         at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:773)
>         at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:525)
>         at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:237)
>         at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:277)
>         at com.leadgenllc.lib.GaSynch.getGaAdGroupNotFullCorrespondingToExGroup(GaSynch.java:269)
>         at com.leadgenllc.lib.GaSynch.acquireGaAdGroupNotFullCorrespondingToExGroup(GaSynch.java:235)
>         at com.leadgenllc.lib.GaSynch.interpretNewExKeywordsAsAdditions(GaSynch.java:190)
>         at com.leadgenllc.lib.GaSynch.interpretExChanges(GaSynch.java:104)
>         at com.leadgenllc.lib.GaSynchToGoogleTest.testNewExEmptyGa(GaSynchToGoogleTest.java:132)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke(Method.java:585)
>         at org.junit.internal.runners.TestMethod.invoke(TestMethod.java:59)
>         at org.junit.internal.runners.MethodRoadie.runTestMethod(MethodRoadie.java:98)
>         at org.junit.internal.runners.MethodRoadie$2.run(MethodRoadie.java:79)
>         at org.junit.internal.runners.MethodRoadie.runBeforesThenTestThenAfters(MethodRoadie.java:87)
>         at org.junit.internal.runners.MethodRoadie.runTest(MethodRoadie.java:77)
>         at org.junit.internal.runners.MethodRoadie.run(MethodRoadie.java:42)
>         at org.junit.internal.runners.JUnit4ClassRunner.invokeTestMethod(JUnit4ClassRunner.java:88)
>         at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
>         at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
>         at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
>         at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
>         at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
>         at org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:62)
>         at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:138)
>         at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:125)
>         at org.apache.maven.surefire.Surefire.run(Surefire.java:132)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke(Method.java:585)
>         at org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:290)
>         at org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:818)
> Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: no such
> column: t2.id {SELECT t0.id, t0.createdAt, t0.updatedAt, t0.isChanged,
> t0.synchronizedAt, t9.id, t9.createdAt, t9.updatedAt, t9.isChanged,
> t9.synchronizedAt, t9.dailyBudget, t9.endDay, t10.id, t10.createdAt,
> t10.updatedAt, t10.isChanged, t10.synchronizedAt, t10.clientEmail,
> t10.customerId, t10.descriptiveName, t9.googleId, t9.name,
> t9.startDay, t9.status, t0.googleId, t0.maxCpc, t0.name, t0.status
> FROM GaAdGroup t0 LEFT OUTER JOIN GaCampaign t9 ON t0.gaCampaignId =
> t9.id LEFT OUTER JOIN GaAccount t10 ON t9.gaAccountId = t10.id WHERE
> (t0.name LIKE ? ESCAPE '\' AND ? > (SELECT COUNT(t2.id) FROM GaKeyword
> t1 WHERE (t1.gaAdGroupId = t0.id AND (t1.status IS NULL OR t1.status
> <> ?))) AND ? > (SELECT COUNT(t2.id) FROM GaKeyword t3, GaAdGroup t4
> WHERE (t4.gaCampaignId = t0.gaCampaignId AND (t3.status IS NULL OR
> t3.status <> ?)) AND t3.gaAdGroupId = t4.id) AND ? > (SELECT
> COUNT(t2.id) FROM GaKeyword t5, GaAdGroup t6, GaCampaign t7,
> GaCampaign t8 WHERE (t7.gaAccountId = t8.gaAccountId AND (t5.status IS
> NULL OR t5.status <> ?)) AND t0.gaCampaignId = t8.id AND
> t5.gaAdGroupId = t6.id AND t6.gaCampaignId = t7.id)) ORDER BY t0.name
> ASC} [code=0, state=null]
>         at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:201)
>         at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:57)
>         at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:228)
>         at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:163)
>         at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:140)
>         at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:163)
>         at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1308)
>         at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:152)
>         at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:475)
>         at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:455)
>         at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:354)
>         at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:327)
>         at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:407)
>         at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:218)
>         at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:208)
>         at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:94)
>         at org.apache.openjpa.lib.rop.RangeResultObjectProvider.open(RangeResultObjectProvider.java:74)
>         at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:34)
>         ... 38 more
> NestedThrowables:
> java.sql.SQLException: no such column: t2.id
>         at org.sqlite.DB.throwex(DB.java:252)
>         at org.sqlite.NativeDB.prepare(Native Method)
>         at org.sqlite.DB.prepare(DB.java:62)
>         at org.sqlite.PrepStmt.<init>(PrepStmt.java:25)
>         at org.sqlite.Conn.prepareStatement(Conn.java:205)
>         at org.sqlite.Conn.prepareStatement(Conn.java:198)
>         at org.sqlite.Conn.prepareStatement(Conn.java:187)
>         at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:165)
>         at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:225)
>         at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:163)
>         at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:140)
>         at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:163)
>         at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1308)
>         at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:152)
>         at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:475)
>         at org.apache.openjpa.jdbc.sql.SQLBuffer.prepareStatement(SQLBuffer.java:455)
>         at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:354)
>         at org.apache.openjpa.jdbc.sql.SelectImpl.execute(SelectImpl.java:327)
>         at org.apache.openjpa.jdbc.sql.LogicalUnion$UnionSelect.execute(LogicalUnion.java:407)
>         at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:218)
>         at org.apache.openjpa.jdbc.sql.LogicalUnion.execute(LogicalUnion.java:208)
>         at org.apache.openjpa.jdbc.kernel.SelectResultObjectProvider.open(SelectResultObjectProvider.java:94)
>         at org.apache.openjpa.lib.rop.RangeResultObjectProvider.open(RangeResultObjectProvider.java:74)
>         at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:34)
>         at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1223)
>         at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:990)
>         at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:842)
>         at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:773)
>         at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:525)
>         at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:237)
>         at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:277)
>         at com.leadgenllc.lib.GaSynch.getGaAdGroupNotFullCorrespondingToExGroup(GaSynch.java:269)
>         at com.leadgenllc.lib.GaSynch.acquireGaAdGroupNotFullCorrespondingToExGroup(GaSynch.java:235)
>         at com.leadgenllc.lib.GaSynch.interpretNewExKeywordsAsAdditions(GaSynch.java:190)
>         at com.leadgenllc.lib.GaSynch.interpretExChanges(GaSynch.java:104)
>         at com.leadgenllc.lib.GaSynchToGoogleTest.testNewExEmptyGa(GaSynchToGoogleTest.java:132)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke(Method.java:585)
>         at org.junit.internal.runners.TestMethod.invoke(TestMethod.java:59)
>         at org.junit.internal.runners.MethodRoadie.runTestMethod(MethodRoadie.java:98)
>         at org.junit.internal.runners.MethodRoadie$2.run(MethodRoadie.java:79)
>         at org.junit.internal.runners.MethodRoadie.runBeforesThenTestThenAfters(MethodRoadie.java:87)
>         at org.junit.internal.runners.MethodRoadie.runTest(MethodRoadie.java:77)
>         at org.junit.internal.runners.MethodRoadie.run(MethodRoadie.java:42)
>         at org.junit.internal.runners.JUnit4ClassRunner.invokeTestMethod(JUnit4ClassRunner.java:88)
>         at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
>         at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
>         at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
>         at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
>         at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
>         at org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:62)
>         at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:138)
>         at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:125)
>         at org.apache.maven.surefire.Surefire.run(Surefire.java:132)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke(Method.java:585)
>         at org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:290)
>         at org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:818)
>

Mime
View raw message