Bug #38928 select ... count(*) not enforcing group by clause when obtaining other fields

• Submitted: 20 Aug 2008 21:58
• Modified: 20 Aug 2008 22:52
• Reporter: Jorge Urdaneta
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.0.67-community-nt
• OS: Windows (XP SP2)
• CPU Architecture: Any

[20 Aug 2008 21:58] Jorge Urdaneta

Description:
When selecting several fields and aggregation functions like count(*) it is expected that the user provide the group by clause. If not, mysql throws an error message as specified in: http://dev.mysql.com/doc/refman/5.0/en/counting-rows.html
In that mysql version it doesn't work as expected. It gives the total count and the values for the data fields of the first row.

I can't reproduce this issue in 5.0.51a-community on Linux. Looks like it is a windows only issue.

How to repeat:
create table pet ( id serial, name varchar(20), owner varchar(20)) ENGINE=INNODB;

insert into pet values(DEFAULT(id), 'boby', 'Bob');
insert into pet values(DEFAULT(id), 'Neron', 'Bob');
insert into pet values(DEFAULT(id), 'Bigone', 'Mary');
insert into pet values(DEFAULT(id), 'badone', 'Mary');
insert into pet values(DEFAULT(id), 'Rug', 'Mary');

select * from pet; /* should return 5 records */

select owner, count(*) from pet; /* should give you an error message */

[20 Aug 2008 22:52] MySQL Verification Team

Thank you for the bug report. That is expected behavior on MySQL if you want to change it please read the Manual sql_mode: 

http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

ONLY_FULL_GROUP_BY

how showed below:

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.70-nt-debug-log Source distribution

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

mysql 5.0 > create database h7;
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > use h7
Database changed
mysql 5.0 > set sql_mode = ONLY_FULL_GROUP_BY;
Query OK, 0 rows affected (0.05 sec)

mysql 5.0 > create table pet ( id serial, name varchar(20), owner varchar(20)) ENGINE=INNODB;
Query OK, 0 rows affected (0.22 sec)

mysql 5.0 >
mysql 5.0 > insert into pet values(DEFAULT(id), 'boby', 'Bob');
Query OK, 1 row affected (0.08 sec)

mysql 5.0 > insert into pet values(DEFAULT(id), 'Neron', 'Bob');
Query OK, 1 row affected (0.08 sec)

mysql 5.0 > insert into pet values(DEFAULT(id), 'Bigone', 'Mary');
Query OK, 1 row affected (0.06 sec)

mysql 5.0 > insert into pet values(DEFAULT(id), 'badone', 'Mary');
Query OK, 1 row affected (0.06 sec)

mysql 5.0 > insert into pet values(DEFAULT(id), 'Rug', 'Mary');
Query OK, 1 row affected (0.06 sec)

mysql 5.0 >
mysql 5.0 > select * from pet; /* should return 5 records */
+----+--------+-------+
| id | name   | owner |
+----+--------+-------+
|  1 | boby   | Bob   |
|  2 | Neron  | Bob   |
|  3 | Bigone | Mary  |
|  4 | badone | Mary  |
|  5 | Rug    | Mary  |
+----+--------+-------+
5 rows in set (0.01 sec)

mysql 5.0 >
mysql 5.0 > select owner, count(*) from pet; /* should give you an error message */
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
mysql 5.0 >