MySQL Bugs: #31794: no syntax error on SELECT id FROM t HAVING count(*)>2;

Bug #31794	no syntax error on SELECT id FROM t HAVING count(*)>2;
Submitted:	23 Oct 2007 18:03	Modified:	29 Nov 2007 0:47
Reporter:	Grigory Rubtsov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.20-beta	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Implicit mixing of GROUP BY columns and no GROUP columns is not triggered. No error on query:
SELECT id FROM t HAVING count(*)>2;

How to repeat:
mysql> CREATE TABLE t (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES (1),(2),(3),(4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT id FROM t HAVING count(*)>2;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

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

mysql> show variables like "%sql_mode%";
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| sql_mode      | ONLY_FULL_GROUP_BY | 
+---------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT id FROM t HAVING count(*)>2;
+------+
| id   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

mysql>

Thank you for the bug report.

Data instead of error => P2

Quoting Peter Gulutzan:

The original complaint is "not a bug" -- the complainer
didn't say sql_mode=only_full_group_by.

But Miguel did use sql_mode=only_full_group_by.

So: Yes, there is a bug. There is an implicit grouping,
but no grouping columns, so "id" should not be allowed
when sql_mode=only_full_group_by.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36736

ChangeSet@1.2558, 2007-10-31 11:54:45+02:00, gkodinov@magare.gmz +3 -0
  Bug #31794: no syntax error on SELECT id FROM t HAVING count(*)>2
  
  The HAVING clause is subject to the same rules as the SELECT list
  about using aggregated and non-aggregated columns.
  But this was not enforced when processing implicit grouping from
  using aggregate functions.
  Fixed by performing the same checks for HAVING as for SELECT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36903

ChangeSet@1.2558, 2007-11-01 18:36:24+02:00, gkodinov@magare.gmz +3 -0
  Bug #31794: no syntax error on SELECT id FROM t HAVING count(*)>2
  
  The HAVING clause is subject to the same rules as the SELECT list
  about using aggregated and non-aggregated columns.
  But this was not enforced when processing implicit grouping from
  using aggregate functions.
  Fixed by performing the same checks for HAVING as for SELECT.

Pushed into 5.0.52

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

With ONLY_FULL_GROUP_BY SQL mode enabled, queries such as SELECT a
FROM t1 HAVING COUNT(*)>2 were not being rejected as they should have
been.

Addition to changelog entry:

This bugfix results in the following behavior:

- There is a check against mixing group and non-group columns *only*
if ONLY_FULL_GROUP_BY is enabled.

- This check is done both for the select list and for the HAVING clause
if there is one.

This behavior differs from previous versions as follows:

- Previously, the HAVING clause was not checked when ONLY_FULL_GROUP_BY
was enabled; now it is checked.

- Previously, the select list was checked even when ONLY_FULL_GROUP_BY
was not enabled; now it is checked only when ONLY_FULL_GROUP_BY is
enabled.