[jira] Closed: (CAY-1032) EJBQL Query produces invalid SQL

["Andrey Razumovsky (JIRA)" <ji...@apache.org>]

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

Andrey Razumovsky closed CAY-1032.
----------------------------------

    Resolution: Duplicate

Added a test with absolutely same set of joins:
select count(p) from Painting p where p.toGallery.galleryName LIKE '%a%' AND (
            p.paintingTitle like '%a%' or
            p.toArtist.artistName like '%a%'
)

This shows that issue had already been fixed (likely CAY-1341)

> EJBQL Query produces invalid SQL
> --------------------------------
>
>                 Key: CAY-1032
>                 URL: https://issues.apache.org/jira/browse/CAY-1032
>             Project: Cayenne
>          Issue Type: Bug
>          Components: Core Library
>    Affects Versions: 3.0
>         Environment: Centos 5
> Mysql 5.0.51
> Cayenne 3.0M3
>            Reporter: Jeff Pierson
>            Assignee: Andrey Razumovsky
>             Fix For: 3.0RC2
>
>
> The following EJBQL query produces invalid SQL:
> 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)
> ...

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