db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (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 Fri, 11 Nov 2005 00:44:03 GMT
    [ http://issues.apache.org/jira/browse/DERBY-280?page=comments#action_12357313 ] 

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

It is true that, given this patch, that edge case will raise an error. The query would have
to be rewritten as the error message indicates to something like the following:

select a+1 a, a+1 b from bug280 group by a;

The following other queries will raise the same error message and need rephrasing:

select 1+a as a, a+1 as a from bug280 group by a;
select ((a+a)-a)+1 as a, a+1 as a from bug280 group by a;

And so on. I don't think that this patch can be patched to allow these queries to pass. These
sort of semantic checks can only be performed during the bind and normalization phases. By
that time we have lost the information needed to identify the original bug.

I can only repeat my belief that it is better to require the user to rewrite some edge cases
than to silently return wrong results. Thanks for taking the time to review this patch.

> 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