[
https://issues.apache.org/jira/browse/DERBY-4406?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12848825#action_12848825
]
C.S. Nirmal J. Fernando edited comment on DERBY-4406 at 3/23/10 6:06 PM:
-------------------------------------------------------------------------
Hi,
I feel the results given by Derby is true. Following is my explanation:
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
-here the query first select 5 random numbers and named them as
'r'. Since random() is a particular function you can't use it to refer a
specific data set (in this case r), therefore "order by random()" should
reorganize those 5 random numbers, randomly (not in ascending/ descending
order).
So the same concept can be applied to following query too.
select random() as r1, random() as r2 from t order by
random()
Say:
r1
r2
------------------------------
-------------------------
0.1285512465366495
0.3423578873009
0.5116860880915798
0.5689209748765
0.21060042130229073
0.4257678987510
0.2506706923680875
0.1278737846784
0.6378857329935494
0.8636983857498
Result would be:
r1
r2
-------------------------------| -------------------------------
0.6378857329935494 | 0.8636983857498
0.5116860880915798 | 0.5689209748765
0.1285512465366495 | 0.3423578873009
0.2106004213022907 | 0.4257678987510
0.2506706923680875 | 0.1278737846784
was (Author: nirmal):
Hi,
I feel the results given by Derby is true. Following is my explanation:
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
-here the query first select 5 random numbers and named them as
'r'. Since random() is a particular function you can't use it to refer a
specific data set (in this case r), therefore "order by random()" should
reorganize those 5 random numbers, randomly (not in ascending/ descending
order).
So the same concept can be applied to following query too.
select random() as r1, random() as r2 from t order by
random()
Say:
r1
r2
------------------------------
-------------------------
0.1285512465366495
0.3423578873009
0.5116860880915798
0.5689209748765
0.21060042130229073
0.4257678987510
0.2506706923680875
0.1278737846784
0.6378857329935494
0.8636983857498
Result would be:
r1 | r2
-------------------------------| -------------------------------
0.6378857329935494 | 0.8636983857498
0.5116860880915798 | 0.5689209748765
0.1285512465366495 | 0.3423578873009
0.2106004213022907 | 0.4257678987510
0.2506706923680875 | 0.1278737846784
> 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.
