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

[23 Oct 2007 18:03] Grigory Rubtsov
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)
[23 Oct 2007 21:32] Miguel Solorzano
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>
[23 Oct 2007 21:44] Miguel Solorzano
Thank you for the bug report.
[29 Oct 2007 14:01] Timour Katchaounov
Data instead of error => P2
[31 Oct 2007 9:32] Georgi Kodinov
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.
[31 Oct 2007 9:55] Bugs System
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.
[1 Nov 2007 16:36] Bugs System
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.
[16 Nov 2007 9:30] Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:33] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:35] Bugs System
Pushed into 6.0.4-alpha
[29 Nov 2007 0:47] Paul Dubois
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.
[1 May 2008 13:48] Paul Dubois
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.