db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2351) ORDER BY with expression with distinct in the select list returns incorrect result
Date Mon, 18 Feb 2008 21:16:34 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2351?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12570011#action_12570011

Bryan Pendleton commented on DERBY-2351:

It was not the intent of this change to reject the alias form of the query; this change
was specifically intended to address the issue that Yip raised in Feb 2007:
in which there was a conflict between the DISTINCT and ORDER BY
specifications in the query.

I agree with Army that "select distinct c1 as a1 from t order by c1" does not
contain such a conflict, and so this patch did not intend to reject that query.

However, it is not clear to me whether that query ought to be allowed or not,
since I don't understand whether it is supposed to be legal to refer to an
underlying base table column in the ORDER BY clause, or whether the column
alias is *required* to be used. Note, in particular, this comment by Jack Klebanoff
regarding the topic:

I'm worried that we may have a number of related problems in this whole area of
handling expressions, aliasing, column number references, and simple column
references in ORDER BY, GROUP BY, and HAVING clauses. Note, for example,
DERBY-2457, DERBY-2085, DERBY-84, DERBY-280, DERBY-1861, DERBY-127,
and DERBY-3094 for  some other examples, both past and present.

I think we need to spend some time understanding how the SQL Standard intends
that the ORDER BY, GROUP BY, and HAVING clauses should behave with respect
to column references, columns identified by column number, and value expressions,
and then we need to ensure that Derby is behaving correctly.

> ORDER BY with expression with distinct in the select list returns incorrect result
> ----------------------------------------------------------------------------------
>                 Key: DERBY-2351
>                 URL: https://issues.apache.org/jira/browse/DERBY-2351
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions:,,
>         Environment: Any
>            Reporter: Yip Ng
>            Assignee: Bryan Pendleton
>             Fix For:,
>         Attachments: derby_2351.diff, derby_2351_v2.diff, reproTests.diff
> When distinct is in the select list and the query has order by with expression, the resultset
produced contains an additional column.  
> ij> create table t1 (c1 int, c2 varchar(10))
> 0 rows inserted/updated/deleted
> ij> insert into t1 values (1,'a'),(2,'b'),(3,'c');
> 3 rows inserted/updated/deleted
> select distinct c1, c2 from t1 order by c1;
> C1         |C2
> ----------------------
> 1          |a
> 2          |b
> 3          |c
> 3 rows selected
> ij> select distinct c1, c2 from t1 order by c1+1;
> C1         |C2        |3                                 <=====returns 3 columns,
incorrect result returned
> ----------------------------------
> 1          |a         |2
> 2          |b         |3
> 3          |c         |4
> 3 rows selected

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

View raw message