Bug #29421 MySQL allows mixing of GROUP columns with no GROUP columns in complex terms
Submitted: 28 Jun 2007 13:43 Modified: 28 Jun 2007 18:56
Reporter: Szymon Nowakowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1.14-beta/5.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: complex terms, GROUP columns
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[28 Jun 2007 13:43] Szymon Nowakowski
Description:
If you have a table
create table tab (x int);

then when you run a query 
select x, max(x) from tab
you get a following error: ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

You should get the same error for a following type of query:
select x + max(x) from tab

but you don't.

How to repeat:
1. execute the following 2 queries:
  create table tab (x int);
  select x + max(x) from tab;
2. The queries get executed BUT an error should be raised

Suggested fix:
In case of all complex expressions mixing GROUP columns and no GROUP colums without a GROUP BY you should generate the same error:

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
[28 Jun 2007 18:56] Miguel Solorzano
Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.21-beta-debug-log Source distribution

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

mysql> create table tab (x int);
Query OK, 0 rows affected (0.09 sec)

mysql>   select x + max(x) from tab;
+------------+
| x + max(x) |
+------------+
|       NULL | 
+------------+
1 row in set (0.11 sec)

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