You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "roger.keays" <ro...@ninthavenue.com.au> on 2008/05/20 03:43:54 UTC
1.1.0-SNAPSHOT: broken query in eager loaded relation
Hi All,
Here is a query which works fine in OpenJPA 1.0.2:
query = em.createQuery("SELECT item FROM Content item WHERE " +
"item.url=:url AND item.root.uuid=:id")
.setParameter("url", url)
.setParameter("id", id);
but in 1.1.0-SNAPSHOT the following exception is thrown:
<openjpa-1.1.0-SNAPSHOT-r422266:657918 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: ERROR: missing
FROM-clause entry for table "t0" {prepstmnt 1036977 SELECT t0.content,
t2.ITEM_UUID, t2.id, t2.inheritable, t2.level, t2.role, t2.type, t2.username
FROM CMS_CONTENT t1 LEFT OUTER JOIN CMS_PRIVILEGES t2 ON t1.uuid =
t2.ITEM_UUID WHERE t0.uuid = ? ORDER BY t2.ITEM_UUID ASC [params=(long) 4]}
[code=0, state=42P01]
org.apache.openjpa.jdbc.sql.SQLExceptions.narrow(SQLExceptions.java:146)
org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4150)
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:88)
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:64)
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:526)
org.apache.openjpa.kernel.DelegatingStoreManager.load(DelegatingStoreManager.java:116)
org.apache.openjpa.kernel.ROPStoreManager.load(ROPStoreManager.java:78)
org.apache.openjpa.kernel.StateManagerImpl.loadFields(StateManagerImpl.java:2911)
org.apache.openjpa.kernel.StateManagerImpl.load(StateManagerImpl.java:376)
org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:862)
org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:774)
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:811)
org.apache.openjpa.jdbc.sql.AbstractResult.load(AbstractResult.java:258)
org.apache.openjpa.jdbc.sql.SelectImpl$SelectResult.load(SelectImpl.java:2228)
org.apache.openjpa.jdbc.meta.strats.RelationFieldStrategy.loadEagerJoin(RelationFieldStrategy.java:510)
org.apache.openjpa.jdbc.meta.FieldMapping.loadEagerJoin(FieldMapping.java:790)
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:860)
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:841)
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:820)
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initializeState(JDBCStoreManager.java:342)
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initialize(JDBCStoreManager.java:263)
org.apache.openjpa.kernel.DelegatingStoreManager.initialize(DelegatingStoreManager.java:111)
org.apache.openjpa.kernel.ROPStoreManager.initialize(ROPStoreManager.java:57)
org.apache.openjpa.kernel.BrokerImpl.initialize(BrokerImpl.java:894)
org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:852)
org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:774)
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:811)
org.apache.openjpa.jdbc.sql.AbstractResult.load(AbstractResult.java:258)
org.apache.openjpa.jdbc.sql.SelectImpl$SelectResult.load(SelectImpl.java:2228)
org.apache.openjpa.jdbc.sql.AbstractResult.load(AbstractResult.java:252)
org.apache.openjpa.jdbc.kernel.InstanceResultObjectProvider.getResultObject(InstanceResultObjectProvider.java:59)
org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:36)
org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1222)
org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:989)
org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:847)
org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:778)
org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:525)
org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:237)
org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:277)
au.com.ninthavenue.cms.application.CMS.getItem(CMS.java:218)
I think the query should probably look something like this:
SELECT t2.ITEM_UUID, t2.id, t2.inheritable, t2.level, t2.role, t2.type,
t2.username FROM CMS_CONTENT t1 LEFT OUTER JOIN CMS_PRIVILEGES t2 ON t1.uuid
= t2.ITEM_UUID WHERE t1.uuid = ? ORDER BY t2.ITEM_UUID ASC [params=(long) 4]
Can't see where the t0.content field comes from (?). The CMS_PRIVILEGES
table is for an eager loaded list of Privilege entities. If I change it to
lazy loaded, the problem disappears.
Cheers,
Roger
--
View this message in context: http://www.nabble.com/1.1.0-SNAPSHOT%3A-broken-query-in-eager-loaded-relation-tp17331680p17331680.html
Sent from the OpenJPA Developers mailing list archive at Nabble.com.
Re: 1.1.0-SNAPSHOT: broken query in eager loaded relation
Posted by "roger.keays" <ro...@ninthavenue.com.au>.
Just for the record, I hit this problem again. Same thing, changing from
eager to lazy loading works around it.
public List<Invoice> getInvoices() { return invoices; }
<openjpa-1.1.0-SNAPSHOT-r422266:657918 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: ERROR: missing
FROM-clause entry for table "t0" {prepstmnt 12040505 SELECT t1.id, t2.id,
t3.id, t4.id, t2.ACCOUNT_ID, t2.amount, t2.comment, t5.id, t6.id,
t5.category, t5.code, t7.id, t7.description, t5.decimalPlaces,
t5.description, t6.category, t6.defaultFreight, t6.DEFAULTTAXTYPE_ID,
t6.POSTACCOUNT_ID, t6.units, t2.details, t2.reconciled, t2.TRANSACTION_ID,
t2.unitPrice, t3.discountedBy, t3.freightToAdd, t3.reference,
t3.TAXTOADD_ID, t4.INVOICE_ID FROM LED_TRANSACTIONS t1 INNER JOIN
LED_ENTRIES t2 ON t1.id = t2.TRANSACTION_ID LEFT OUTER JOIN
LED_INVOICE_ITEMS t3 ON t2.id = t3.id LEFT OUTER JOIN LED_PAYMENTS t4 ON
t2.id = t4.id INNER JOIN LED_COMMODITIES t5 ON t2.COMMODITY_ID = t5.id LEFT
OUTER JOIN LED_PRODUCTS t6 ON t5.id = t6.id LEFT OUTER JOIN LED_DATASETS t7
ON t5.DATASET_ID = t7.id WHERE t0.CUSTOMER_ID = ? ORDER BY t1.id ASC,
t2.amount ASC [params=(long) 59]} [code=0, state=42P01]
at
org.apache.openjpa.jdbc.sql.SQLExceptions.narrow(SQLExceptions.java:146)
at
org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4150)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:88)
at
org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:64)
at
org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:526)
at
org.apache.openjpa.kernel.DelegatingStoreManager.load(DelegatingStoreManager.java:116)
at
org.apache.openjpa.kernel.ROPStoreManager.load(ROPStoreManager.java:78)
at
org.apache.openjpa.kernel.StateManagerImpl.loadFields(StateManagerImpl.java:2911)
at
org.apache.openjpa.kernel.StateManagerImpl.loadField(StateManagerImpl.java:2989)
at
org.apache.openjpa.kernel.StateManagerImpl.beforeAccessField(StateManagerImpl.java:1489)
at
org.apache.openjpa.kernel.StateManagerImpl.accessingField(StateManagerImpl.java:1474)
at
au.com.ninthavenue.ledger.entities.Customer.pcGetinvoices(Customer.java)
at
au.com.ninthavenue.ledger.entities.Customer.getInvoices(Customer.java:95)
at
au.com.ninthavenue.ledger.actions.AddInvoice.processAction(AddInvoice.java:74)
roger.keays wrote:
>
> Hi All,
>
> Here is a query which works fine in OpenJPA 1.0.2:
>
> query = em.createQuery("SELECT item FROM Content item WHERE " +
> "item.url=:url AND item.root.uuid=:id")
> .setParameter("url", url)
> .setParameter("id", id);
>
> but in 1.1.0-SNAPSHOT the following exception is thrown:
>
> <openjpa-1.1.0-SNAPSHOT-r422266:657918 nonfatal general error>
> org.apache.openjpa.persistence.PersistenceException: ERROR: missing
> FROM-clause entry for table "t0" {prepstmnt 1036977 SELECT t0.content,
> t2.ITEM_UUID, t2.id, t2.inheritable, t2.level, t2.role, t2.type,
> t2.username FROM CMS_CONTENT t1 LEFT OUTER JOIN CMS_PRIVILEGES t2 ON
> t1.uuid = t2.ITEM_UUID WHERE t0.uuid = ? ORDER BY t2.ITEM_UUID ASC
> [params=(long) 4]} [code=0, state=42P01]
> org.apache.openjpa.jdbc.sql.SQLExceptions.narrow(SQLExceptions.java:146)
>
> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4150)
>
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:88)
> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:64)
>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:526)
>
> org.apache.openjpa.kernel.DelegatingStoreManager.load(DelegatingStoreManager.java:116)
> org.apache.openjpa.kernel.ROPStoreManager.load(ROPStoreManager.java:78)
>
> org.apache.openjpa.kernel.StateManagerImpl.loadFields(StateManagerImpl.java:2911)
>
> org.apache.openjpa.kernel.StateManagerImpl.load(StateManagerImpl.java:376)
> org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:862)
> org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:774)
>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:811)
> org.apache.openjpa.jdbc.sql.AbstractResult.load(AbstractResult.java:258)
>
> org.apache.openjpa.jdbc.sql.SelectImpl$SelectResult.load(SelectImpl.java:2228)
>
> org.apache.openjpa.jdbc.meta.strats.RelationFieldStrategy.loadEagerJoin(RelationFieldStrategy.java:510)
>
> org.apache.openjpa.jdbc.meta.FieldMapping.loadEagerJoin(FieldMapping.java:790)
>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:860)
>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:841)
>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:820)
>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initializeState(JDBCStoreManager.java:342)
>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initialize(JDBCStoreManager.java:263)
>
> org.apache.openjpa.kernel.DelegatingStoreManager.initialize(DelegatingStoreManager.java:111)
>
> org.apache.openjpa.kernel.ROPStoreManager.initialize(ROPStoreManager.java:57)
> org.apache.openjpa.kernel.BrokerImpl.initialize(BrokerImpl.java:894)
> org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:852)
> org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:774)
>
> org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:811)
> org.apache.openjpa.jdbc.sql.AbstractResult.load(AbstractResult.java:258)
>
> org.apache.openjpa.jdbc.sql.SelectImpl$SelectResult.load(SelectImpl.java:2228)
> org.apache.openjpa.jdbc.sql.AbstractResult.load(AbstractResult.java:252)
>
> org.apache.openjpa.jdbc.kernel.InstanceResultObjectProvider.getResultObject(InstanceResultObjectProvider.java:59)
>
> org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:36)
> org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1222)
> org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:989)
> org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:847)
> org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:778)
>
> org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:525)
> org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:237)
>
> org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:277)
> au.com.ninthavenue.cms.application.CMS.getItem(CMS.java:218)
>
>
> I think the query should probably look something like this:
>
> SELECT t2.ITEM_UUID, t2.id, t2.inheritable, t2.level, t2.role, t2.type,
> t2.username FROM CMS_CONTENT t1 LEFT OUTER JOIN CMS_PRIVILEGES t2 ON
> t1.uuid = t2.ITEM_UUID WHERE t1.uuid = ? ORDER BY t2.ITEM_UUID ASC
> [params=(long) 4]
>
> Can't see where the t0.content field comes from (?). The CMS_PRIVILEGES
> table is for an eager loaded list of Privilege entities. If I change it to
> lazy loaded, the problem disappears.
>
> Cheers,
>
> Roger
>
--
View this message in context: http://www.nabble.com/1.1.0-SNAPSHOT%3A-broken-query-in-eager-loaded-relation-tp17331680p17534302.html
Sent from the OpenJPA Developers mailing list archive at Nabble.com.