db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-4406) Wrong order when using ORDER BY on non-deterministic function
Date Mon, 12 Oct 2009 15:31:31 GMT
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.


Mime
View raw message