db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4406) Wrong order when using ORDER BY on non-deterministic function
Date Sat, 27 Mar 2010 22:29:27 GMT

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

Bryan Pendleton commented on DERBY-4406:

Hi Nirmal, I think it would be great if you can look at this problem. You should mark the
entry as 'assigned' to you while you are studying it to let others know (you can unassign
it later if you so choose).

I think that you'll find that it is helpful to study the code in OrderByColumn.java and OrderByList.java.

In particular, you'll want to understand the concept of "pulled up" columns, which
is a construct that Derby uses to handle cases like

  select a,b from t order by c, d

Since columns c and d don't appear in the select list, but need to be retrieved from
the database in order to use their values for sorting, they are 'pulled up' into the result
set, but marked specially to show that they are present only for sorting purposes,
but not for use in the actual results.

In the query in this issue

  select random() from t order by random()

I think that the problem is related to the fact that we don't recognize that the two
random() expressions are equivalent, and so we treat this as a case where we are
'pulling up' a second column into the result set, instead of just setting the order by
column to refer to the column already present in the result set.

> 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:,
>            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.

View raw message