Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <157945204.1204777858523.JavaMail.jira@brutus>
Date: Wed, 5 Mar 2008 20:30:58 -0800 (PST)
From: "Bryan Pendleton (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-2351) ORDER BY with expression with distinct
 in the select list returns incorrect result
In-Reply-To: <5230524.1171672685493.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ 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_checkQualifiedName.diff

Thomas, I thought that your suggestion about using different
name resolution rules depending on whether the ORDER BY
column reference was qualified or unqualified was excellent,
and I decided to give it a try.

Attached is d2351_aliasing_checkQualifiedName.diff,
which uses different resolution rules depending on whether
the ORDER BY column reference is table.column, or just column:
 - if the table name is provided, we match against the
   underlying table name, and don't consider any aliases
 - if the table name is NOT provided, we first match against
   the alias name, if present, and if no alias name matches
   then we match against the underlying source column name.

It seems to resolve the problematic queries that we've been
discussing over the past several days, and doesn't obviously
break any of the other queries in the orderby.sql suite.

I'm running a more complete set of regression tests overnight.
Please have a look at this latest patch and let me know what you think.


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