db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Roman Puchkovskiy <roman.puchkovs...@blandware.com>
Subject 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 14:35:47 GMT


I run Derby A query that seems to be correct (and worked in
previous versios) now produces an error. Steps to reproduce:

1. Run ij
2. Create a new DB using "connect
'jdbc:derby:C:\TMP\derby\test_db;create=true';" command
3. Run attached  http://www.nabble.com/file/p14982335/result.ddl result.ddl 
to create DB tables.
4. Run following query:
select distinct menuitem0_."id" as id1_37_, menuitem0_."version" as
version2_37_, menuitem0_."parent_id" as parent3_37_, menuitem0_."identifier"
as identifier4_37_, menuitem0_."align" as align5_37_, menuitem0_."style" as
style6_37_, menuitem0_."style_class" as style7_37_, menuitem0_."style_id" as
style8_37_, menuitem0_."image" as image9_37_, menuitem0_."alt_image" as
alt10_37_, menuitem0_."action" as action11_37_, menuitem0_."forward" as
forward12_37_, menuitem0_."location" as location13_37_,
menuitem0_."external_location" as external14_37_, menuitem0_."anchor" as
anchor15_37_, menuitem0_."target" as target16_37_, menuitem0_."visibility"
as visibility17_37_, menuitem0_."pos" as pos18_37_, menuitem0_."js_onclick"
as js19_37_, menuitem0_."js_onmouseover" as js20_37_,
menuitem0_."js_onmouseout" as js21_37_, menuitem0_."hreflang" as
hreflang22_37_, menuitem0_."orig_id" as orig23_37_, menuitem0_."owner_id" as
owner24_37_ from "al_core_menu_item" menuitem0_ left outer join
"al_core_menu_item_role" roles1_ on menuitem0_."id"=roles1_."menu_item_id"
left outer join "al_core_role" role2_ on
roles1_."rolename"=role2_."rolename" where (menuitem0_."parent_id"=?
)and(menuitem0_."owner_id" is null ) order by  menuitem0_."pos" asc
,menuitem0_."identifier" asc

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

But "pos" column is in 'select' clause!

This query did not produce error in and

Here is sysinfo output:  http://www.nabble.com/file/p14982335/sysinfo.txt

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-tp14982335p14982335.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

View raw message