Sybase EJBQL Subquery syntax
----------------------------
Key: CAY-908
URL: https://issues.apache.org/cayenne/browse/CAY-908
Project: Cayenne
Issue Type: Bug
Components: Cayenne Core Library
Affects Versions: 3.0
Reporter: Andrus Adamchik
Assignee: Andrus Adamchik
Priority: Minor
When testing on sybase, syntax (1) generates an exception below. Syntax (2), with only one column in subselect, works.
(1) SELECT DISTINCT t0.PAINTING_TITLE AS PAINTING_TITLE, t0.ARTIST_ID AS ARTIST_ID, t0.PAINTING_DESCRIPTION AS PAINTING_DESCRIPTION, t0.ESTIMATED_PRICE AS ESTIMATED_PRICE, t0.GALLERY_ID AS GALLERY_ID, t0.PAINTING_ID AS PAINTING_ID
FROM PAINTING t0
WHERE EXISTS
(SELECT DISTINCT t1.PAINTING_TITLE, t1.ARTIST_ID, t1.PAINTING_DESCRIPTION, t1.ESTIMATED_PRICE, t1.GALLERY_ID, t1.PAINTING_ID FROM PAINTING t1
WHERE t1.PAINTING_TITLE = t0.PAINTING_TITLE AND t0.ESTIMATED_PRICE <> t1.ESTIMATED_PRICE)
(2) SELECT DISTINCT t0.PAINTING_TITLE AS PAINTING_TITLE, t0.ARTIST_ID AS ARTIST_ID, t0.PAINTING_DESCRIPTION AS PAINTING_DESCRIPTION, t0.ESTIMATED_PRICE AS ESTIMATED_PRICE, t0.GALLERY_ID AS GALLERY_ID, t0.PAINTING_ID AS PAINTING_ID
FROM PAINTING t0
WHERE EXISTS
(SELECT DISTINCT t1.PAINTING_ID FROM PAINTING t1
WHERE t1.PAINTING_TITLE = t0.PAINTING_TITLE AND t0.ESTIMATED_PRICE <> t1.ESTIMATED_PRICE)
Oct 28, 2007 4:37:53 PM org.apache.cayenne.access.QueryLogger logQuery
INFO: SELECT DISTINCT t0.PAINTING_TITLE AS PAINTING_TITLE, t0.ARTIST_ID AS ARTIST_ID, t0.PAINTING_DESCRIPTION AS PAINTING_DESCRIPTION, t0.ESTIMATED_PRICE AS ESTIMATED_PRICE, t0.GALLERY_ID AS GALLERY_ID, t0.PAINTING_ID AS PAINTING_ID FROM PAINTING t0 WHERE EXISTS (SELECT DISTINCT t1.PAINTING_TITLE, t1.ARTIST_ID, t1.PAINTING_DESCRIPTION, t1.ESTIMATED_PRICE, t1.GALLERY_ID, t1.PAINTING_ID FROM PAINTING t1 WHERE t1.PAINTING_TITLE = t0.PAINTING_TITLE AND t0.ESTIMATED_PRICE <> t1.ESTIMATED_PRICE)
Oct 28, 2007 4:37:53 PM org.apache.cayenne.access.QueryLogger logQueryError
INFO: *** error.
com.sybase.jdbc3.jdbc.SybSQLException: Incorrect syntax near ','.
at com.sybase.jdbc3.tds.Tds.processEed(Tds.java:2942)
at com.sybase.jdbc3.tds.Tds.nextResult(Tds.java:2246)
at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(SybStatement.java:220)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(SybStatement.java:203)
at com.sybase.jdbc3.jdbc.SybStatement.executeLoop(SybStatement.java:1868)
at com.sybase.jdbc3.jdbc.SybStatement.execute(SybStatement.java:1860)
at com.sybase.jdbc3.jdbc.SybPreparedStatement.execute(SybPreparedStatement.java:640)
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:96)
at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:57)
at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:236)
at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:446)
at org.apache.cayenne.access.DataDomainQueryAction.access$0(DataDomainQueryAction.java:424)
at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:418)
at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:853)
at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:415)
at org.apache.cayenne.access.UnitTestDomainQueryAction.runQueryInTransaction(UnitTestDomainQueryAction.java:53)
at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:119)
at org.apache.cayenne.access.UnitTestDomainQueryAction.execute(UnitTestDomainQueryAction.java:48)
at org.apache.cayenne.access.UnitTestDomain.onQuery(UnitTestDomain.java:62)
at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:282)
at org.apache.cayenne.access.DataContextQueryAction.execute(DataContextQueryAction.java:59)
at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1323)
at org.apache.cayenne.access.DataContext.performQuery(DataContext.java:1312)
at org.apache.cayenne.access.DataContextEJBQLSubqueryTest.testExists(DataContextEJBQLSubqueryTest.java:48)
-- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
This archive was generated by hypermail 2.0.0 : Sun Oct 28 2007 - 10:50:20 EDT