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] Updated: (DERBY-280) Wrong result from select when aliasing to same name as used in group by
Date Fri, 04 Nov 2005 23:06:21 GMT
     [ http://issues.apache.org/jira/browse/DERBY-280?page=all ]

Rick Hillegas updated DERBY-280:

    Attachment: bug280.diff

I am attaching a patch which makes the situation better. I tried a couple approaches to fixing
this bug: 1) I tried fabricating unique aliases in the inner query block and propagating the
original aliases just to the outer query block; this broke ORDER BY logic. 2) I tried binding
the outer select list by position rather than by name; this broke whenever the compiler, to
solve other problems, fabricated dummy columns. In addition, with both approaches I found
myself grafting special-case logic across many classes. I felt that these solutions made the
code more brittle and were not warranted by this edge-case problem. I feel that the cleanest
way to solve this problem is to redo the special parser logic which mangles the abstract syntax
tree for queries with GROUP BY and HAVING  clauses (see bug 681). That, however, is a big
task. Until bug 681 is fixed, I'm offering the attached patch. The patch raises an exception
if the customer writes a query with the following profile:

o The query contains a GROUP BY or HAVING clause
o The query contains an expression which is aliased to the same name as a selected column

The exception tells the customer how to rewrite their query so that it will compile. I think
this is better than the current situation in which the query silently returns the wrong results.
It is worth pointing out that this patch does not break any queries in our existing regression

Derbyall runs cleanly with this patch. The patch contains the following files:

M      java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
M      java\engine\org\apache\derby\iapi\reference\SQLState.java
M      java\engine\org\apache\derby\loc\messages_en.properties
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\aggregate.sql
M      java\testing\org\apache\derbyTesting\functionTests\master\aggregate.out

> 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:
For more information on JIRA, see:

View raw message