[jira] Commented: (DERBY-4406) Wrong order when using ORDER BY on non-deterministic function

["Rick Hillegas (JIRA)" <ji...@apache.org>]

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

Rick Hillegas commented on DERBY-4406:
--------------------------------------

Hi Knut,

I asked the SQL committee for advice on what is the correct ordering when an expression in the sort key matches two expressions in the SELECT list. E.g.:

 select random() * a, random() * a
 from t
 order by random() * a

I received two responses. One simply confirmed that the spec didn't seem to cover that case. The other response was more detailed:

"You're right, the rule does not specify which column from the SELECT list to use.
Since this feature has been around since SQL:1999 (14.1 <declare cursor> SR 18)f)i)2)A))
I would not support making the standard more definite.  So we could say that
it is implementation-dependent or implementation-defined, which column is chosen.
I personally favor implementation-dependent,  because this is such a screwball example
anyway.  You are welcome to write a comment and a paper to solve it.
If we leave the standard wording untouched, I would say that it supports
implementation-dependent because the use of "some" implies that when there is more
than one, the implementation can decide which one based on its own whim."

The terms "implementation-dependent" and "implementation-defined" are defined in part 1 of the Standard, section 3.1:

3.1.1.8 implementation-defined: Possibly differing between SQL-implementations, but specified by the 
implementor for each particular SQL-implementation. 
3.1.1.9 implementation-dependent: Possibly differing between SQL-implementations, but not specified 
by ISO/IEC 9075, and not required to be specified by the implementor for any particular SQL- 
implementation. 

I think this means that we are free to do either of the following:

1) Pick a matching column to sort by, using some well defined rule. E.g., sort by the first matching column.

2) Pick a matching column at random and sort by it.

I vote for (1) if it is easy to figure out the first matching column. If it is not easy to figure out the first matching column, then for this edge case I would be content with option (2).

Thanks.



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