[
https://issues.apache.org/jira/browse/DERBY-2351?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12574887#action_12574887
]
Thomas Nielsen commented on DERBY-2351:
---------------------------------------
Bryan>Attached is a revised version of d2351_aliasing.diff, with
Bryan>the whitespace corrected in ResultColumn.java (I think),
No whitespace in the currnet diff :)
>Thomas, please let me know if you aren't able to see orderby.out in this
>patch.
The .out was there all along, it just doesn't apply for some reason. Same
problem with the current patch. It was made from the head of trunk, right?
I agree that the second query from 10.2 produces the wrong results - it's using
an explicit table.columnname reference for ordering. In this particular case an
ambigous error would be better that wrong results.
Could we use the fact that the user specified tableName.columnName, and not
just columnName to distinguish between the aliased and original column names
somehow? If using t.c notation you could actually exclude aliased columns from
the check.
This would mean
select distinct person.name as age from person order by person.age;
=> explicit check on column named 'age' in table 'person'
select distinct person.name as age from person order by age;
=> alias 'age' exists, check alias
select distinct person.name as their_age from person order by age;
=> alias 'age' does not exists, check 'person' for 'age'
select person.name as name, pets.name as pet_name from person,pets order by
name;
=> alias 'name' exists, check alias
select person.name as person_name, pets.name as pet_name from person,pets
order by person.name;
=> explicit check on column named 'name' in table 'person'
but
select person.name as person_name, pets.name as pet_name from person,pets
order by name;
=> ambiguous, no alias 'name', but both 'person' and 'pets' have column
'name'
We may actually lack information on whether the user did explicitly use t.c or
only column/alias name at this stage, so it might not be possible at all for
all I know. I also see potential for breaking existing applications with such
changes. But if keeping the old behavior produces wrong results, your current
patch with throwing an ambiguous exception is still a lot better than returning
wrong results IMHO!
> 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.
