[
https://issues.apache.org/jira/browse/DERBY-2351?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12569950#action_12569950
]
A B commented on DERBY-2351:
----------------------------
Per the user thread found here:
http://article.gmane.org/gmane.comp.apache.db.derby.user/8393
the following query appears to have worked in 10.3.1.4 but fails in 10.3.2.1
and later:
create table t(c1 int, c2 int);
select distinct c1 from t order by c1; -- works
select distinct c1 as a1 from t order by c1; -- used to work, now fails
The failure shows the exception that was added for this issue, namely:
ERROR 42879: The ORDER BY clause may not contain column 'C1', since the query
specifies
DISTINCT and that column does not appear in the query result.
I skimmed over the comments for this issue and, from what I can tell, the above
query (with the column alias) is *not* ambiguous--at least, not in the way that
this issue describes. I.e. if the alias "A1" can be identified as pointing
to "C1" (which should be possible...I think?) then the query satisfies the
requirement that ORDER BY columns be a subset of the DISTINCT columns.
Was it the intent of this issue to deliberately block queries such as this one,
or was that an accident? An easy enough workaround exists--just specify "A1"
in the order by clause instead of "C1"--but I think the question remains: is
that supposed to be necessary?
Note: I haven't actually done a pre- and post- commit check for this specific
issue, I'm just assuming (perhaps incorrectly) that this issue is the one that
changed the behavior, given the discussion and the new error code. Apologies
if that assumption is wrong...
> 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.1, 10.4.0.0
>
> 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.
