db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2351) ORDER BY with expression with distinct in the select list returns incorrect result
Date Wed, 05 Mar 2008 19:35:40 GMT

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

A B commented on DERBY-2351:

Army> it seems reasonable to throw an error instead of returning wrong results--as long
Army> as we indicate to users that such a change happens.

Perhaps this is obvious, but it occurs to me that the difference between the query being discussed
and the query that was originally disallowed by changes for this Jira is that the latter was
disallowed because the semantics are not defined, while the former has a well-defined behavior
that Derby fails to accomplish.  I don't think this changes the fact that it's (probably)
better to throw an error than to allow wrong results, but I *do* think that if a commit is
made which causes the second query (from Bryan's Mar 03 comments) to start throwing an error
(instead of returning results in the wrong order), it would be good to file another Jira defect
to indicate that such an error is due to a known (current) Derby limitation.

> 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: d2351_aliasing.diff, d2351_aliasing.diff, derby_2351.diff, derby_2351_v2.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