Bug #80455 Non-grouping field error with tables that have the same column names
Submitted: 20 Feb 2016 23:21 Modified: 21 Feb 2016 21:58
Reporter: Courtney Miles Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.5, 5.6 OS:Linux
Assigned to: CPU Architecture:Any
Tags: GROUP BY, ONLY_FULL_GROUP_BY, SQL_MODE

[20 Feb 2016 23:21] Courtney Miles
Description:
With ONLY_FULL_GROUP_BY enabled, a HAVING clause that refers to columns from two or more tables, where the columns have the same name, will trigger a "Non-grouping field" error.

This also happens if you join to the same table multiple times and use the table alias in the HAVING.

How to repeat:
DROP TABLE IF EXISTS t1, t2;

SET @@sql_mode='ONLY_FULL_GROUP_BY';

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);

-- Two different tabled with same column name in HAVING
SELECT t1.c1, t2.c1
FROM t1, t2
GROUP BY t1.c1, t2.c1
HAVING t1.c1 > SUM(t1.c2) AND t2.c1 > SUM(t2.c2);

-- ERROR 1463 (42000): Non-grouping field 'c1' is used in HAVING clause
[21 Feb 2016 21:58] Miguel Solorzano
Thank you for the bug report. Repeatable on version reported 5.5 and 5.6 (version 5.7 isn't affected by this bug).

miguel@tikal ~/dbs $ 5.5/bin/mysql -uroot -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.49-debug Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS t1, t2;
SET @@sql_mode='ONLY_FULL_GROUP_BY';

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);

-- Two different tabled with same column name in HAVING
SELECT t1.c1, t2.c1
FROM t1, t2
GROUP BY t1.c1, t2.c1
HAVING t1.c1 > SUM(t1.c2) AND t2.c1 > SUM(t2.c2);Query OK, 0 rows affected (0,18 sec)

mysql>
mysql> SET @@sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0,00 sec)

mysql>
mysql> CREATE TABLE t1 (c1 INT, c2 INT);
Query OK, 0 rows affected (0,18 sec)

mysql> CREATE TABLE t2 (c1 INT, c2 INT);
Query OK, 0 rows affected (0,18 sec)

mysql>
mysql> -- Two different tabled with same column name in HAVING
mysql> SELECT t1.c1, t2.c1
    -> FROM t1, t2
    -> GROUP BY t1.c1, t2.c1
    -> HAVING t1.c1 > SUM(t1.c2) AND t2.c1 > SUM(t2.c2);
ERROR 1463 (42000): Non-grouping field 'c1' is used in HAVING clause
----------------------------------------------------------------------
miguel@tikal ~/dbs $ 5.6/bin/mysql -uroot -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.30-debug Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS t1, t2;
Query OK, 0 rows affected, 2 warnings (0,00 sec)

mysql>
mysql> SET @@sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0,00 sec)

mysql>
mysql> CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);

-- Two different tabled with same column name in HAVING
SELECT t1.c1, t2.c1
FROM t1, t2
GROUP BY t1.c1, t2.c1
HAVING t1.c1 > SUM(t1.c2) AND t2.c1 > SUM(t2.c2);Query OK, 0 rows affected (0,26 sec)

mysql> CREATE TABLE t2 (c1 INT, c2 INT);
Query OK, 0 rows affected (0,29 sec)

mysql>
mysql> -- Two different tabled with same column name in HAVING
mysql> SELECT t1.c1, t2.c1
    -> FROM t1, t2
    -> GROUP BY t1.c1, t2.c1
    -> HAVING t1.c1 > SUM(t1.c2) AND t2.c1 > SUM(t2.c2);
ERROR 1463 (42000): Non-grouping field 'c1' is used in HAVING clause
-------------------------------------------------------------------------------------
miguel@tikal ~/dbs $ 5.7/bin/mysql -uroot -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12-debug Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS t1, t2;
Query OK, 0 rows affected, 2 warnings (0,00 sec)

mysql>
mysql> SET @@sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql>
mysql> CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);

-- Two different tabled with same column name in HAVING
SELECT t1.c1, t2.c1
FROM t1, t2
GROUP BY t1.c1, t2.c1
HAVING t1.c1 > SUM(t1.c2) AND t2.c1 > SUM(t2.c2);Query OK, 0 rows affected (0,25 sec)

mysql> CREATE TABLE t2 (c1 INT, c2 INT);
Query OK, 0 rows affected (0,25 sec)

mysql>
mysql> -- Two different tabled with same column name in HAVING
mysql> SELECT t1.c1, t2.c1
    -> FROM t1, t2
    -> GROUP BY t1.c1, t2.c1
    -> HAVING t1.c1 > SUM(t1.c2) AND t2.c1 > SUM(t2.c2);
Empty set (0,01 sec)