[
https://issues.apache.org/jira/browse/DERBY-4406?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12857424#action_12857424
]
Lily Wei commented on DERBY-4406:
---------------------------------
If the goal is for Derby to recognize OrderByColumn is referencing an
expression which is equivalent to ResultColumn that is already present in the
SELECT and yet follow SQL standard as much as we can. I agree (+1) we must
make sure that if we change OrderByColumn behavior for use case (1) and/or (2)
and we don't change the behavior for use case (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
