You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by "Lachlan Deck (JIRA)" <de...@cayenne.apache.org> on 2008/06/12 04:29:52 UTC
[jira] Created: (CAY-1074) Wrong SQL Generation for orderings
Wrong SQL Generation for orderings
----------------------------------
Key: CAY-1074
URL: https://issues.apache.org/cayenne/browse/CAY-1074
Project: Cayenne
Issue Type: Bug
Components: Cayenne Core Library
Affects Versions: 3.0
Environment: N/A
Reporter: Lachlan Deck
Assignee: Andrus Adamchik
Priority: Critical
Pay particular attention to the t0.lastName DESC.
logger.warn("search query to run:" + query + " expression:" + qualifier + " orderings:" + orderings);
WARN - search query to run:org.apache.cayenne.query.SelectQuery@39f0bb[root=class ish.oncourse.cayenne.Contact,name=<null>] expression:(isStudent = true) and ((student.enrolments.courseClass.endDateTime >= Thu Jun 12 00:00:00 EST 2008) or (student.enrolments.courseClass.endDateTime = null)) and (student.enrolments.courseClass.isCancelled != true) orderings:[<ordering descending="true"><![CDATA[lastName]]></ordering>, <ordering descending="true"><![CDATA[firstName]]></ordering>]
--- generated sql ---
SELECT DISTINCT t0.id, t0.lastName, t0.lastName DESC, t0.firstName FROM Contact t0 JOIN Student t1 ON (t0.id = t1.contactId) JOIN Enrolment t2 ON (t1.id = t2.studentId) JOIN CourseClass t3 ON (t2.courseClass_Id = t3.id) WHERE (t0.isStudent = ?) AND ((t3.endDateTime >= ?) OR (t3.endDateTime IS NULL)) AND (t3.isCancelled <> ?) AND ((t0.isDeleted IS NULL) OR (t0.isDeleted = ?)) ORDER BY t0.lastName DESC, t0.firstName DESC [bind: 1->isStudent:'true', 2->endDateTime:'2008-06-11 00:00:00.0', 3->isCancelled:'true', 4->isDeleted:0]
--- stack trace ---
[java] org.apache.cayenne.CayenneRuntimeException: [v.3.0-SNAPSHOT May 14 2008 22:35:23] Remote error. URL - <...>; CAUSE - [v.3.0-SNAPSHOT May 14 2008 22:35:23] Error getting ResultIterator: Query Exception:
[java] java.sql.SQLException: Syntax error: Encountered "DESC" at line 1, column 49.
[java] at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
[java] at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
[java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
[java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
[java] at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
[java] at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
[java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
[java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
[java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
[java] at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
[java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
[java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
[java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:274)
[java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:280)
[java] at org.apache.cayenne.access.TransactionConnectionDecorator.prepareStatement(TransactionConnectionDecorator.java:179)
[java] at org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:118)
[java] at org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:72)
[java] at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:58)
[java] at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:230)
[java] at org.apache.cayenne.access.DataDomainLegacyQueryAction.execute(DataDomainLegacyQueryAction.java:81)
[java] at org.apache.cayenne.access.DataDomain$1.transform(DataDomain.java:717)
[java] at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:837)
[java] at org.apache.cayenne.access.DataDomain.performQueries(DataDomain.java:714)
[java] at org.apache.cayenne.access.DataContext.internalPerformIteratedQuery(DataContext.java:1273)
[java] at org.apache.cayenne.access.DataContext.performIteratedQuery(DataContext.java:1239)
[java] at org.apache.cayenne.access.IncrementalFaultList.fillIn(IncrementalFaultList.java:218)
[java] at org.apache.cayenne.access.IncrementalFaultList.<init>(IncrementalFaultList.java:163)
[java] at org.apache.cayenne.access.SimpleIdIncrementalFaultList.<init>(SimpleIdIncrementalFaultList.java:43)
[java] at org.apache.cayenne.access.DataContextQueryAction.interceptPaginatedQuery(DataContextQueryAction.java:93)
[java] at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:95)
[java] at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1336)
[java] at org.apache.cayenne.access.ClientServerChannelQueryAction.runQuery(ClientServerChannelQueryAction.java:120)
[java] at org.apache.cayenne.access.ClientServerChannelQueryAction.execute(ClientServerChannelQueryAction.java:66)
[java] at org.apache.cayenne.access.ClientServerChannel.onQuery(ClientServerChannel.java:82)
[java] at org.apache.cayenne.remote.service.DispatchHelper.dispatch(DispatchHelper.java:40)
[java] at org.apache.cayenne.remote.service.BaseRemoteService.processMessage(BaseRemoteService.java:153)
[java] at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
[java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[java] at java.lang.reflect.Method.invoke(Method.java:585)
[java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:180)
[java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:109)
[java] at com.caucho.hessian.server.HessianServlet.service(HessianServlet.java:393)
[java] at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
[java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
[java] at ish.oncourse.server.SecurityFilter.doFilter(SecurityFilter.java:112)
[java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
[java] at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
[java] at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
[java] at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
[java] at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)
[java] at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
[java] at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
[java] at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
[java] at org.mortbay.jetty.Server.handle(Server.java:324)
[java] at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
[java] at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:842)
[java] at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:730)
[java] at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
[java] at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
[java] at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)
[java] at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
[java] Caused by: ERROR 42X01: Syntax error: Encountered "DESC" at line 1, column 49.
[java] at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
[java] at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
[java] at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
[java] at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
[java] at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
[java] ... 55 more
[java]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
Re: [jira] Commented: (CAY-1074) Derby: Wrong SQL Generated for select items -- DESC follows column name
Posted by Mike Kienenberger <mk...@gmail.com>.
You're right in that it's probably not Derby-specific.
I didn't notice that the original problem was in the select items area
rather than the orderings area, and I actually went to the derby web
site to see if it supported "order by COLUMN desc". That's why I
added the comment and changed the subject -- just to help clarify the
issue in case someone else has the same myopic reading that I did :-)
On Thu, Jun 12, 2008 at 10:18 AM, Ari Maniatis (JIRA)
<de...@cayenne.apache.org> wrote:
>
> [ https://issues.apache.org/cayenne/browse/CAY-1074?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12956#action_12956 ]
>
> Ari Maniatis commented on CAY-1074:
> -----------------------------------
>
> That was Lachlan's point here. And I don't know that this is Derby specific...
>
> Just another data point: when we changed the ordering so that it sorts on just one field, the problem goes away. The issue exists only when ordering on the two fields (first name, last name in this example).
>
> Environment:
>
> * ROP
> * paged query
> * M4 build (or pretty close to)
>
> What *should* be happening I think is that neither of these fields should appear in the select since the query should just fetch IDs for the paging.
>
>> Derby: Wrong SQL Generated for select items -- DESC follows column name
>> -----------------------------------------------------------------------
>>
>> Key: CAY-1074
>> URL: https://issues.apache.org/cayenne/browse/CAY-1074
>> Project: Cayenne
>> Issue Type: Bug
>> Components: Cayenne Core Library
>> Affects Versions: 3.0
>> Environment: N/A
>> Reporter: Lachlan Deck
>> Assignee: Andrus Adamchik
>> Priority: Critical
>>
>> Pay particular attention to the t0.lastName DESC.
>> logger.warn("search query to run:" + query + " expression:" + qualifier + " orderings:" + orderings);
>> WARN - search query to run:org.apache.cayenne.query.SelectQuery@39f0bb[root=class ish.oncourse.cayenne.Contact,name=<null>] expression:(isStudent = true) and ((student.enrolments.courseClass.endDateTime >= Thu Jun 12 00:00:00 EST 2008) or (student.enrolments.courseClass.endDateTime = null)) and (student.enrolments.courseClass.isCancelled != true) orderings:[<ordering descending="true"><![CDATA[lastName]]></ordering>, <ordering descending="true"><![CDATA[firstName]]></ordering>]
>> --- generated sql ---
>> SELECT DISTINCT t0.id, t0.lastName, t0.lastName DESC, t0.firstName FROM Contact t0 JOIN Student t1 ON (t0.id = t1.contactId) JOIN Enrolment t2 ON (t1.id = t2.studentId) JOIN CourseClass t3 ON (t2.courseClass_Id = t3.id) WHERE (t0.isStudent = ?) AND ((t3.endDateTime >= ?) OR (t3.endDateTime IS NULL)) AND (t3.isCancelled <> ?) AND ((t0.isDeleted IS NULL) OR (t0.isDeleted = ?)) ORDER BY t0.lastName DESC, t0.firstName DESC [bind: 1->isStudent:'true', 2->endDateTime:'2008-06-11 00:00:00.0', 3->isCancelled:'true', 4->isDeleted:0]
>> --- stack trace ---
>> [java] org.apache.cayenne.CayenneRuntimeException: [v.3.0-SNAPSHOT May 14 2008 22:35:23] Remote error. URL - <...>; CAUSE - [v.3.0-SNAPSHOT May 14 2008 22:35:23] Error getting ResultIterator: Query Exception:
>> [java] java.sql.SQLException: Syntax error: Encountered "DESC" at line 1, column 49.
>> [java] at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
>> [java] at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
>> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
>> [java] at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
>> [java] at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
>> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
>> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
>> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
>> [java] at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
>> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
>> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
>> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:274)
>> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:280)
>> [java] at org.apache.cayenne.access.TransactionConnectionDecorator.prepareStatement(TransactionConnectionDecorator.java:179)
>> [java] at org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:118)
>> [java] at org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:72)
>> [java] at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:58)
>> [java] at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:230)
>> [java] at org.apache.cayenne.access.DataDomainLegacyQueryAction.execute(DataDomainLegacyQueryAction.java:81)
>> [java] at org.apache.cayenne.access.DataDomain$1.transform(DataDomain.java:717)
>> [java] at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:837)
>> [java] at org.apache.cayenne.access.DataDomain.performQueries(DataDomain.java:714)
>> [java] at org.apache.cayenne.access.DataContext.internalPerformIteratedQuery(DataContext.java:1273)
>> [java] at org.apache.cayenne.access.DataContext.performIteratedQuery(DataContext.java:1239)
>> [java] at org.apache.cayenne.access.IncrementalFaultList.fillIn(IncrementalFaultList.java:218)
>> [java] at org.apache.cayenne.access.IncrementalFaultList.<init>(IncrementalFaultList.java:163)
>> [java] at org.apache.cayenne.access.SimpleIdIncrementalFaultList.<init>(SimpleIdIncrementalFaultList.java:43)
>> [java] at org.apache.cayenne.access.DataContextQueryAction.interceptPaginatedQuery(DataContextQueryAction.java:93)
>> [java] at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:95)
>> [java] at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1336)
>> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.runQuery(ClientServerChannelQueryAction.java:120)
>> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.execute(ClientServerChannelQueryAction.java:66)
>> [java] at org.apache.cayenne.access.ClientServerChannel.onQuery(ClientServerChannel.java:82)
>> [java] at org.apache.cayenne.remote.service.DispatchHelper.dispatch(DispatchHelper.java:40)
>> [java] at org.apache.cayenne.remote.service.BaseRemoteService.processMessage(BaseRemoteService.java:153)
>> [java] at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
>> [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>> [java] at java.lang.reflect.Method.invoke(Method.java:585)
>> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:180)
>> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:109)
>> [java] at com.caucho.hessian.server.HessianServlet.service(HessianServlet.java:393)
>> [java] at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
>> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
>> [java] at ish.oncourse.server.SecurityFilter.doFilter(SecurityFilter.java:112)
>> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
>> [java] at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
>> [java] at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
>> [java] at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
>> [java] at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)
>> [java] at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
>> [java] at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
>> [java] at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
>> [java] at org.mortbay.jetty.Server.handle(Server.java:324)
>> [java] at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
>> [java] at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:842)
>> [java] at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:730)
>> [java] at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
>> [java] at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
>> [java] at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)
>> [java] at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
>> [java] Caused by: ERROR 42X01: Syntax error: Encountered "DESC" at line 1, column 49.
>> [java] at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>> [java] at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
>> [java] at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
>> [java] at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
>> [java] at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
>> [java] ... 55 more
>> [java]
>
> --
> This message is automatically generated by JIRA.
> -
> You can reply to this email to add a comment to the issue online.
>
>
[jira] Updated: (CAY-1074) Derby: Wrong SQL Generated for select
items -- DESC follows column name
Posted by "Mike Kienenberger (JIRA)" <de...@cayenne.apache.org>.
[ https://issues.apache.org/cayenne/browse/CAY-1074?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Kienenberger updated CAY-1074:
-----------------------------------
Summary: Derby: Wrong SQL Generated for select items -- DESC follows column name (was: Wrong SQL Generation for orderings)
The problem isn't in the ordering sql generated, it's in the select item sql generated. Strange that t0.lastName is followed by DESC (like in the ordering), but t0.firstName is not, despite also being followed by DESC.
> Derby: Wrong SQL Generated for select items -- DESC follows column name
> -----------------------------------------------------------------------
>
> Key: CAY-1074
> URL: https://issues.apache.org/cayenne/browse/CAY-1074
> Project: Cayenne
> Issue Type: Bug
> Components: Cayenne Core Library
> Affects Versions: 3.0
> Environment: N/A
> Reporter: Lachlan Deck
> Assignee: Andrus Adamchik
> Priority: Critical
>
> Pay particular attention to the t0.lastName DESC.
> logger.warn("search query to run:" + query + " expression:" + qualifier + " orderings:" + orderings);
> WARN - search query to run:org.apache.cayenne.query.SelectQuery@39f0bb[root=class ish.oncourse.cayenne.Contact,name=<null>] expression:(isStudent = true) and ((student.enrolments.courseClass.endDateTime >= Thu Jun 12 00:00:00 EST 2008) or (student.enrolments.courseClass.endDateTime = null)) and (student.enrolments.courseClass.isCancelled != true) orderings:[<ordering descending="true"><![CDATA[lastName]]></ordering>, <ordering descending="true"><![CDATA[firstName]]></ordering>]
> --- generated sql ---
> SELECT DISTINCT t0.id, t0.lastName, t0.lastName DESC, t0.firstName FROM Contact t0 JOIN Student t1 ON (t0.id = t1.contactId) JOIN Enrolment t2 ON (t1.id = t2.studentId) JOIN CourseClass t3 ON (t2.courseClass_Id = t3.id) WHERE (t0.isStudent = ?) AND ((t3.endDateTime >= ?) OR (t3.endDateTime IS NULL)) AND (t3.isCancelled <> ?) AND ((t0.isDeleted IS NULL) OR (t0.isDeleted = ?)) ORDER BY t0.lastName DESC, t0.firstName DESC [bind: 1->isStudent:'true', 2->endDateTime:'2008-06-11 00:00:00.0', 3->isCancelled:'true', 4->isDeleted:0]
> --- stack trace ---
> [java] org.apache.cayenne.CayenneRuntimeException: [v.3.0-SNAPSHOT May 14 2008 22:35:23] Remote error. URL - <...>; CAUSE - [v.3.0-SNAPSHOT May 14 2008 22:35:23] Error getting ResultIterator: Query Exception:
> [java] java.sql.SQLException: Syntax error: Encountered "DESC" at line 1, column 49.
> [java] at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
> [java] at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:274)
> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:280)
> [java] at org.apache.cayenne.access.TransactionConnectionDecorator.prepareStatement(TransactionConnectionDecorator.java:179)
> [java] at org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:118)
> [java] at org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:72)
> [java] at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:58)
> [java] at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:230)
> [java] at org.apache.cayenne.access.DataDomainLegacyQueryAction.execute(DataDomainLegacyQueryAction.java:81)
> [java] at org.apache.cayenne.access.DataDomain$1.transform(DataDomain.java:717)
> [java] at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:837)
> [java] at org.apache.cayenne.access.DataDomain.performQueries(DataDomain.java:714)
> [java] at org.apache.cayenne.access.DataContext.internalPerformIteratedQuery(DataContext.java:1273)
> [java] at org.apache.cayenne.access.DataContext.performIteratedQuery(DataContext.java:1239)
> [java] at org.apache.cayenne.access.IncrementalFaultList.fillIn(IncrementalFaultList.java:218)
> [java] at org.apache.cayenne.access.IncrementalFaultList.<init>(IncrementalFaultList.java:163)
> [java] at org.apache.cayenne.access.SimpleIdIncrementalFaultList.<init>(SimpleIdIncrementalFaultList.java:43)
> [java] at org.apache.cayenne.access.DataContextQueryAction.interceptPaginatedQuery(DataContextQueryAction.java:93)
> [java] at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:95)
> [java] at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1336)
> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.runQuery(ClientServerChannelQueryAction.java:120)
> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.execute(ClientServerChannelQueryAction.java:66)
> [java] at org.apache.cayenne.access.ClientServerChannel.onQuery(ClientServerChannel.java:82)
> [java] at org.apache.cayenne.remote.service.DispatchHelper.dispatch(DispatchHelper.java:40)
> [java] at org.apache.cayenne.remote.service.BaseRemoteService.processMessage(BaseRemoteService.java:153)
> [java] at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
> [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> [java] at java.lang.reflect.Method.invoke(Method.java:585)
> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:180)
> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:109)
> [java] at com.caucho.hessian.server.HessianServlet.service(HessianServlet.java:393)
> [java] at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
> [java] at ish.oncourse.server.SecurityFilter.doFilter(SecurityFilter.java:112)
> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
> [java] at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
> [java] at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
> [java] at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
> [java] at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)
> [java] at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
> [java] at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
> [java] at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
> [java] at org.mortbay.jetty.Server.handle(Server.java:324)
> [java] at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
> [java] at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:842)
> [java] at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:730)
> [java] at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
> [java] at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
> [java] at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)
> [java] at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
> [java] Caused by: ERROR 42X01: Syntax error: Encountered "DESC" at line 1, column 49.
> [java] at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> [java] at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
> [java] at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
> [java] at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> [java] at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
> [java] ... 55 more
> [java]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (CAY-1074) Derby: Wrong SQL Generated for select
items -- DESC follows column name
Posted by "Andrus Adamchik (JIRA)" <de...@cayenne.apache.org>.
[ https://issues.apache.org/cayenne/browse/CAY-1074?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12958#action_12958 ]
Andrus Adamchik commented on CAY-1074:
--------------------------------------
No luck reproducing it so far. This unit test works, generating valid SQL:
SELECT t0.ARTIST_NAME, t0.ARTIST_ID, t0.DATE_OF_BIRTH FROM ARTIST t0 ORDER BY t0.ARTIST_NAME DESC, t0.DATE_OF_BIRTH DESC
Calendar c = Calendar.getInstance();
DataContext context = createDataContext();
Artist a1 = context.newObject(Artist.class);
a1.setArtistName("2");
a1.setDateOfBirth(c.getTime());
c.add(Calendar.DAY_OF_MONTH, -1);
Artist a2 = context.newObject(Artist.class);
a2.setArtistName("3");
a2.setDateOfBirth(c.getTime());
c.add(Calendar.DAY_OF_MONTH, -1);
Artist a3 = context.newObject(Artist.class);
a3.setArtistName("3");
a3.setDateOfBirth(c.getTime());
context.commitChanges();
SelectQuery query = new SelectQuery(Artist.class);
query.addOrdering(Artist.ARTIST_NAME_PROPERTY, Ordering.DESC);
query.addOrdering(Artist.DATE_OF_BIRTH_PROPERTY, Ordering.DESC);
List<Artist> list = context.performQuery(query);
assertEquals(3, list.size());
assertSame(a2, list.get(0));
assertSame(a3, list.get(1));
assertSame(a1, list.get(2));
> Derby: Wrong SQL Generated for select items -- DESC follows column name
> -----------------------------------------------------------------------
>
> Key: CAY-1074
> URL: https://issues.apache.org/cayenne/browse/CAY-1074
> Project: Cayenne
> Issue Type: Bug
> Components: Cayenne Core Library
> Affects Versions: 3.0
> Environment: N/A
> Reporter: Lachlan Deck
> Assignee: Andrus Adamchik
> Priority: Critical
>
> Pay particular attention to the t0.lastName DESC.
> logger.warn("search query to run:" + query + " expression:" + qualifier + " orderings:" + orderings);
> WARN - search query to run:org.apache.cayenne.query.SelectQuery@39f0bb[root=class ish.oncourse.cayenne.Contact,name=<null>] expression:(isStudent = true) and ((student.enrolments.courseClass.endDateTime >= Thu Jun 12 00:00:00 EST 2008) or (student.enrolments.courseClass.endDateTime = null)) and (student.enrolments.courseClass.isCancelled != true) orderings:[<ordering descending="true"><![CDATA[lastName]]></ordering>, <ordering descending="true"><![CDATA[firstName]]></ordering>]
> --- generated sql ---
> SELECT DISTINCT t0.id, t0.lastName, t0.lastName DESC, t0.firstName FROM Contact t0 JOIN Student t1 ON (t0.id = t1.contactId) JOIN Enrolment t2 ON (t1.id = t2.studentId) JOIN CourseClass t3 ON (t2.courseClass_Id = t3.id) WHERE (t0.isStudent = ?) AND ((t3.endDateTime >= ?) OR (t3.endDateTime IS NULL)) AND (t3.isCancelled <> ?) AND ((t0.isDeleted IS NULL) OR (t0.isDeleted = ?)) ORDER BY t0.lastName DESC, t0.firstName DESC [bind: 1->isStudent:'true', 2->endDateTime:'2008-06-11 00:00:00.0', 3->isCancelled:'true', 4->isDeleted:0]
> --- stack trace ---
> [java] org.apache.cayenne.CayenneRuntimeException: [v.3.0-SNAPSHOT May 14 2008 22:35:23] Remote error. URL - <...>; CAUSE - [v.3.0-SNAPSHOT May 14 2008 22:35:23] Error getting ResultIterator: Query Exception:
> [java] java.sql.SQLException: Syntax error: Encountered "DESC" at line 1, column 49.
> [java] at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
> [java] at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:274)
> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:280)
> [java] at org.apache.cayenne.access.TransactionConnectionDecorator.prepareStatement(TransactionConnectionDecorator.java:179)
> [java] at org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:118)
> [java] at org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:72)
> [java] at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:58)
> [java] at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:230)
> [java] at org.apache.cayenne.access.DataDomainLegacyQueryAction.execute(DataDomainLegacyQueryAction.java:81)
> [java] at org.apache.cayenne.access.DataDomain$1.transform(DataDomain.java:717)
> [java] at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:837)
> [java] at org.apache.cayenne.access.DataDomain.performQueries(DataDomain.java:714)
> [java] at org.apache.cayenne.access.DataContext.internalPerformIteratedQuery(DataContext.java:1273)
> [java] at org.apache.cayenne.access.DataContext.performIteratedQuery(DataContext.java:1239)
> [java] at org.apache.cayenne.access.IncrementalFaultList.fillIn(IncrementalFaultList.java:218)
> [java] at org.apache.cayenne.access.IncrementalFaultList.<init>(IncrementalFaultList.java:163)
> [java] at org.apache.cayenne.access.SimpleIdIncrementalFaultList.<init>(SimpleIdIncrementalFaultList.java:43)
> [java] at org.apache.cayenne.access.DataContextQueryAction.interceptPaginatedQuery(DataContextQueryAction.java:93)
> [java] at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:95)
> [java] at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1336)
> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.runQuery(ClientServerChannelQueryAction.java:120)
> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.execute(ClientServerChannelQueryAction.java:66)
> [java] at org.apache.cayenne.access.ClientServerChannel.onQuery(ClientServerChannel.java:82)
> [java] at org.apache.cayenne.remote.service.DispatchHelper.dispatch(DispatchHelper.java:40)
> [java] at org.apache.cayenne.remote.service.BaseRemoteService.processMessage(BaseRemoteService.java:153)
> [java] at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
> [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> [java] at java.lang.reflect.Method.invoke(Method.java:585)
> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:180)
> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:109)
> [java] at com.caucho.hessian.server.HessianServlet.service(HessianServlet.java:393)
> [java] at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
> [java] at ish.oncourse.server.SecurityFilter.doFilter(SecurityFilter.java:112)
> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
> [java] at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
> [java] at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
> [java] at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
> [java] at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)
> [java] at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
> [java] at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
> [java] at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
> [java] at org.mortbay.jetty.Server.handle(Server.java:324)
> [java] at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
> [java] at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:842)
> [java] at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:730)
> [java] at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
> [java] at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
> [java] at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)
> [java] at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
> [java] Caused by: ERROR 42X01: Syntax error: Encountered "DESC" at line 1, column 49.
> [java] at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> [java] at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
> [java] at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
> [java] at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> [java] at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
> [java] ... 55 more
> [java]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (CAY-1074) Derby: Wrong SQL Generated for select
items -- DESC follows column name
Posted by "Andrus Adamchik (JIRA)" <de...@cayenne.apache.org>.
[ https://issues.apache.org/cayenne/browse/CAY-1074?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12959#action_12959 ]
Andrus Adamchik commented on CAY-1074:
--------------------------------------
Ok, now I am getting somewhere... adding a JOIN to the SelectQuery caused that:
query.andQualifier(ExpressionFactory.noMatchExp(Artist.PAINTING_ARRAY_PROPERTY , null));
SELECT DISTINCT t0.ARTIST_NAME, t0.ARTIST_ID, t0.DATE_OF_BIRTH, t0.ARTIST_NAME DESC, t0.DATE_OF_BIRTH FROM ARTIST t0 JOIN PAINTING t1 ON (t0.ARTIST_ID = t1.ARTIST_ID) WHERE t1.PAINTING_ID IS NOT NULL ORDER BY t0.ARTIST_NAME DESC, t0.DATE_OF_BIRTH DESC - prepared in 19 ms.
> Derby: Wrong SQL Generated for select items -- DESC follows column name
> -----------------------------------------------------------------------
>
> Key: CAY-1074
> URL: https://issues.apache.org/cayenne/browse/CAY-1074
> Project: Cayenne
> Issue Type: Bug
> Components: Cayenne Core Library
> Affects Versions: 3.0
> Environment: N/A
> Reporter: Lachlan Deck
> Assignee: Andrus Adamchik
> Priority: Critical
>
> Pay particular attention to the t0.lastName DESC.
> logger.warn("search query to run:" + query + " expression:" + qualifier + " orderings:" + orderings);
> WARN - search query to run:org.apache.cayenne.query.SelectQuery@39f0bb[root=class ish.oncourse.cayenne.Contact,name=<null>] expression:(isStudent = true) and ((student.enrolments.courseClass.endDateTime >= Thu Jun 12 00:00:00 EST 2008) or (student.enrolments.courseClass.endDateTime = null)) and (student.enrolments.courseClass.isCancelled != true) orderings:[<ordering descending="true"><![CDATA[lastName]]></ordering>, <ordering descending="true"><![CDATA[firstName]]></ordering>]
> --- generated sql ---
> SELECT DISTINCT t0.id, t0.lastName, t0.lastName DESC, t0.firstName FROM Contact t0 JOIN Student t1 ON (t0.id = t1.contactId) JOIN Enrolment t2 ON (t1.id = t2.studentId) JOIN CourseClass t3 ON (t2.courseClass_Id = t3.id) WHERE (t0.isStudent = ?) AND ((t3.endDateTime >= ?) OR (t3.endDateTime IS NULL)) AND (t3.isCancelled <> ?) AND ((t0.isDeleted IS NULL) OR (t0.isDeleted = ?)) ORDER BY t0.lastName DESC, t0.firstName DESC [bind: 1->isStudent:'true', 2->endDateTime:'2008-06-11 00:00:00.0', 3->isCancelled:'true', 4->isDeleted:0]
> --- stack trace ---
> [java] org.apache.cayenne.CayenneRuntimeException: [v.3.0-SNAPSHOT May 14 2008 22:35:23] Remote error. URL - <...>; CAUSE - [v.3.0-SNAPSHOT May 14 2008 22:35:23] Error getting ResultIterator: Query Exception:
> [java] java.sql.SQLException: Syntax error: Encountered "DESC" at line 1, column 49.
> [java] at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
> [java] at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:274)
> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:280)
> [java] at org.apache.cayenne.access.TransactionConnectionDecorator.prepareStatement(TransactionConnectionDecorator.java:179)
> [java] at org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:118)
> [java] at org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:72)
> [java] at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:58)
> [java] at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:230)
> [java] at org.apache.cayenne.access.DataDomainLegacyQueryAction.execute(DataDomainLegacyQueryAction.java:81)
> [java] at org.apache.cayenne.access.DataDomain$1.transform(DataDomain.java:717)
> [java] at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:837)
> [java] at org.apache.cayenne.access.DataDomain.performQueries(DataDomain.java:714)
> [java] at org.apache.cayenne.access.DataContext.internalPerformIteratedQuery(DataContext.java:1273)
> [java] at org.apache.cayenne.access.DataContext.performIteratedQuery(DataContext.java:1239)
> [java] at org.apache.cayenne.access.IncrementalFaultList.fillIn(IncrementalFaultList.java:218)
> [java] at org.apache.cayenne.access.IncrementalFaultList.<init>(IncrementalFaultList.java:163)
> [java] at org.apache.cayenne.access.SimpleIdIncrementalFaultList.<init>(SimpleIdIncrementalFaultList.java:43)
> [java] at org.apache.cayenne.access.DataContextQueryAction.interceptPaginatedQuery(DataContextQueryAction.java:93)
> [java] at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:95)
> [java] at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1336)
> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.runQuery(ClientServerChannelQueryAction.java:120)
> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.execute(ClientServerChannelQueryAction.java:66)
> [java] at org.apache.cayenne.access.ClientServerChannel.onQuery(ClientServerChannel.java:82)
> [java] at org.apache.cayenne.remote.service.DispatchHelper.dispatch(DispatchHelper.java:40)
> [java] at org.apache.cayenne.remote.service.BaseRemoteService.processMessage(BaseRemoteService.java:153)
> [java] at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
> [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> [java] at java.lang.reflect.Method.invoke(Method.java:585)
> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:180)
> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:109)
> [java] at com.caucho.hessian.server.HessianServlet.service(HessianServlet.java:393)
> [java] at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
> [java] at ish.oncourse.server.SecurityFilter.doFilter(SecurityFilter.java:112)
> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
> [java] at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
> [java] at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
> [java] at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
> [java] at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)
> [java] at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
> [java] at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
> [java] at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
> [java] at org.mortbay.jetty.Server.handle(Server.java:324)
> [java] at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
> [java] at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:842)
> [java] at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:730)
> [java] at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
> [java] at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
> [java] at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)
> [java] at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
> [java] Caused by: ERROR 42X01: Syntax error: Encountered "DESC" at line 1, column 49.
> [java] at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> [java] at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
> [java] at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
> [java] at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> [java] at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
> [java] ... 55 more
> [java]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (CAY-1074) Derby: Wrong SQL Generated for select
items -- DESC follows column name
Posted by "Ari Maniatis (JIRA)" <de...@cayenne.apache.org>.
[ https://issues.apache.org/cayenne/browse/CAY-1074?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12956#action_12956 ]
Ari Maniatis commented on CAY-1074:
-----------------------------------
That was Lachlan's point here. And I don't know that this is Derby specific...
Just another data point: when we changed the ordering so that it sorts on just one field, the problem goes away. The issue exists only when ordering on the two fields (first name, last name in this example).
Environment:
* ROP
* paged query
* M4 build (or pretty close to)
What *should* be happening I think is that neither of these fields should appear in the select since the query should just fetch IDs for the paging.
> Derby: Wrong SQL Generated for select items -- DESC follows column name
> -----------------------------------------------------------------------
>
> Key: CAY-1074
> URL: https://issues.apache.org/cayenne/browse/CAY-1074
> Project: Cayenne
> Issue Type: Bug
> Components: Cayenne Core Library
> Affects Versions: 3.0
> Environment: N/A
> Reporter: Lachlan Deck
> Assignee: Andrus Adamchik
> Priority: Critical
>
> Pay particular attention to the t0.lastName DESC.
> logger.warn("search query to run:" + query + " expression:" + qualifier + " orderings:" + orderings);
> WARN - search query to run:org.apache.cayenne.query.SelectQuery@39f0bb[root=class ish.oncourse.cayenne.Contact,name=<null>] expression:(isStudent = true) and ((student.enrolments.courseClass.endDateTime >= Thu Jun 12 00:00:00 EST 2008) or (student.enrolments.courseClass.endDateTime = null)) and (student.enrolments.courseClass.isCancelled != true) orderings:[<ordering descending="true"><![CDATA[lastName]]></ordering>, <ordering descending="true"><![CDATA[firstName]]></ordering>]
> --- generated sql ---
> SELECT DISTINCT t0.id, t0.lastName, t0.lastName DESC, t0.firstName FROM Contact t0 JOIN Student t1 ON (t0.id = t1.contactId) JOIN Enrolment t2 ON (t1.id = t2.studentId) JOIN CourseClass t3 ON (t2.courseClass_Id = t3.id) WHERE (t0.isStudent = ?) AND ((t3.endDateTime >= ?) OR (t3.endDateTime IS NULL)) AND (t3.isCancelled <> ?) AND ((t0.isDeleted IS NULL) OR (t0.isDeleted = ?)) ORDER BY t0.lastName DESC, t0.firstName DESC [bind: 1->isStudent:'true', 2->endDateTime:'2008-06-11 00:00:00.0', 3->isCancelled:'true', 4->isDeleted:0]
> --- stack trace ---
> [java] org.apache.cayenne.CayenneRuntimeException: [v.3.0-SNAPSHOT May 14 2008 22:35:23] Remote error. URL - <...>; CAUSE - [v.3.0-SNAPSHOT May 14 2008 22:35:23] Error getting ResultIterator: Query Exception:
> [java] java.sql.SQLException: Syntax error: Encountered "DESC" at line 1, column 49.
> [java] at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
> [java] at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:274)
> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:280)
> [java] at org.apache.cayenne.access.TransactionConnectionDecorator.prepareStatement(TransactionConnectionDecorator.java:179)
> [java] at org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:118)
> [java] at org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:72)
> [java] at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:58)
> [java] at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:230)
> [java] at org.apache.cayenne.access.DataDomainLegacyQueryAction.execute(DataDomainLegacyQueryAction.java:81)
> [java] at org.apache.cayenne.access.DataDomain$1.transform(DataDomain.java:717)
> [java] at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:837)
> [java] at org.apache.cayenne.access.DataDomain.performQueries(DataDomain.java:714)
> [java] at org.apache.cayenne.access.DataContext.internalPerformIteratedQuery(DataContext.java:1273)
> [java] at org.apache.cayenne.access.DataContext.performIteratedQuery(DataContext.java:1239)
> [java] at org.apache.cayenne.access.IncrementalFaultList.fillIn(IncrementalFaultList.java:218)
> [java] at org.apache.cayenne.access.IncrementalFaultList.<init>(IncrementalFaultList.java:163)
> [java] at org.apache.cayenne.access.SimpleIdIncrementalFaultList.<init>(SimpleIdIncrementalFaultList.java:43)
> [java] at org.apache.cayenne.access.DataContextQueryAction.interceptPaginatedQuery(DataContextQueryAction.java:93)
> [java] at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:95)
> [java] at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1336)
> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.runQuery(ClientServerChannelQueryAction.java:120)
> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.execute(ClientServerChannelQueryAction.java:66)
> [java] at org.apache.cayenne.access.ClientServerChannel.onQuery(ClientServerChannel.java:82)
> [java] at org.apache.cayenne.remote.service.DispatchHelper.dispatch(DispatchHelper.java:40)
> [java] at org.apache.cayenne.remote.service.BaseRemoteService.processMessage(BaseRemoteService.java:153)
> [java] at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
> [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> [java] at java.lang.reflect.Method.invoke(Method.java:585)
> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:180)
> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:109)
> [java] at com.caucho.hessian.server.HessianServlet.service(HessianServlet.java:393)
> [java] at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
> [java] at ish.oncourse.server.SecurityFilter.doFilter(SecurityFilter.java:112)
> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
> [java] at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
> [java] at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
> [java] at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
> [java] at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)
> [java] at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
> [java] at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
> [java] at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
> [java] at org.mortbay.jetty.Server.handle(Server.java:324)
> [java] at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
> [java] at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:842)
> [java] at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:730)
> [java] at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
> [java] at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
> [java] at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)
> [java] at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
> [java] Caused by: ERROR 42X01: Syntax error: Encountered "DESC" at line 1, column 49.
> [java] at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> [java] at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
> [java] at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
> [java] at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> [java] at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
> [java] ... 55 more
> [java]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (CAY-1074) Derby: Wrong SQL Generated for select
items -- DESC follows column name
Posted by "Andrus Adamchik (JIRA)" <de...@cayenne.apache.org>.
[ https://issues.apache.org/cayenne/browse/CAY-1074?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12957#action_12957 ]
Andrus Adamchik commented on CAY-1074:
--------------------------------------
That's evil. Unless somebody is already looking at this, I'll put it on my short TODO.
> Derby: Wrong SQL Generated for select items -- DESC follows column name
> -----------------------------------------------------------------------
>
> Key: CAY-1074
> URL: https://issues.apache.org/cayenne/browse/CAY-1074
> Project: Cayenne
> Issue Type: Bug
> Components: Cayenne Core Library
> Affects Versions: 3.0
> Environment: N/A
> Reporter: Lachlan Deck
> Assignee: Andrus Adamchik
> Priority: Critical
>
> Pay particular attention to the t0.lastName DESC.
> logger.warn("search query to run:" + query + " expression:" + qualifier + " orderings:" + orderings);
> WARN - search query to run:org.apache.cayenne.query.SelectQuery@39f0bb[root=class ish.oncourse.cayenne.Contact,name=<null>] expression:(isStudent = true) and ((student.enrolments.courseClass.endDateTime >= Thu Jun 12 00:00:00 EST 2008) or (student.enrolments.courseClass.endDateTime = null)) and (student.enrolments.courseClass.isCancelled != true) orderings:[<ordering descending="true"><![CDATA[lastName]]></ordering>, <ordering descending="true"><![CDATA[firstName]]></ordering>]
> --- generated sql ---
> SELECT DISTINCT t0.id, t0.lastName, t0.lastName DESC, t0.firstName FROM Contact t0 JOIN Student t1 ON (t0.id = t1.contactId) JOIN Enrolment t2 ON (t1.id = t2.studentId) JOIN CourseClass t3 ON (t2.courseClass_Id = t3.id) WHERE (t0.isStudent = ?) AND ((t3.endDateTime >= ?) OR (t3.endDateTime IS NULL)) AND (t3.isCancelled <> ?) AND ((t0.isDeleted IS NULL) OR (t0.isDeleted = ?)) ORDER BY t0.lastName DESC, t0.firstName DESC [bind: 1->isStudent:'true', 2->endDateTime:'2008-06-11 00:00:00.0', 3->isCancelled:'true', 4->isDeleted:0]
> --- stack trace ---
> [java] org.apache.cayenne.CayenneRuntimeException: [v.3.0-SNAPSHOT May 14 2008 22:35:23] Remote error. URL - <...>; CAUSE - [v.3.0-SNAPSHOT May 14 2008 22:35:23] Error getting ResultIterator: Query Exception:
> [java] java.sql.SQLException: Syntax error: Encountered "DESC" at line 1, column 49.
> [java] at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
> [java] at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:274)
> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:280)
> [java] at org.apache.cayenne.access.TransactionConnectionDecorator.prepareStatement(TransactionConnectionDecorator.java:179)
> [java] at org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:118)
> [java] at org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:72)
> [java] at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:58)
> [java] at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:230)
> [java] at org.apache.cayenne.access.DataDomainLegacyQueryAction.execute(DataDomainLegacyQueryAction.java:81)
> [java] at org.apache.cayenne.access.DataDomain$1.transform(DataDomain.java:717)
> [java] at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:837)
> [java] at org.apache.cayenne.access.DataDomain.performQueries(DataDomain.java:714)
> [java] at org.apache.cayenne.access.DataContext.internalPerformIteratedQuery(DataContext.java:1273)
> [java] at org.apache.cayenne.access.DataContext.performIteratedQuery(DataContext.java:1239)
> [java] at org.apache.cayenne.access.IncrementalFaultList.fillIn(IncrementalFaultList.java:218)
> [java] at org.apache.cayenne.access.IncrementalFaultList.<init>(IncrementalFaultList.java:163)
> [java] at org.apache.cayenne.access.SimpleIdIncrementalFaultList.<init>(SimpleIdIncrementalFaultList.java:43)
> [java] at org.apache.cayenne.access.DataContextQueryAction.interceptPaginatedQuery(DataContextQueryAction.java:93)
> [java] at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:95)
> [java] at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1336)
> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.runQuery(ClientServerChannelQueryAction.java:120)
> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.execute(ClientServerChannelQueryAction.java:66)
> [java] at org.apache.cayenne.access.ClientServerChannel.onQuery(ClientServerChannel.java:82)
> [java] at org.apache.cayenne.remote.service.DispatchHelper.dispatch(DispatchHelper.java:40)
> [java] at org.apache.cayenne.remote.service.BaseRemoteService.processMessage(BaseRemoteService.java:153)
> [java] at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
> [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> [java] at java.lang.reflect.Method.invoke(Method.java:585)
> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:180)
> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:109)
> [java] at com.caucho.hessian.server.HessianServlet.service(HessianServlet.java:393)
> [java] at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
> [java] at ish.oncourse.server.SecurityFilter.doFilter(SecurityFilter.java:112)
> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
> [java] at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
> [java] at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
> [java] at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
> [java] at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)
> [java] at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
> [java] at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
> [java] at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
> [java] at org.mortbay.jetty.Server.handle(Server.java:324)
> [java] at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
> [java] at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:842)
> [java] at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:730)
> [java] at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
> [java] at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
> [java] at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)
> [java] at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
> [java] Caused by: ERROR 42X01: Syntax error: Encountered "DESC" at line 1, column 49.
> [java] at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> [java] at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
> [java] at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
> [java] at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> [java] at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
> [java] ... 55 more
> [java]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (CAY-1074) Derby: Wrong SQL Generated for select
items -- DESC follows column name
Posted by "Andrus Adamchik (JIRA)" <de...@cayenne.apache.org>.
[ https://issues.apache.org/cayenne/browse/CAY-1074?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Andrus Adamchik closed CAY-1074.
--------------------------------
Resolution: Fixed
Fix Version/s: 3.0
> Derby: Wrong SQL Generated for select items -- DESC follows column name
> -----------------------------------------------------------------------
>
> Key: CAY-1074
> URL: https://issues.apache.org/cayenne/browse/CAY-1074
> Project: Cayenne
> Issue Type: Bug
> Components: Cayenne Core Library
> Affects Versions: 3.0
> Environment: N/A
> Reporter: Lachlan Deck
> Assignee: Andrus Adamchik
> Priority: Critical
> Fix For: 3.0
>
>
> Pay particular attention to the t0.lastName DESC.
> logger.warn("search query to run:" + query + " expression:" + qualifier + " orderings:" + orderings);
> WARN - search query to run:org.apache.cayenne.query.SelectQuery@39f0bb[root=class ish.oncourse.cayenne.Contact,name=<null>] expression:(isStudent = true) and ((student.enrolments.courseClass.endDateTime >= Thu Jun 12 00:00:00 EST 2008) or (student.enrolments.courseClass.endDateTime = null)) and (student.enrolments.courseClass.isCancelled != true) orderings:[<ordering descending="true"><![CDATA[lastName]]></ordering>, <ordering descending="true"><![CDATA[firstName]]></ordering>]
> --- generated sql ---
> SELECT DISTINCT t0.id, t0.lastName, t0.lastName DESC, t0.firstName FROM Contact t0 JOIN Student t1 ON (t0.id = t1.contactId) JOIN Enrolment t2 ON (t1.id = t2.studentId) JOIN CourseClass t3 ON (t2.courseClass_Id = t3.id) WHERE (t0.isStudent = ?) AND ((t3.endDateTime >= ?) OR (t3.endDateTime IS NULL)) AND (t3.isCancelled <> ?) AND ((t0.isDeleted IS NULL) OR (t0.isDeleted = ?)) ORDER BY t0.lastName DESC, t0.firstName DESC [bind: 1->isStudent:'true', 2->endDateTime:'2008-06-11 00:00:00.0', 3->isCancelled:'true', 4->isDeleted:0]
> --- stack trace ---
> [java] org.apache.cayenne.CayenneRuntimeException: [v.3.0-SNAPSHOT May 14 2008 22:35:23] Remote error. URL - <...>; CAUSE - [v.3.0-SNAPSHOT May 14 2008 22:35:23] Error getting ResultIterator: Query Exception:
> [java] java.sql.SQLException: Syntax error: Encountered "DESC" at line 1, column 49.
> [java] at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
> [java] at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> [java] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:274)
> [java] at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:280)
> [java] at org.apache.cayenne.access.TransactionConnectionDecorator.prepareStatement(TransactionConnectionDecorator.java:179)
> [java] at org.apache.cayenne.access.trans.QueryAssembler.createStatement(QueryAssembler.java:118)
> [java] at org.apache.cayenne.access.jdbc.SelectAction.performAction(SelectAction.java:72)
> [java] at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:58)
> [java] at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:230)
> [java] at org.apache.cayenne.access.DataDomainLegacyQueryAction.execute(DataDomainLegacyQueryAction.java:81)
> [java] at org.apache.cayenne.access.DataDomain$1.transform(DataDomain.java:717)
> [java] at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:837)
> [java] at org.apache.cayenne.access.DataDomain.performQueries(DataDomain.java:714)
> [java] at org.apache.cayenne.access.DataContext.internalPerformIteratedQuery(DataContext.java:1273)
> [java] at org.apache.cayenne.access.DataContext.performIteratedQuery(DataContext.java:1239)
> [java] at org.apache.cayenne.access.IncrementalFaultList.fillIn(IncrementalFaultList.java:218)
> [java] at org.apache.cayenne.access.IncrementalFaultList.<init>(IncrementalFaultList.java:163)
> [java] at org.apache.cayenne.access.SimpleIdIncrementalFaultList.<init>(SimpleIdIncrementalFaultList.java:43)
> [java] at org.apache.cayenne.access.DataContextQueryAction.interceptPaginatedQuery(DataContextQueryAction.java:93)
> [java] at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:95)
> [java] at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1336)
> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.runQuery(ClientServerChannelQueryAction.java:120)
> [java] at org.apache.cayenne.access.ClientServerChannelQueryAction.execute(ClientServerChannelQueryAction.java:66)
> [java] at org.apache.cayenne.access.ClientServerChannel.onQuery(ClientServerChannel.java:82)
> [java] at org.apache.cayenne.remote.service.DispatchHelper.dispatch(DispatchHelper.java:40)
> [java] at org.apache.cayenne.remote.service.BaseRemoteService.processMessage(BaseRemoteService.java:153)
> [java] at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
> [java] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> [java] at java.lang.reflect.Method.invoke(Method.java:585)
> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:180)
> [java] at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:109)
> [java] at com.caucho.hessian.server.HessianServlet.service(HessianServlet.java:393)
> [java] at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
> [java] at ish.oncourse.server.SecurityFilter.doFilter(SecurityFilter.java:112)
> [java] at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
> [java] at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
> [java] at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
> [java] at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
> [java] at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)
> [java] at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
> [java] at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
> [java] at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
> [java] at org.mortbay.jetty.Server.handle(Server.java:324)
> [java] at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
> [java] at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:842)
> [java] at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:730)
> [java] at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
> [java] at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
> [java] at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)
> [java] at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
> [java] Caused by: ERROR 42X01: Syntax error: Encountered "DESC" at line 1, column 49.
> [java] at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> [java] at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
> [java] at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
> [java] at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> [java] at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
> [java] ... 55 more
> [java]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.