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, 17 Apr 2010 15:51:27 GMT

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

Bryan Pendleton commented on DERBY-4406:

The more I think about it, the more I'm tempted to just leave the current Derby
behavior alone in this area. The idea of ordering by RANDOM is rather hypothetical,
and I'm having trouble coming up with a real-life situation in which I would use this
function in this way. In practice, I think that if I was trying to do random data
processing (e.g., for statistical simulations, or for generating test data), I would
be content to generate the random data and store it into the database, then
subsequently sort by the actual columns which held the randomly-generated data, thus
avoiding this problem entirely.

It seems like the main point of DERBY-4406 is that our current implementation
has problems when using expressions in the ORDER BY clause which are either:
 - non-deterministic, or
 - have side effects
In either of these situations the user might particularly care about invoking the
function once-per-row in the query, not more-than-once.

Are there functions other than RANDOM() which exhibit this behavior?

Since the consensus appears to be that the original query was not standard SQL,
the fact that we implement it differently than other databases is OK.

So I'm leaning toward the conclusion that we should resolve this as "won't fix".

> 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
>            Assignee: C.S. Nirmal J. Fernando
> 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.
If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message