Cannot use column alias in HAVING clause

[Chris Wilson <ch...@aptivate.org>]

Hi all,

I'd like to be able to use a column alias (from the column list of the 
SELECT statement) in the HAVING clause. When I try, I get the following 
error:

ij> select 0 as foo from rita.request having foo;

ERROR 42X04: Column 'FOO' 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 'FOO' is not a 
column in the target table.

Of course, this is a trivial example, but in my real-life case I have an 
aggregate function being given an alias:

SELECT ... SUM(mo2_.ltu_qty) as req_line_ledger_balance ... GROUP BY 
... HAVING (req_line_ledger_balance <> 0)

If I include the SUM(...) expression again in the HAVING clause, 
presumably Derby will evaluate it a second time, not knowing that it's 
already evaluated it and the result is in the selected columns?

I understand the purpose of the restriction and the error message, 
"appears in a HAVING clause and is not in the GROUP BY list," but 
aggregate functions do not have to be in the GROUP BY list in order to be 
available in time for HAVING to operate on them, as the values are in the 
output set.

Derby clearly knows this, as using the expression instead of the alias 
works:

ij> select sum(ltu_qty) AS sum_ltu_qty from rita.movement group by 
move_site_id having sum(ltu_qty) > 0;

SUM_LTU_QTY
-----------
0 rows selected

ij> select sum(ltu_qty) AS sum_ltu_qty from rita.movement group by
move_site_id having sum_ltu_qty > 0;

ERROR 42X04: Column 'SUM_LTU_QTY' ... appears in a HAVING clause and is 
not in the GROUP BY list...

I would try to write a patch myself, but I'd need some help to get Derby 
to compile in Eclipse so that I can debug it.

Cheers, Chris.
-- 
Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES

Aptivate is a not-for-profit company registered in England and Wales
with company number 04980791.

Re: Cannot use column alias in HAVING clause

[Chris Wilson <ch...@aptivate.org>]

Hi Bryan,

On Fri, 10 Sep 2010, Bryan Pendleton wrote:
> On 09/10/2010 02:48 AM, Chris Wilson wrote:
> > I'd like to be able to use a column alias (from the column list of the
> > SELECT statement) in the HAVING clause.
> ...
> > Derby clearly knows this, as using the expression instead of the alias
> > works:
> 
> After fairly extensive discussion (see this issue for a good overview:
> https://issues.apache.org/jira/browse/DERBY-2457), the community decided,
> I believe, that this was correct behavior according to the SQL standard.

Thanks for pointing me to that issue. I would not have guessed that it had 
already been discussed in such detail :)

I would try to argue the case, but I guess I'd have to take it up with the 
SQL Standards group, and that might take a few years :)

Cheers, Chris.
-- 
Aptivate | http://www.aptivate.org | Phone: +44 1223 760887
The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES

Aptivate is a not-for-profit company registered in England and Wales
with company number 04980791.

Re: Cannot use column alias in HAVING clause

[Bryan Pendleton <bp...@gmail.com>]

On 09/10/2010 02:48 AM, Chris Wilson wrote:
 > I'd like to be able to use a column alias (from the column list of the
 > SELECT statement) in the HAVING clause.
...
 > Derby clearly knows this, as using the expression instead of the alias
 > works:

After fairly extensive discussion (see this issue for a good overview:
https://issues.apache.org/jira/browse/DERBY-2457), the community decided,
I believe, that this was correct behavior according to the SQL standard.

thanks,

bryan