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] Commented: (DERBY-4406) Wrong order when using ORDER BY on non-deterministic function
Date Thu, 15 Apr 2010 12:22:50 GMT

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

Knut Anders Hatlen commented on DERBY-4406:
-------------------------------------------

Before we spend too much time implementing a solution for this issue,
I think we need to take a step back and clarify what the solution
should look like.

Here are some questions I think we should try to answer before we go
further:

1) SELECT RANDOM() AS R FROM T ORDER BY RANDOM()

For this statement (taken from the bug description), we don't get any
guidance from the SQL standard, because the SQL standard doesn't allow
sort keys that don't have a column reference. This means that changing
how the result from the query is ordered, does not make it any more
standards compliant. So does it make any sense to change it?

2) SELECT A*RANDOM() AS R FROM T ORDER BY A*RANDOM()

How should this query be ordered? Based on my interpretation in an
earlier comment (28/Mar/10), I believe that the results should be
sorted on R. Derby currently does not sort the results on R. So, do
others agree with my previous interpretation that the correct way to
handle this query, is to order the results by R? And if so, can/should
we change it?

3) SELECT A, A*RANDOM() AS R FROM T ORDER BY A*RANDOM()

My interpretation (comment dated 28/Mar/10) is that this query should
not be ordered by R, and Derby currently behaves in accordance with
that interpretation. Do others agree with this interpretation? If so,
we must make sure that if we change the behaviour of (1) and/or (2),
we don't also change the behaviour of (3).

> 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
>            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

        

Mime
View raw message