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 Tue, 04 Mar 2008 17:21:41 GMT

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

A B commented on DERBY-2351:

> I think this behavior predates this patch, and moreover it is also
> independent of whether DISTINCT is present. 

Ah, okay, thanks for pointing that out.

> if we had really sorted the rows by person.age we'd have produced the result
> ----------
> john
> mary
> john

Just to be clear, note the following:

ij> select * from person order by age;
NAME      |AGE
john      |10
john      |30
mary      |50

If we sort the rows by person.age the order is, in fact, "john, john, mary".  So from that
it's hard to tell whether 10.2 sorted the query on the alias "AGE" (meaning column NAME) or
the actual column AGE.  But when I added another row, ('zack', 5), we see the problem you're
talking about:

ij> insert into person values ('zack', 5);
1 row inserted/updated/deleted

ij>  select person.name as age from person order by person.age;

Since we're supposed to be sorting by person.age, zack should be first, not last.  So you're
right, 10.2 seems wrong.

> how bad is it to break these queries? They did not throw errors before, but were they
> giving the correct results?

I agree with Thomas in that it seems reasonable to throw an error instead of returning wrong
results--as long as we indicate to users that such a change happens.  Which is, incidentally,
exactly what the original fix for this issue did: a query that used to work is now rejected
as invalid.

And that in turn begs the question: should this issue have been marked "Existing Application
Impact" since the solution affects existing applications (queries that used to run without
error will now fail)?  Seems like was released with the initial fix, which changed
the behavior, but no indication of existing application impact was made.  I assume that was

> 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