Bug 3780

Summary: HAVING clause does not read AS columns?
Product: SQL Reporter: Anthony Damico <ajdamico>
Component: allAssignee: SQL devs <bugs-sql>
Status: REOPENED ---    
Severity: enhancement CC: ajdamico, martin.van.dinther, niels, till.wollenberg
Priority: Normal    
Version: -- development   
Hardware: x86_64 (amd64/em64t)   
OS: Windows   

Description Anthony Damico 2015-08-08 04:22:55 CEST
User-Agent:       Mozilla/5.0 (Windows NT 6.3; WOW64; rv:39.0) Gecko/20100101 Firefox/39.0
Build Identifier: 

sorry if i'm mistaking the usage of HAVING, but this works in SQLite so i'm confused why it's breaking in monetdb?

Reproducible: Always

Steps to Reproduce:
CREATE TABLE bugexample (id INTEGER , value INTEGER);
INSERT INTO bugexample VALUES ( 1 , 1 ) , ( 2 , 2 ) , ( 2 , 2 ) ;

SELECT id FROM bugexample GROUP BY id HAVING id > 1;
SELECT id , SUM( value ) AS z FROM bugexample GROUP BY id HAVING z > 1;
SELECT id , COUNT(*) AS z FROM bugexample GROUP BY id HAVING z > 1;

Actual Results:  
sql>CREATE TABLE bugexample (id INTEGER , value INTEGER);
operation successful (12.075ms)
sql>INSERT INTO bugexample VALUES ( 1 , 1 ) , ( 2 , 2 ) , ( 2 , 2 ) ;
3 affected rows (5.528ms)
sql>
sql>SELECT id FROM bugexample GROUP BY id HAVING id > 1;
+------+
| id   |
+======+
|    2 |
+------+
1 tuple (0.941ms)
sql>SELECT id , SUM( value ) AS z FROM bugexample GROUP BY id HAVING z > 1;
SELECT: identifier 'z' unknown
sql>SELECT id , COUNT(*) AS z FROM bugexample GROUP BY id HAVING z > 1;
SELECT: identifier 'z' unknown
sql>

Expected Results:  
read the dynamically-created column
Comment 1 Niels Nes cwiconfidential 2015-08-08 17:08:20 CEST
the having is handled before the selection, ie the z alias isn't known jet.
Comment 2 Anthony Damico 2015-08-08 17:10:33 CEST
sorry, i'm confused, how should this query be written?
Comment 3 Anthony Damico 2015-08-08 17:26:37 CEST
hi, these queries work as a workaround, but i thought the whole point of HAVING was to filter a query *after* it had been generated?

SELECT z , id FROM ( SELECT id , SUM( value ) AS z FROM bugexample GROUP BY id ) AS w GROUP BY id , z HAVING z > 1;

SELECT z , id FROM ( SELECT id , COUNT(*) AS z FROM bugexample GROUP BY id ) AS w GROUP BY id , z HAVING z > 1;


the GROUP BY is necessary for the HAVING statement (so these commands do not work)..  i don't understand the utility of HAVING if it cannot read an alias?

SELECT * FROM ( SELECT id , SUM( value ) AS z FROM bugexample GROUP BY id ) AS w HAVING z > 1;

SELECT * FROM ( SELECT id , COUNT(*) AS z FROM bugexample GROUP BY id ) AS w HAVING z > 1;
Comment 4 Martin van Dinther cwiconfidential 2015-08-13 15:39:57 CEST
I think you are right in that the alias name should be usable in the HAVING clause, so I reopened this issue.

For the time being some possible workarounds are:
1) duplicate the aggregate-expression in the HAVING clause like:
SELECT id , SUM( value ) AS z FROM bugexample GROUP BY id HAVING SUM(value) > 1;
SELECT id , COUNT(*) AS z FROM bugexample GROUP BY id HAVING COUNT(*) > 1;

2) use aggregate query in the FROM-clause like:
SELECT z , id FROM ( SELECT id , SUM( value ) AS z FROM bugexample GROUP BY id ) AS w WHERE z > 1;
SELECT z , id FROM ( SELECT id , COUNT(*) AS z FROM bugexample GROUP BY id ) AS w WHERE z > 1;
These are a little different from your workarounds, as you do not need to GROUP twice.

3) use WITH-clause like:
WITH w as (SELECT id , SUM( value ) AS z FROM bugexample GROUP BY id) SELECT z , id FROM w  WHERE z > 1;
WITH w as (SELECT id , COUNT(*) AS z FROM bugexample GROUP BY id) SELECT z , id FROM w  WHERE z > 1;

4) create an aggregation VIEW once like:
CREATE VIEW w AS SELECT id, COUNT(*) AS count, SUM(value) AS sum_value FROM bugexample GROUP BY id;
and next use that view  for all queries on the aggregate:
SELECT sum_value, id FROM w  WHERE sum_value > 1;
SELECT count, id FROM w  WHERE count > 1;

Thanks for reporting this issue/request.