You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Satheesh Bandaram (JIRA)" <de...@db.apache.org> on 2005/06/11 22:10:47 UTC
[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
[ 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