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

[26 Sep 2008 2:15] Kishor Grandhe
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.
[26 Sep 2008 4:18] Valeriy Kravchuk
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)
[13 Nov 2008 15:39] Bugs System
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.
[22 Nov 2008 11:40] Bugs System
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.
[24 Nov 2008 15:31] Bugs System
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.
[2 Dec 2008 13:00] Bugs System
Pushed into 5.0.74  (revid:kgeorge@mysql.com-20081124153024-1kw3ar1egzfzwsaq) (version source revid:kgeorge@mysql.com-20081124153829-i9pgozti2sp3ivze) (pib:5)
[3 Dec 2008 2:42] Paul DuBois
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.
[8 Dec 2008 10:20] Bugs System
Pushed into 5.1.31  (revid:kgeorge@mysql.com-20081124153024-1kw3ar1egzfzwsaq) (version source revid:patrick.crews@sun.com-20081126180318-v685u61mpgoc176x) (pib:5)
[8 Dec 2008 11:31] Bugs System
Pushed into 6.0.9-alpha  (revid:kgeorge@mysql.com-20081124153024-1kw3ar1egzfzwsaq) (version source revid:satya.bn@sun.com-20081126062231-h6os2axygjw27wb4) (pib:5)
[8 Dec 2008 17:07] Paul DuBois
Noted in 5.1.31, 6.0.9 changelogs.
[19 Jan 2009 11:21] Bugs System
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)
[19 Jan 2009 12:59] Bugs System
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)
[19 Jan 2009 16:05] Bugs System
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)