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 Sun, 02 Mar 2008 18:30:50 GMT

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

Bryan Pendleton commented on DERBY-2351:

Currently, the code which associates ORDER BY column specs with the ResultColumn
instances in the SELECT's RCL examines the SELECT list using only the "exposedName".
The exposedName is the same as the column name for simple column references,
but is the alias name when a column alias is used, and is an internally generated name
for expressions. So in:

  select age, name as first_name, age / 7 as age_in_dog_years, upper(name) from person;

we have 4 columns, with the exposedName 's of:
 - age
 - first_name
 - age_in_dog_years
 - SQLCol1

Since ORDER BY looks only at the exposed name, when it sees a statement like:

  select name as first_name from person order by name;

it doesn't find the column 'name' in the SELECT RCL, so it decides thta it needs
to "pull up" the name column from the underlying table.

Since pulled up columns are invalid for DISTINCT queries, we get the error in question.

It seems relatively straightforward to enhance ResultColumnList.java's
findResultColumnForOrderBy() and getOrderByColumnToLink() methods so that
they search for columns using both the exposedName *and* the underlying
column reference's name, thus making the order by column *not* be a pulled-up
column, and thus valid in DISTINCT queries, and that does indeed solve the repro 
script from the 18-feb-2008 comment on this issue.

I'm investigating working that change up as a proper patch, with additional test
cases, and running the current regression tests to try to figure out what else may break
as a result of introducing this new column resolution algorithm.

> 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:,,
>         Environment: Any
>            Reporter: Yip Ng
>            Assignee: Bryan Pendleton
>             Fix For:,
>         Attachments: 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.

View raw message