You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by "Ari Maniatis (JIRA)" <de...@cayenne.apache.org> on 2008/06/12 16:18:52 UTC
[jira] Commented: (CAY-1074) Derby: Wrong SQL Generated for select
items -- DESC follows column name
[ 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.
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.
>
>