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 Mon, 18 Feb 2008 22:46:35 GMT

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

A B commented on DERBY-2351:

> It was not the intent of this change to reject the alias form of the query [...]

Okay, thank you for the reply, Bryan.  And thanks for the investigation that you've done to
reply so thoroughly.

> However, it is not clear to me whether that query ought to be allowed or not [...]

>From the various responses to the user thread referenced in my previous comment, it sounds
like different databases treat this kind of thing differently.  So I don't know what the "best"
behavior would be here, either...

For the record, I was merely asking the question because a query which worked in one release
stopped working in the next, with no clear explanation as to why.  The response on the user
list was generally "well that doesn't work in some other databases, so don't do it; use the
alias instead"--which is fine as a workaround, but it left me wondering why the behavior changed
between releases to begin with.  Your reply above filled in the missing information for me--so

> I think we need to spend some time understanding how the SQL Standard intends
> that the ORDER BY, GROUP BY, and HAVING clauses should behave [...]

If the standard lays it all out, then I agree, that certainly seems like a good way to go...

> 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: 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.

View raw message