db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Satheesh Bandaram (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Date Thu, 10 Nov 2005 23:36:04 GMT
    [ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12357306 ] 

Satheesh Bandaram commented on DERBY-280:
-----------------------------------------

I have briefly looked at your patch... While not addressing the underlying problem, the patch
attempts to improve the situation by raising exception instead of possibly wrong results.
This may be a better short term solution, but the patch incorrectly disables unambiguous cases
too.

Using the same table as the patch adds, the following correctly works today:

ij>  select a+1 as a, a+1 as a from bug280 group by a;
A          |A
-----------------------
2          |2
3          |3

2 rows selected

There is no ambiguity here, since both expressions are the same. But after applying the patch,
the query fails:

ij> select a+1 as a, a+1 as a from bug280 group by a;
ERROR X0A00: The select list mentions column 'A' twice. This is not allowed in queries with
GROUP BY or HAVING clauses. Try aliasing one of the conflicting columns to a unique name.


> Wrong result from select when aliasing to same name as used in group by
> -----------------------------------------------------------------------
>
>          Key: DERBY-280
>          URL: http://issues.apache.org/jira/browse/DERBY-280
>      Project: Derby
>         Type: Bug
>   Components: SQL
>     Reporter: Bernt M. Johnsen
>     Assignee: Rick Hillegas
>     Priority: Minor
>  Attachments: bug280.diff
>
> Wrong result from select when aliasing to same name as used in group by. Example:
> If we have the following table:
>     ij> select * from tt;
>     I          |J
>     -----------------------
>     1          |2
>     2          |3
>     1          |2
>     2          |3
>     2          |3
>                                                                                     
                                                        
>     5 rows selected
> The following select is ok:
>     ij> select i, count(*) as cnt from tt group by i;
>     I          |CNT
>     -----------------------
>     1          |2
>     2          |3
>                                                                                     
                                                        
>     2 rows selected
> But this one returns wrong result in the aliased column:
>     ij> select i, count(*) as i from tt group by i;
>     I          |I
>     -----------------------
>     1          |1
>     2          |2
>                                                                                     
                                                        
>     2 rows selected
>                                                                                     
                                                        

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message