db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4371) Non-selected columns for SELECT DISTINCT allowed in ORDER BY clause if ordered by expression
Date Fri, 09 Oct 2009 16:17:31 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4371?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12764074#action_12764074
] 

Knut Anders Hatlen commented on DERBY-4371:
-------------------------------------------

SQL:2003 talks about this in part 2, 14.1 <declare cursor>, syntax rule 18d (18 d 9
B II, to be specific). My understanding is that if you have SELECT DISTINCT, all the columns/expressions
in the ORDER BY clause must also be found in the select list.

Adding this restriction would solve the issue by rejecting the problematic query. Whereas
the behaviour of this particular query is not well-defined, there may be some meaningful queries
that will be rejected too, like "select distinct i from t order by -i", so we should probably
add a release note if we choose to fix it this way.

Another data point, PostgreSQL does not allow any of the queries discussed here:

kh160127=# select distinct i from t order by j;
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
kh160127=# select distinct i from t order by j*2;
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
kh160127=# select distinct i from t order by -i;
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

MySQL allows all three of them, although only the latter has a well-defined ordering.

> Non-selected columns for SELECT DISTINCT allowed in ORDER BY clause if ordered by expression
> --------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4371
>                 URL: https://issues.apache.org/jira/browse/DERBY-4371
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.1.1
>            Reporter: Bernt M. Johnsen
>            Priority: Critical
>
> How to repeat:
> ij> create table t (i integer, j integer);;
> 0 rows inserted/updated/deleted
> ij> insert into t values (1,2),(1,3);
> 2 rows inserted/updated/deleted
> ij> select distinct i from t order by j;
> ERROR 42879: The ORDER BY clause may not contain column 'J', since the query specifies
DISTINCT and that column does not appear in the query result.
> ij> select distinct i from t order by j*2;
> I          
> -----------
> 1          
> 1          
> 2 rows selected

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message