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