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