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