db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jodeleit, Peter" <jodel...@e-spirit.de>
Subject "SELECT .. ORDER BY .. DESC" delivers wrong ordering
Date Wed, 31 Mar 2010 11:46:25 GMT
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)

Mime
View raw message