MySQL Bugs: #39656: Behaviour different for agg functions with & without where

Bug #39656	Behaviour different for agg functions with & without where - ONLY_FULL_GROUP_BY
Submitted:	26 Sep 2008 2:15	Modified:	8 Dec 2008 17:07
Reporter:	Kishor Grandhe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.0.67, 5.1.28	OS:	Any (Windows XP, Fedora 9)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	AGGREGATE FN WITH NO OTHER COLUMN, ONLY_FULL_GROUP_BY

Description:
SQL Mode = ONLY_FULL_GROUP_BY
   When we select a aggregate function with no other columns in the select clause and no where clause, the results are correct. But when we add an where clause it throws error 1140

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR
OUP columns is illegal if there is no GROUP BY clause.

 

How to repeat:
create table test (a int, b int);
insert into test (a,b) values (1,1);
insert into test (a,b) values (1,2);
insert into test (a,b) values (1,3);

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test where a=1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> set sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;

+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test where a=1;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR
OUP columns is illegal if there is no GROUP BY clause

Suggested fix:
Select queries with just Aggregate functions in the select list need not have group by clauses and having clauses.

Thank you for a bug report. Verified also with 5.1.28:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.28-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table test;
Query OK, 0 rows affected (0.20 sec)

mysql> create table test (a int, b int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test (a,b) values (1,1);
Query OK, 1 row affected (0.09 sec)

mysql> insert into test (a,b) values (1,2);
Query OK, 1 row affected (0.03 sec)

mysql> insert into test (a,b) values (1,3);
Query OK, 1 row affected (0.05 sec)

mysql> set sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.02 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.09 sec)

mysql> select count(*) from test where a=1;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR
OUP columns is illegal if there is no GROUP BY clause
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from test where a=1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.05 sec)

mysql> set sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.02 sec)

mysql> alter table test engine=MyISAM;
Query OK, 3 rows affected (0.25 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select count(*) from test where a=1;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR
OUP columns is illegal if there is no GROUP BY clause

So, this is NOT related to InnoDB only and is also a regression, as 4.1.22, for example, gives correct results:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3306 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 4.1.22-community-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table test;
ERROR 1051 (42S02): Unknown table 'test'
mysql> create table test (a int, b int);
Query OK, 0 rows affected (0.23 sec)

mysql> insert into test (a,b) values (1,1);
Query OK, 1 row affected (0.17 sec)

mysql> insert into test (a,b) values (1,2);
Query OK, 1 row affected (0.03 sec)

mysql> insert into test (a,b) values (1,3);
Query OK, 1 row affected (0.05 sec)

mysql> set sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.02 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/58662

2712 Georgi Kodinov	2008-11-13
      Bug #39656: Behaviour different for agg functions with & without where - ONLY_FULL_GROUP_BY
      
      The check for non-aggregated columns in queries with aggregate function, but without GROUP BY
      was treating all the parts of the query as if they are in the SELECT list.
      Fixed by ignoring the non-aggregated fields in the WHERE clause.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/59616

2712 Georgi Kodinov	2008-11-22
      Bug #39656: Behaviour different for agg functions with & without where -
      ONLY_FULL_GROUP_BY
      
      The check for non-aggregated columns in queries with aggregate function, but without
      GROUP BY was treating all the parts of the query as if they are in the SELECT list.
      Fixed by ignoring the non-aggregated fields in the WHERE clause.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/59687

2712 Georgi Kodinov	2008-11-24
      Bug #39656: Behaviour different for agg functions with & without where -
      ONLY_FULL_GROUP_BY
      
      The check for non-aggregated columns in queries with aggregate function, but without
      GROUP BY was treating all the parts of the query as if they are in the SELECT list.
      Fixed by ignoring the non-aggregated fields in the WHERE clause.

Pushed into 5.0.74  (revid:kgeorge@mysql.com-20081124153024-1kw3ar1egzfzwsaq) (version source revid:kgeorge@mysql.com-20081124153829-i9pgozti2sp3ivze) (pib:5)

Noted in 5.0.74 changelog.

With the ONLY_FULL_GROUP_BY SQL mode enabled, the check for
non-aggregated columns in queries with aggregate functions, but
without a GROUP BY clause was treating all the parts of the query as
if they were in the select list. This is fixed by ignoring the
non-aggregated columns in the WHERE clause. 

Resetting report to NDI pending push into 5.1.x, 6.0.x.

Pushed into 5.1.31  (revid:kgeorge@mysql.com-20081124153024-1kw3ar1egzfzwsaq) (version source revid:patrick.crews@sun.com-20081126180318-v685u61mpgoc176x) (pib:5)

Pushed into 6.0.9-alpha  (revid:kgeorge@mysql.com-20081124153024-1kw3ar1egzfzwsaq) (version source revid:satya.bn@sun.com-20081126062231-h6os2axygjw27wb4) (pib:5)

Noted in 5.1.31, 6.0.9 changelogs.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)