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-765) Valid query fails with ERROR X0A00: The select list mentions column 'A' twice...
Date Wed, 26 Jul 2006 18:24:15 GMT
    [ http://issues.apache.org/jira/browse/DERBY-765?page=comments#action_12423690 ] 
            
Rick Hillegas commented on DERBY-765:
-------------------------------------

Re-entering the release note to correct a problem with the previous version, found by Army.

 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 b from myTable group by a;


> 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

        

Mime
View raw message