db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-2351) ORDER BY with expression with distinct in the select list returns incorrect result
Date Sun, 10 Jun 2007 21:47:25 GMT

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


Mime
View raw message