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 17:06:34 GMT

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


Mime
View raw message