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 04:36:50 GMT

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

Bryan Pendleton updated DERBY-2351:

    Attachment: d2351_aliasing.diff

Thanks Thomas and Army for having a look at the patch.

Attached is a revised version of d2351_aliasing.diff, with
the whitespace corrected in ResultColumn.java (I think),
and a few additional test cases.

Thomas, please let me know if you aren't able to see
orderby.out in this patch.

Army, with respect to the error message, I agree that it is
confusing, as there only appears to be one column in
the result of the query expression. However, I think this
behavior predates this patch, and moreover it is also
independent of whether DISTINCT is present. Even without
this patch applied:

ij> select name as age from person order by person.age;
ERROR 42X79: Column name 'AGE' appears more than once in the result of the query expression.

While looking at this, however, I discovered a possibly more
disturbing problem. The following query *works* before the
patch, but is *rejected* after the patch:

   select person.name as name, pets.name as pet_name from person,pets order by name;

Before this patch, the ORDER BY analysis looks only at the exposed 
names 'name' and 'pet_name'. But with the patch in place, the ORDER BY
processing sees that the column with the exposed name pet_name
also has the underlying source column name 'name', and so it sees
two possible columns to which 'ORDER BY NAME' could refer, and
refuses the query as ambiguous.

I'm not quite sure what to do, but I'm attaching the updated patch
anyway, to try to keep the discussion going.

> 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