[
https://issues.apache.org/jira/browse/DERBY-2351?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Bryan Pendleton updated DERBY-2351:
-----------------------------------
Attachment: reproTests.diff
The symptoms of this problem seem to have changed in the last 6 months.
However, there is definitely still something wrong. With the latest trunk, I
don't see the extra mystery columns in the IJ output. However, I do appear to
be getting wrong results.
And I agree with Yip that in the wrong results case, the query is ambiguous and
should be rejected with an error. Interestingly, in the wrong results case,
Derby currently chooses neither of the answers that Yip suggested, but instead
chooses to display all three rows! Thus Derby currently chooses to violate the
DISTINCT part of the query, rather than violating the ORDER BY part of the
query. Given that the two parts of the query are in conflict, I'm not sure that
it really matters very much which part of the query Derby disobeys, as either
way is wrong and the only thing that can be done is to reject the query as
ambiguous.
I'm attaching reproTests.diff, a patch proposal with some new test cases for
the ORDER BY test suite. These test cases demonstrate the wrong results.
Although I don't currently have a fix for this problem, or even a clear idea of
how to fix it, I thought that it would be useful to post the test cases anyway.
> 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.2.0, 10.3.0.0
> Environment: Any
> Reporter: Yip Ng
> Assignee: Bryan Pendleton
> Attachments: 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.
