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)
<[email protected]> 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:[EMAIL PROTECTED] 
>> 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.
>
>

Reply via email to