"The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well."
That is why Select * works, as then columns a,b,c could be used in HAVING clause.
Thanks for your answer, but IFNULL( aaaa.a, aaaa.b) is in GROUP BY and when used in HAVING error occurs...
In my example is by mistake shortened IFNULL(a,b) but with IFNULL( aaaa.a, aaaa.b) the result is the same...
So I still think it is a bug
The error is Unknown column 'aaaa.a' in 'having clause', the server does not do subexpression matching. You do not group by a, that's why you have this error. As a workaround you can try to group by IFNULL(a,b),a,b.
Of course I know, that I can add a,b to GROUP BY - in this case IFNULL(a,b) is useless there...
However this is not a syntax issue, grouping by a,b can cause different query result:
row 1: a=1, b=1
row 2: a=null, b=1
with group by ifnull(a,b) -> result is groupped into 1 row
with group by ifnull(a,b),a,b -> result is groupped into 2 rows!!
This makes impossible to use non-trivial expressions in group by to be used in having!!
Please reconsider this behavior... Thanks
To use non-trivial expressions in group by and in having, you can use select aliases:
CREATE TABLE aaaa (a INT, b INT, c int); |
SELECT IFNULL(a,b) AS x FROM aaaa GROUP BY x HAVING x<>SUM( IFNULL( aaaa.c, 0)); |
I agree that a database, technically, should be able to figure it out on its own without hints like aliases. But it is not a bug that it does not do it, it's a feature that wasn't implemented yet.
Thanks for your answer, ok, this works, however it is not a system solution...
Is is possible to change the type of issue to Improvement or New feature?
I think this really should be standard behavior... Thank you