Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1529506233.1204606370638.JavaMail.jira@brutus>
Date: Mon, 3 Mar 2008 20:52:50 -0800 (PST)
From: "Bryan Pendleton (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (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:comment-tabpanel&focusedCommentId=12574842#action_12574842 ] 

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

In Derby 10.2, all of the following queries work:

    -- orders the rows by the first column (person.name):
    select person.name as name, pets.name as pet_name from person, pets order by name;
    -- orders the rows by the first column (person.name, aliased to 'age'):
    select person.name as age from person order by person.age;
    -- again appears to order the rows by the aliased column?
    select distinct person.name as age from person order by person.age;

The first query is only ambiguous if we interpret an unqualified ORDER BY
reference to refer to either an exposedName or to an underlying column name.
The third query, of course, is inspired by the query that Yip noted as problematic
in the comment on 16-Feb-2007. I think that the results from
the second query in 10.2 are particularly disturbing, since even though the
user specifically said to sort by 'person.age', the 10.2 code apparently sorted
by the exposedName column 'age', since if we had really sorted the rows by
person.age we'd have produced the result 

AGE
----------
john
mary
john

So, how bad is it to break these queries? They did not throw errors before,
but were they giving the correct results?

The complete 10.2 script session is pasted  below. 
============================================================
ij version 10.2
ij> connect 'jdbc:derby:ten2db;create=true';
ij> create table person (name varchar(10), age int);
0 rows inserted/updated/deleted
ij> create table pets (name varchar(10), age int);
0 rows inserted/updated/deleted
ij> insert into person values ('john', 30), ('mary', 50), ('john', 10);
3 rows inserted/updated/deleted
ij> insert into pets values ('Buster', 1), ('Fido', 3);
2 rows inserted/updated/deleted
ij> select person.name as name, pets.name as pet_name from person, pets order by name;
NAME      |PET_NAME
---------------------
john      |Fido
john      |Fido
john      |Buster
john      |Buster
mary      |Fido
mary      |Buster

6 rows selected
ij> select person.name as age from person order by person.age;
AGE
----------
john
john
mary

3 rows selected
ij> select distinct person.name as age from person order by person.age;
AGE
----------
john
mary


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