MySQL Bugs: #80455: Non-grouping field error with tables that have the same column names

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

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

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)