db-derby-dev mailing list archives

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

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

A B commented on DERBY-2351:
----------------------------

modifySynonymResult.diff looks good to me.  Might be nice if the whitespace inconsistencies
could be patched up a bit, but functionally I think the patch is good.

Two minor suggestions:

1) Could the logic in ColumnReference.columnNameMatches() use the new "getSourceColumnName()"
method, instead of duplicating the "instanceof ColumnReference" check?

2) Do you think it would be worth it add some sanity queries for table aliasing, as well?
 Ex, I ran the following:

  -- All succeed.
  select distinct i a from t1 hmm order by i;
  select distinct i a from t1 hmm order by a;
  select distinct i a from t1 hmm order by hmm.i;

  -- All throw an error.
  select distinct i a from t1 hmm order by t1.i;
  select distinct i a from t1 hmm order by t1.a;
  select distinct i a from t1 hmm order by hmm.a;
 
>From what I can tell treatment of all of the above queries is correct, so this would just
be a matter of adding them to the test script.  That could come as a separate patch, though.

Thanks for your persistence on this one, Bryan!

> 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, d2351_aliasing_checkQualifiedName.diff,
derby_2351.diff, derby_2351_v2.diff, modifySynonymResults.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