[
https://issues.apache.org/jira/browse/DERBY-2351?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575061#action_12575061
]
A B commented on DERBY-2351:
----------------------------
> I think this behavior predates this patch, and moreover it is also
> independent of whether DISTINCT is present.
Ah, okay, thanks for pointing that out.
> if we had really sorted the rows by person.age we'd have produced the result
>
> AGE
> ----------
> john
> mary
> john
Just to be clear, note the following:
ij> select * from person order by age;
NAME |AGE
----------------------
john |10
john |30
mary |50
If we sort the rows by person.age the order is, in fact, "john, john, mary".
So from that it's hard to tell whether 10.2 sorted the query on the alias "AGE"
(meaning column NAME) or the actual column AGE. But when I added another row,
('zack', 5), we see the problem you're talking about:
ij> insert into person values ('zack', 5);
1 row inserted/updated/deleted
ij> select person.name as age from person order by person.age;
AGE
----------
john
john
mary
zack
Since we're supposed to be sorting by person.age, zack should be first, not
last. So you're right, 10.2 seems wrong.
> how bad is it to break these queries? They did not throw errors before, but
> were they
> giving the correct results?
I agree with Thomas in that it seems reasonable to throw an error instead of
returning wrong results--as long as we indicate to users that such a change
happens. Which is, incidentally, exactly what the original fix for this issue
did: a query that used to work is now rejected as invalid.
And that in turn begs the question: should this issue have been marked
"Existing Application Impact" since the solution affects existing applications
(queries that used to run without error will now fail)? Seems like 10.3.2.1
was released with the initial fix, which changed the behavior, but no
indication of existing application impact was made. I assume that was
unintentional?
> 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.
