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] Updated: (DERBY-2351) ORDER BY with expression with distinct in the select list returns incorrect result
Date Tue, 04 Mar 2008 17:47:42 GMT

     [ https://issues.apache.org/jira/browse/DERBY-2351?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Bryan Pendleton updated DERBY-2351:

    Derby Info: [Existing Application Impact]

The fix to this issue may impact existing applications because some
queries which were previously accepted and executed will now
return errors. The intent of the fix is that the only queries that fall
into that category are those which are ambiguous due to the fact
that they specify a DISTINCT set of results, but the ORDER BY clause
mentions one or more columns which are not present in the DISTINCT
clause, introducing an ambiguity about how to perform the ordering
when there exist multiple rows with the same values for the DISTINCT
column(s) but different values for the ORDER BY columns.

> 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: d2351_aliasing.diff, d2351_aliasing.diff, derby_2351.diff, derby_2351_v2.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