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: | |
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
[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.