Bug #42567 Invalid GROUP BY error
Submitted: 3 Feb 2009 12:39 Modified: 18 Mar 2009 14:58
Reporter: Boyd Pappot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.1.30-community-log OS:Windows (2003)
Assigned to: Bernt Marius Johnsen CPU Architecture:Any
Tags: regression

[3 Feb 2009 12:39] Boyd Pappot 
Description:
When using "ONLY_FULL_GROUP_BY", the following query generates an error:

select count(*) from information_schema.statistics
where information_schema.statistics.table_schema = database();

This is triggered using a where statement, without the where clause the query works as expected.

The error thrown is not even correct:

Mixing of GROUP columns .. with no GROUP columns

while there aren't any "no GROUP" columns selected

How to repeat:
Server version: 5.1.30-community-log MySQL Community Server (GPL)

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

mysql> use test;
Database changed
mysql> create table t1(number int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t1 values (1), (10);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select count(1) from t1;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from t1 where number > 5;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
[3 Feb 2009 13:16] Valeriy Kravchuk 
Verified just as described:

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 9
Server version: 5.1.30-community-log MySQL Community Server (GPL)

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

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

mysql> drop table t1;
Query OK, 0 rows affected (0.09 sec)

mysql> create table t1(number int);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1 values(1), (10);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select count(*) from t1 where number > 5;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.05 sec)

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

mysql> select count(*) from t1 where number > 5;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR
OUP columns is illegal if there is no GROUP BY clause
[3 Feb 2009 13:19] Valeriy Kravchuk 
5.0.74 works OK, so this is regression:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)

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

mysql> drop table t1;
Query OK, 0 rows affected (1.56 sec)

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

mysql> create table t1(number int);
Query OK, 0 rows affected (0.25 sec)

mysql> insert into t1 values(1), (10);
Query OK, 2 rows affected (0.20 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select count(*) from t1 where number > 5;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.08 sec)

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

mysql> select count(*) from t1 where number > 5;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)
[3 Feb 2009 13:21] Valeriy Kravchuk 
6.0.9 also works as expected.
[3 Feb 2009 13:23] Boyd Pappot 
5.1.22 works as expected.
[3 Feb 2009 13:32] Valeriy Kravchuk 
Looks like it is fixed somehow in current 5.1.32 from bzr though:

openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test
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.1.32-debug Source distribution

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

mysql> drop table t1;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t1 (number int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values (1), (10);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select count(*) from t1 where number > 5;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

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

mysql> select count(*) from t1 where number > 5;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)
[26 Feb 2009 14:42] 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/67690

2824 Bernt M. Johnsen	2009-02-26
      BUG#42567: Added a testcase to group_by.test and a comment in func_group.test of a possible future test problem
[26 Feb 2009 17:23] 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/67707

2825 Bernt M. Johnsen	2009-02-26
      Prepared for push (BUG#42567)
[13 Mar 2009 19:02] Bugs System 
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:staale.smedseng@sun.com-20090227160332-3k1kc0rao6y07cbp) (merge vers: 5.1.33) (pib:6)
[15 Mar 2009 2:41] Paul DuBois 
Noted in 5.1.33 changelog.

With the ONLY_FULL_GROUP_BY SQL mode enabled, some legal queries
failed. 

Setting report to NDI pending push into 6.0.x.
[18 Mar 2009 13:17] Bugs System 
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:bernt.johnsen@sun.com-20090226181205-by1jwatx1qvxp8ts) (merge vers: 6.0.10-alpha) (pib:6)
[18 Mar 2009 14:58] Paul DuBois 
Noted in 6.0.11 changelog.
[9 May 2009 16:39] Bugs System 
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:36] Bugs System 
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:34] Bugs System 
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)