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
