[ http://issues.apache.org/jira/browse/DERBY-765?page=comments#action_12423684 ] Rick Hillegas commented on DERBY-765: -------------------------------------
Here's a release note for this issue: PROBLEM Derby raises an error for queries 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 select column or another alias For instance, the following query raises an error: select a+1 as a, a+1 as a from myTable group by a; SYMPTOM If you pose such a query, you will see an error like the following: ERROR X0A00: The select list mentions column 'A' twice. This is not allowed in queries with GROUP BY or HAVING clauses. Try aliasing one of the conflicting columns to a unique name. CAUSE Due to a very hard bug in Derby's SQL interpreter, queries like the one above can return incorrect results. Until that bug is fixed, Derby has been changed so that it rejects these queries. We believe it's better to reject these queries than to return bad results. SOLUTION: The fix involves rewriting how Derby interprets queries with GROUP BY or HAVING clauses. WORKAROUND: You can work around this limitation by rewriting your query so that the SELECT list does not alias an expression to the same name as another column or alias. The following rewritten query works fine: select a+1 as a, a+1 as c from myTable group by b; > Valid query fails with ERROR X0A00: The select list mentions column 'A' > twice... > -------------------------------------------------------------------------------- > > Key: DERBY-765 > URL: http://issues.apache.org/jira/browse/DERBY-765 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.2.0.0 > Reporter: Kathey Marsden > Priority: Minor > > The following query is valid but fails with 10.2 > ij> create table bug280 > ( > a int, > b int > ); > 0 rows inserted/updated/deleted > ij> insert into bug280( a, b ) > values ( 1, 1 ), ( 1, 2 ), ( 1, 3 ), ( 2, 1 ), ( 2, 2 ); > 5 rows inserted/updated/deleted > ij> select a+1 as a, a+1 as a from bug280 group by a; > ERROR X0A00: The select list mentions column 'A' twice. This is not allowed > in queries with GROUP BY or HAVING clauses. Try aliasing one of the > conflicting columns to a unique name. -- 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
