Looks like a Cayenne bug to me... t2 is nowhere to be found in the
translated query. besides a join to manufacturer should probably be an
OUTER JOIN. Would you mind opening a bug report please?
https://issues.apache.org/cayenne/
Thanks,
Andrus
On Apr 16, 2008, at 6:24 PM, Jeff Pierson wrote:
> I am running into an error that I think might be a bug in the EJBQL
> implementation unless I'm doing something wrong that I'm not
> seeing. I
> am running the following EJBQL query against a MySQL 5.0 database with
> Cayenne 3.0M3, it looks valid to me:
>
> SELECT COUNT(p) from Product p where p.vsCatalog.id = 1 and
> (
> p.displayName like '%rimadyl%'
> or p.manufacturer.name like '%rimadyl%'
> or p.description like '%rimadyl%'
> or p.longdescription like '%rimadyl%'
> or p.longdescription2 like '%rimadyl%'
> or p.manufacturerPartNumber like '%rimadyl%'
> or p.partNumber like '%rimadyl%'
> )
>
> The error log shows that the SQL it produced is:
>
> SELECT COUNT(*) AS sc0 FROM product t0 INNER JOIN
> catalogmanager.vs_catalog t1 ON (t0.vs_catalog_id = t1.id) INNER JOIN
> manufacturer t3 ON (t0.manufacturer_id = t3.id) WHERE t2.id = ? AND
> t0.display_name LIKE ? OR t3.name LIKE ? OR t0.description LIKE ? OR
> t0.longdescription LIKE ? OR t0.longdescription2 LIKE ? OR
> t0.mfg_partnum LIKE ? OR t0.partnum LIKE ?
>
> And the SQLException:
>
> com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column
> 't2.id' in 'where clause'
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:
> 936)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
> at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
> at
> com
> .mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:
> 1153)
> at
> com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:794)
> at
> org
> .apache
> .cayenne
> .access.jdbc.SQLTemplateAction.execute(SQLTemplateAction.java:133)
> at
> org
> .apache
> .cayenne
> .access.jdbc.SQLTemplateAction.performAction(SQLTemplateAction.java:
> 107)
> at
> org
> .apache
> .cayenne.access.jdbc.EJBQLAction.performAction(EJBQLAction.java:100)
> at
> org
> .apache
> .cayenne
> .access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:58)
> at
> org.apache.cayenne.access.DataNode.performQueries(DataNode.java:230)
> at
> org
> .apache
> .cayenne
> .access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:442)
> at org.apache.cayenne.access.DataDomainQueryAction.access
> $000(DataDomainQueryAction.java:67)
> at org.apache.cayenne.access.DataDomainQueryAction
> $2.transform(DataDomainQueryAction.java:415)
> at
> org
> .apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:
> 847)
> at
> org
> .apache
> .cayenne
> .access
> .DataDomainQueryAction
> .runQueryInTransaction(DataDomainQueryAction.java:412)
> at
> org
> .apache
> .cayenne
> .access.DataDomainQueryAction.execute(DataDomainQueryAction.java:119)
> at
> org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:740)
> at
> org
> .apache
> .cayenne
> .util
> .ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:296)
> at
> org
> .apache
> .cayenne
> .util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:
> 84)
> at
> org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1331)
> at
> org.apache.cayenne.access.DataContext.performQuery(DataContext.java:
> 1320)
> at
> com
> .vetsource
> .dashboard
> .catalogmanager
> .web.ProductDataProvider.size(ProductDataProvider.java:76)
> ...
>
> Three objects are involved, Catalog, Product, and Manufacturer. I can
> provide the source code and the .map.xml file if necessary. The query
> was working in this form before I had to add the AND and the
> parentheses
> around all of the OR'ed conditions:
>
> SELECT COUNT(p) from Product p where
> p.displayName like '%rimadyl%'
> or p.manufacturer.name like '%rimadyl%'
> or p.description like '%rimadyl%'
> or p.longdescription like '%rimadyl%'
> or p.longdescription2 like '%rimadyl%'
> or p.manufacturerPartNumber like '%rimadyl%'
> or p.partNumber like '%rimadyl%'
>
> Any ideas? Thanks.
>
> Jeff Pierson
>
>
>
>
>
This archive was generated by hypermail 2.0.0 : Wed Apr 16 2008 - 11:35:00 EDT