Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of chris@aptivate.org designates
 87.106.150.205 as permitted sender)
Date: Fri, 10 Sep 2010 10:48:36 +0100 (BST)
From: Chris Wilson <chris@aptivate.org>
To: Derby Discussion <derby-user@db.apache.org>
Subject: Cannot use column alias in HAVING clause
Message-ID: <alpine.LRH.2.00.1009100950520.10504@mail.aptivate.org>
User-Agent: Alpine 2.00 (LRH 1167 2008-08-23)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; format=flowed; charset=US-ASCII

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.