db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Nielsen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2351) ORDER BY with expression with distinct in the select list returns incorrect result
Date Tue, 04 Mar 2008 08:33:52 GMT

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

Thomas Nielsen commented on DERBY-2351:
---------------------------------------

Bryan>Attached is a revised version of d2351_aliasing.diff, with
Bryan>the whitespace corrected in ResultColumn.java (I think),

No whitespace in the currnet diff :)

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

The .out was there all along, it just doesn't apply for some reason. Same problem with the
current patch. It was made from the head of trunk, right?

I agree that the second query from 10.2 produces the wrong results - it's using an explicit
table.columnname reference for ordering. In this particular case an ambigous error would be
better that wrong results.

Could we use the fact that the user specified tableName.columnName, and not just columnName
to distinguish between the aliased and original column names somehow? If using t.c notation
you could actually exclude aliased columns from the check. 

This would mean 
   
   select distinct person.name as age from person order by person.age; 
   => explicit check on column named 'age' in table 'person'

   select distinct person.name as age from person order by age; 
   => alias 'age' exists, check alias
   
   select distinct person.name as their_age from person order by age; 
   => alias 'age' does not exists, check 'person' for 'age'

  select person.name as name, pets.name as pet_name from person,pets order by name; 
  => alias 'name' exists, check alias

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

   => explicit check on column named 'name' in table 'person'

but

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

   => ambiguous, no alias 'name', but both 'person' and 'pets' have column 'name'

We may actually lack information on whether the user did explicitly use t.c or only column/alias
name at this stage, so it might not be possible at all for all I know. I also see potential
for breaking existing applications with such changes. But if keeping the old behavior produces
wrong results, your current patch with throwing an ambiguous exception is still a lot better
than returning wrong results IMHO!

> 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: 10.2.1.6, 10.2.2.0, 10.3.1.4
>         Environment: Any
>            Reporter: Yip Ng
>            Assignee: Bryan Pendleton
>             Fix For: 10.3.2.2, 10.4.0.0
>
>         Attachments: d2351_aliasing.diff, d2351_aliasing.diff, 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.


Mime
View raw message