You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Jodeleit, Peter" <jo...@e-spirit.de> on 2010/03/31 13:46:25 UTC
"SELECT .. ORDER BY .. DESC" delivers wrong ordering
Hello,
I've got a problem with ORDER BY .. DESC. I've searched the JIRA and found no matching issue. Is this really a unknown bug?
We use derby v10.5.3.0. I can deliver the complete database as testcase if needed.
Here ist the statement:
SELECT DISTINCT t14.FS_ID, t14.NAME_DE
FROM p8001571_8001467.PRODUCTS t14 LEFT JOIN p8001571_8001467.RT_PRODUCT_CATEGORIES_PRODUCTS_CATEGORIES_LIST t9
ON t14.FS_ID=t9.PROD_FS_ID0 AND t9.FS_VALID_TO>1268993576528 AND t9.FS_VALID_FROM<=1268993576528
LEFT JOIN p8001571_8001467.PRODUCT_CATEGORIES t8 ON t9.PROD_FS_ID=t8.FS_ID AND t8.FS_VALID_TO>1268993576528 AND t8.FS_VALID_FROM<=1268993576528
WHERE t8.FS_ID=1091 AND t14.FS_VALID_TO>1268993576528 AND t14.FS_VALID_FROM<=1268993576528
ORDER BY t14.NAME_DE ASC
This select delivers:
1408, "DS 1000 block"
1152, "DS 1000 modular"
1409, "DS 1200 block"
1344, "DS 1200 modular"
1472, "DS 1400 block"
1345, "DS 1400 modular"
If I change the order by to "DESC" I get the following result which is obviously not correct ordered:
1152, "DS 1000 modular"
1344, "DS 1200 modular"
1345, "DS 1400 modular"
1408, "DS 1000 block"
1409, "DS 1200 block"
1472, "DS 1400 block"
If I keep "DESC" and change the columns in the request to "t14.NAME_DE, t14.FS_ID" the result is correct again:
"DS 1000 block", 1408
"DS 1000 modular", 1152
"DS 1200 block", 1409
"DS 1200 modular", 1344
"DS 1400 block", 1472
"DS 1400 modular", 1345
The result is also correct if only the column "NAME_DE" is selected.
I cross-checked the behaviour with PostgreSQL, it delivered the results as I expected.
Peter
--
Peter Jodeleit
Lead Developer
www.e-Spirit.com
----------------------------------------------------------
Folgen Sie uns auf Twitter: www.twitter.com/espirit_news
----------------------------------------------------------
Bleiben Sie auf dem Laufenden: www.e-Spirit.com/newsletter
----------------------------------------------------------
Sitz des Unternehmens: Barcelonaweg 14 | 44269 Dortmund
Vorstand: Jörn Bodemann (Vors.) | Christoph Junge
Vorsitzender des Aufsichtsrats: Michael Kenfenheuer
Amtsgericht Dortmund (HRB 20399)
Re: "SELECT .. ORDER BY .. DESC" delivers wrong ordering
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
On 03/31/10 01:46 PM, Jodeleit, Peter wrote:
> Hello,
>
> I've got a problem with ORDER BY .. DESC. I've searched the JIRA and found no matching issue. Is this really a unknown bug?
> We use derby v10.5.3.0. I can deliver the complete database as testcase if needed.
>
Hi Peter,
It looks like you've found a new bug, and it would be great if you could
file a JIRA issue and attach the database (or scripts to generate the
database) to it.
Thanks,
--
Knut Anders