Wrong order when using ORDER BY on non-deterministic function
-------------------------------------------------------------
Key: DERBY-4406
URL: https://issues.apache.org/jira/browse/DERBY-4406
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.5.3.0, 10.6.0.0
Reporter: Knut Anders Hatlen
If I read the SQL standard correctly, a statement such as "select random() as r
from t order by random()" should be treated as "select random() as r from t
order by r". Derby does however generate a second, hidden random() column by
which the rows are ordered.
ij> create table t(x int);
0 rows inserted/updated/deleted
ij> insert into t values 1,2,3,4,5;
5 rows inserted/updated/deleted
ij> -- wrong result, not ordered by r
ij> select random() as r from t order by random();
R
----------------------
0.1285512465366495
0.5116860880915798
0.21060042130229073
0.2506706923680875
0.6378857329935494
5 rows selected
ij> -- correct result, ordered by r
ij> select random() as r from t order by r;
R
----------------------
0.0749025910679918
0.07694931688380491
0.1724114605785414
0.2268758969382877
0.31900450349277965
5 rows selected
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
