Bug #45809 Use "COUNT()" without "GROUP BY" has no error message
Submitted: 28 Jun 2009 15:18 Modified: 22 Aug 2009 16:50
Reporter: Ender Li Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.35-community,5.1.36-community OS:Windows (XP Pro)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: "COUNT()", "GROUP BY", error message, without

[28 Jun 2009 15:18] Ender Li
Description:
I do everything follow "MySQL 5.1 Reference Manual :: 3 Tutorial".
In "3.3.4.8. Counting Rows",

Note the use of GROUP BY to group all records for each owner. Without it, all you get is an error message: 
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause

I did not get the same error message(ERROR 1140) when I exec "SELECT owner, COUNT(*) FROM pet;".
I got this:
mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Harold |        9 |
+--------+----------+
1 row in set (0.00 sec)

How to repeat:
mysql> CREATE DATABASE menagerie;
Query OK, 1 row affected (0.03 sec)

mysql> USE menagerie
Database changed
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.16 sec)
mysql> LOAD DATA LOCAL INFILE 'c:/pet.txt' INTO TABLE pet;
Query OK, 8 rows affected (0.03 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Harold |        9 |
+--------+----------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
Maybe fix MySQL5.1.35, or modify "MySQL 5.1 Reference Manual".
Just make them "do" and "say" the same.
[28 Jun 2009 15:49] Peter Laursen
Count(*) is an exception. It can be used with no GROUP BY. It is also documented.

So this 

"SELECT owner, COUNT(*) FROM pet;"
.. is a valid (but useless) statement.  However try

"SELECT owner, COUNT(owner) FROM pet;" -- error
and 
"SELECT owner, COUNT(owner) FROM pet GROUP BY owner;" -- correct

Peter
(not a MySQL person)
[28 Jun 2009 17:49] Valeriy Kravchuk
The result will actuallt depend on SQL mode. Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html. 

Look:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.37-debug | 
+--------------+
1 row in set (0.00 sec)

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

mysql> create table tg(c1 int, c2 int);
Query OK, 0 rows affected (0.39 sec)

mysql> insert into tg values(1,1), (1,2), (2,1);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql> set session 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.01 sec)

mysql> select c1, count(*) from tg;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Also, COUNT is not exception. Any aggregate function will be allowed with empty sql_mode:

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

mysql> select c1, max(c2) from tg;
+------+---------+
| c1   | max(c2) |
+------+---------+
|    1 |       2 | 
+------+---------+
1 row in set (0.02 sec)

Read also http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html
[3 Jul 2009 8:51] Ender Li
Who can modify the reference(http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html) if there are no bugs in MySQL Server?

I test 5.1.36-community, give me no error. 
But in the reference(Document generated on: 2009-07-03 (revision: 15568) ), it said, "Without it, all you get is an error message".
[3 Jul 2009 9:07] Valeriy Kravchuk
Indeed, http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html should explain how sql_mode may influence the result.
[22 Aug 2009 16:50] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Revised discussion to mention the effect of ONLY_FULL_GROUP_BY.