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: | |
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
[21 Feb 2016 21:58]
MySQL Verification Team
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)