Non-grouping field error with ONLY_FULL_GROUP_BY mode and tables that have the same column name

Question

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;

Error reads:

Non-grouping field 'c1' is used in HAVING clause

Both t1.c1 and 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)

UPDATE:

A MySQL Bug has been raised for this - http://bugs.mysql.com/bug.php?id=80455

Answer 1

It seems a bug (MySQL 5.5 and 5.6). A temporary solution that can be useful is:

SELECT `t1`.`c1`, `der`.`_c1`
FROM `t1`, (SELECT `c1` `_c1`, `c2` `_c2`
            FROM `t2`) `der`
GROUP BY `t1`.`c1`, `der`.`_c1`
HAVING `t1`.`c1` > 1 AND `der`.`_c1` > 1;

SQL Fiddle demo

In MySQL 5.7.11, the query:

SELECT `t1`.`c1`, `t2`.`c1`
FROM `t1`, `t2`
GROUP BY `t1`.`c1`, `t2`.`c1`
HAVING `t1`.`c1` > 1 AND `t2`.`c1` > 1;

runs smoothly.