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 Thu, 06 Mar 2008 14:50:58 GMT

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

Bryan Pendleton commented on DERBY-2351:
----------------------------------------

Hi Thomas, thanks for catching this. I see the same failure in my environment.

Here's the statement in question:

    select t1.id as idcolumn1, t1.id as idcolumn2 from t1 order by t1.idcolumn1, t1.idcolumn2;

Without my patch, it binds "t1.idcolumn1" to the alias "idcolumn1",
but with my patch it rejects the query because there is no column
"idcolumn1" in table "t1".

The behavior of the patch seems "correct", according to the rules we discussed.

But it does cause a query which was formerly accepted, to be rejected.

My feeling is that the query above deserves to be rejected, but I wonder if anyone
knows what the SQL Standard says about the construct "tablename.aliasname"?



> 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, 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