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 Sat, 11 Jun 2005 20:10:47 GMT
    [ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12313377 ] 

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

I think this problem is caused because of rewriting of group by clause and having multiple
result columns with same name. ("i" in this case). A query like SELECT I, COUNT(*) AS I FROM
TT GROUP BY I gets rewritten to something like SELECT I, I FROM (select I, COUNT(*) AS I FROM
TT GROUP BY I) internally, to facilitate processing of GROUP BY/HAVING clauses. When each
of the resultColumn "I" is bound to expressions from the inner query, both resolve to the
first "I" causing the wrong results.

If the query were to flip the expressions in the original query, to something like: SELECT
COUNT(*) AS I, I FROM TT GROUP BY I, then the result would still be wrong, but both result
columns for I would be for COUNT(*) column.

ij> select i, count(*) as i from tt group by i;
I          |I
-----------------------
1          |1
2          |2

2 rows selected
ij> select count(*) as i, i from tt group by i;
I          |I
-----------------------
1          |1
1          |1

2 rows selected

I think a fix needs to address the binding of rewritten querries correctly. When Derby rewrites
the query to facilitate group by/having processing, the correlation name for the inner query
is NULL.

> 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
>     Priority: Minor

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