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)
