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. > >
