[DERBY-4406] Wrong order when using ORDER BY on non-deterministic function

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 10.5.3.0, 10.6.1.0
• Component/s: SQL
• Issue & fix info: Repro attached
• Bug behavior facts: Deviation from standard
• Assignee: Nirmal Fernando
• Reporter: Knut Anders Hatlen
• Created: 12/Oct/09 15:29
• Updated: 09/Aug/10 11:25
• Resolved: 09/Aug/10 11:25

Description

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

Issue Links

• is related to: DERBY-4371 Non-selected columns for SELECT DISTINCT allowed in ORDER BY clause if ordered by expression