db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: [jira] Commented: (DERBY-765) Valid query fails with ERROR X0A00: The select list mentions column 'A' twice...
Date Wed, 26 Jul 2006 18:23:03 GMT
Hi Army,

Thanks for catching this. No, you're not missing anything. I was 
experimenting with problem queries and didn't correct this one.

Thanks,
-Rick

Army wrote:

> Rick Hillegas (JIRA) wrote:
>
>> 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; 
>
>
> For no apparent reason except that I'm currently reviewing DERBY-883, 
> I noticed the "b" in this query (which I think was supposed to be a 
> "c"?) and so I tried it out to see if it would work...
>
> ij> create table myTable (a int, b int);
> 0 rows inserted/updated/deleted
>
> ij> select a+1 as a, a+1 as c from myTable group by b;
> ERROR 42Y36: Column reference 'A' is invalid.  For a SELECT list with 
> a GROUP BY, the list may only contain grouping columns and valid 
> aggregate expressions.
>
> ij> select a+1 as a, a+1 as c from myTable group by c;
> ERROR 42X04: Column 'C' is either not in any table in the FROM list or 
> appears within a join specification and is outside the scope of the 
> join specification or appears in a HAVING clause and is not in the 
> GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'C' 
> is not a column in the target table.
>
> ij> select a+1 as a, a+1 as c from myTable group by a;
> A          |C
> -----------------------
>
> 0 rows selected
>
> Just curious...am I missing something?
>
> Army
>


Mime
View raw message