[jira] Commented: (DERBY-280) Wrong result from select when aliasing to same name as used in group by

["Rick Hillegas (JIRA)" <de...@db.apache.org>]

    [ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12356646 ] 

Rick Hillegas commented on DERBY-280:
-------------------------------------

>From my reading of Part 2 of the ANSI spec, this syntax should be disallowed:

select i, count(*) as i from tt group by i;

This query gives rise to a table whose row type contains two fields with the same name. I believe this is illegal. Here is my reasoning:

1) According to syntax rule 2 in section 7.12, a <query specification> gives rise to a table.

2) According to section 4.14.1, all rows of a table have the same row type.

3) According to section 4.13, two fields in the same row type may not have the same name.

I propose to raise a syntax error in the parser when a <query specification> gives rise to a table with two identically named columns.

> 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

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