6

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

3

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.

  • Thank you confirming this works in 5.7. I'm aware ONLY_FULL_GROUP_BY is on by default in 5.7 and was curious of this. It is unfortunate I will need to write uglier queries in 5.6. – Courtney Miles Feb 20 '16 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.