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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.1.14-beta/5.0
• OS: Any
• CPU Architecture: Any
• Tags: complex terms, GROUP columns

[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] MySQL Verification Team

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>