db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "C.S. Nirmal J. Fernando (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 Tue, 20 Apr 2010 16:26:51 GMT

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

C.S. Nirmal J. Fernando commented on DERBY-4371:
------------------------------------------------

Hi Bryan & Knut,

select a, sum(b) from t group by a having a+c > 1  : I checked this in both Derby and PostgreSQL,
this is not legal as Bryan correctly mentioned since column reference 'c' included in having
clause is not in group by clause, though the 'a' in select clause is in the group by clause.
(Note that 'b' is used as 'sum(b)', i.e. if we use a column reference with an aggregate function
it need not to be in the group by clause.)

select a+c, sum(b) from t group by a+c having a+c > 1  :This is a valid query in both Derby
and PostgreSQL. I think Bryan is correct, since the same expression used in *all 3* cases,
this is successful. I verified this by following queries:
     select a, sum(b) from t group by a+c having a+c > 1 //this fails 
     select a-c, sum(b) from t group by a+c having a+c > 1 //this fails
     select a-c, sum(b) from t group by a,c having a+c > 1 //this passes
     select a, sum(b) from t group by a+c,a having a+c > 1 //this passes
                      ---> this implies expressions in select clause should be in group
by clause

same way I figured out that an expression used in a having clause should be in group by clause.

Thanks.


> 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
>            Assignee: C.S. Nirmal J. Fernando
>            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