db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "C.S. Nirmal J. Fernando (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4406) Wrong order when using ORDER BY on non-deterministic function
Date Tue, 23 Mar 2010 18:02:27 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4406?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12848825#action_12848825
] 

C.S. Nirmal J. Fernando commented on DERBY-4406:
------------------------------------------------

Hi,

I feel the results given by Derby is true. Following is my explanation:

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 

                -here the query first select 5 random numbers and named them as 'r'. Since
random() is a particular function you can't use it to refer a specific data set (in this case
r), therefore "order by random()" should reorganize those 5 random numbers, randomly (not
in ascending/ descending order).

So the same concept can be applied to following query too.

                    select random() as r1, random() as r2 from t order by random() 

Say:

r1                                                                                  &
              r2
------                                                                                   
           --------
0.1285512465366495                                                              0.3423578873009
0.5116860880915798                                                              0.5689209748765
0.21060042130229073                                                            0.4257678987510
0.2506706923680875                                                              0.1278737846784
0.6378857329935494                                                              0.8636983857498

Result would be:

r1                                             r2
------                                       --------
0.6378857329935494 |    0.8636983857498
0.5116860880915798 |    0.5689209748765
0.1285512465366495 |    0.3423578873009
0.2106004213022907 |    0.4257678987510
0.2506706923680875 |    0.1278737846784






> 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