[ 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 tests.
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:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
