I'm finding that MySQL seems to have an issue when
ONLY_FULL_GROUP_BY mode is enabled and the query includes a
HAVING that refers to grouped columns from different tables that have the same name.
This is the schema to replicate the issue:
CREATE TABLE t1 (c1 INT, c2 INT); CREATE TABLE t2 (c1 INT, c2 INT);
And you need to add
ONLY_FULL_GROUP_BY to your SQL mode:
SET SESSION SQL_MODE='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'
And this is the query to create the error:
SELECT t1.c1, t2.c1 FROM t1, t2 GROUP BY t1.c1, t2.c1 HAVING t1.c1 > 1 AND t2.c1 > 1;
Non-grouping field 'c1' is used in HAVING clause
t2.c1 are grouped, so I don't see what is wrong with this query.
Additionally if the query refers to different column names from the same table, the query works. For example:
SELECT t1.c1, t2.c2 FROM t1, t2 GROUP BY t1.c1, t2.c2 HAVING t1.c1 > 1 AND t2.c2 > 1;
Am I doing something wrong? I can't find a report of this issue anywhere. I can replicate the error on MySQL version 5.5.44 on Ubuntu and 5.6.24 on Gentoo.
SQLFiddle for this - http://sqlfiddle.com/#!2/da2704/4
The simple query above is just to demonstrate the issue. Here is a version that cannot be achieved using
WHERE instead of
HAVING and suffers the same problem:
... HAVING t1.c1 > SUM(t1.c2) AND t2.c1 > SUM(t2.c2)
A MySQL Bug has been raised for this - http://bugs.mysql.com/bug.php?id=80455