MySQL Bugs: #17282: legal having clause being disallowd by mysql.

Bug #17282	legal having clause being disallowd by mysql.
Submitted:	9 Feb 2006 16:48	Modified:	2 Aug 2006 16:31
Reporter:	Dhruv Matani	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.13/4.1.19 BK	OS:	Linux (Suse GNU/Linux x86-64)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Hello,

I have this table:
mysql> explain tab2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| branch | varchar(40) | YES  | MUL | NULL    |       |
| id     | int(11)     | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

And this query fails:
mysql> select count(*) from tab2 group by branch having branch<>'mumbai' order by id desc,branch desc limit 100;
ERROR 1054 (42S22): Unknown column 'branch' in 'having clause'

Which I think should not fail. And apart from that, I think there are other problems when I use the HAVING clause.

Regards,
-Dhruv.

How to repeat:
Fire the query above, and it should fail.

Thank you for the bug report. This bug not affects 5.0:

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.19-debug-log

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

mysql> create table tab2 (branch varchar(40), id int);
Query OK, 0 rows affected (0.05 sec)

mysql> select count(*) from tab2 group by branch having
    -> branch<>'mumbai' order by id desc,branch desc limit 100;
ERROR 1054 (42S22): Unknown column 'branch' in 'having clause'
mysql> 

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-debug

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

mysql> create table tab2 (branch varchar(40), id int);
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from tab2 group by branch having branch<>'mumbai' order by id desc,branch desc limit 100;
Empty set (0.00 sec)

mysql>

Hello,
  Thanks... so I should upgrade to 5.0....

  Also, this query: mysql> select branch, count(*)/max(id) from tab2 group by branch having (branch<>'mumbai' OR count(*)<2) order by id desc,branch desc limit 100;

Should not compile, because I'm ordering by id, which is a nonsense field when I do a GROUP BY, because id is not present in the group by. However, it does????

Regards,
-Dhruv.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Your second query :
select branch, count(*)/max(id) from tab2 group by
branch having (branch<>'mumbai' OR count(*)<2) order by id desc,branch desc
limit 100;

should not cause an error because according to the Reference Manual section 13.2.7 :
"MySQL extends the use of GROUP BY to allow selecting fields that are not mentioned in the GROUP BY clause."
And since according to the SQL standard ORDER BY is for columns in the select list, including columns which could (temporarily, for purposes of sorting) be added to the select list by the implementation. So the rules for ORDER BY columns should be the same as the rules for select-list columns.

If and only if sql_mode=ONLY_FULL_GROUP_BY, your example statement
should be illegal because 'id' would be illegal in the select list.

However there are known deficiences in the implementation of the ONLY_FULL_GROUP_BY flag(bug #8510). I suggest you track bug #8510 for further information.

I will update http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html
to point out that the SELECT list extension also applies to ORDER BY columns,
as long as ONLY_FULL_GROUP_BY is not enabled.

Or,
u can do that:

select count(*), branch  from tab2 group by branch having branch<>'mumbai' order by id desc,branch desc limit 100;