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

Hi.

I run Derby 10.3.2.1. 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
result."

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

This query did not produce error in 10.1.2.1 and 10.3.1.4.

Here is sysinfo output:  http://www.nabble.com/file/p14982335/sysinfo.txt
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.


Mime
View raw message