db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: Derby complains on SQL that has ORDER BY that order column is not in query result, but it's there
Date Sun, 20 Jan 2008 23:02:56 GMT
Well, 
You could say that it's a product defect in that the column name is pos,
although you're aliasing it to a different name. It appears that the other
databases allow you to reference it either by the name or the alias.

Or you could say it's a hibernate defect because it's the one aliasing the
column and then ordering by the initial column name.


> -----Original Message-----
> From: Roman Puchkovskiy-2 [mailto:rpuch@mail.ru]
> Sent: Sunday, January 20, 2008 1:27 PM
> To: derby-user@db.apache.org
> Subject: RE: Derby complains on SQL that has ORDER BY that order column is
> not in query result, but it's there
> 
> 
> Thanks for replies.
> 
> Unfortunately, I cannot fix query directly, it's generated by Hibernate.
> Both Hibernate 3.1.3 and 3.2.5.ga (current version) produce query that
> causes error. With Hibernate 3.1.3 same HQL was working with older Derby
> versions, MySQL, Postgress, Oracle, Hypersonic...
> 
> 
> Sai Pullabhotla wrote:
> >
> >
> > I know some databases do require that the ORDER BY clause use the column
> > ALIASEs if any. I'm not sure if it is an SQL standard. So, if you change
> > the
> > ORDER BY to ORDER BY pos_18_37_, it should work.
> >
> > Sai Pullabhotla
> > President
> > jMethods, Inc.
> > Phone: +1 (402) 408-5753
> > Fax: +1 (402) 408-6861
> > www.jMethods.com
> > -----Original Message-----
> > From: Roman Puchkovskiy [mailto:roman.puchkovskiy@blandware.com]
> > Sent: Sunday, January 20, 2008 10:50 AM
> > To: derby-user@db.apache.org
> > Subject: Re: Derby complains on SQL that has ORDER BY that order column
> is
> > not in query result, but it's there
> >
> >
> > pos18_37_ is just an alias for menuitem0_."pos", so why query is
> rejected?
> >
> >
> > Donald McLean-3 wrote:
> >>
> >> But didn't you select it "as pos18_37_"?
> >>
> >> On Jan 20, 2008 9:35 AM, Roman Puchkovskiy
> >> <roman.puchkovskiy@blandware.com> wrote:
> >>>
> >>> as visibility17_37_, menuitem0_."pos" as pos18_37_,
> >>> menuitem0_."js_onclick"
> >>>
> >>> Error is produced: "The ORDER BY clause may not contain column 'pos',
> >>> since
> >>> the query specifies DISTINCT and that column does not appear in the
> >>> query
> >>> result."
> >>>
> >>> But "pos" column is in 'select' clause!
> >>
> >>
> >
> > --
> > View this message in context:
> > http://www.nabble.com/Derby-complains-on-SQL-that-has-ORDER-BY-that-
> order-co
> > lumn-is-not-in-query-result%2C-but-it%27s-there-tp14982335p14983729.html
> > Sent from the Apache Derby Users mailing list archive at Nabble.com.
> >
> >
> >
> 
> --
> View this message in context: http://www.nabble.com/Derby-complains-on-
> SQL-that-has-ORDER-BY-that-order-column-is-not-in-query-result%2C-but-
> it%27s-there-tp14982335p14985622.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.




Mime
View raw message