MySQL Bugs: #48150: Quoted aliases are not recognized in HAVING clause

Bug #48150	Quoted aliases are not recognized in HAVING clause
Submitted:	19 Oct 2009 14:06	Modified:	19 Feb 2013 13:49
Reporter:	Michael Skulsky	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.39, 5.1.41, 5.1.47	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
A HAVING clause containing a quoted alias, wrapped in a function call, produces an error in 5.1. This wasn't so in 5.0, all 3 queries from "How-to-repeat" section below work OK in 5.0.

How to repeat:
mysql> create table test_tbl (a int);
Query OK, 0 rows affected (0.00 sec)

This query produces OK result:

mysql> select a, count(*) as cnt from test_tbl group by a having `cnt`>0;
Empty set (0.00 sec)

This is OK too:

mysql> select a, count(*) as cnt from test_tbl group by a having acos(cnt)>0;
Empty set (0.00 sec)

And this is a bug:

mysql> select a, count(*) as cnt from test_tbl group by a having acos(`cnt`)>0;
ERROR 1054 (42S22): Unknown column 'cnt' in 'having clause'

Verified just as described with recent 5.1.41 from bzr on Mac OS X also:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.41-debug |
+--------------+
1 row in set (0.00 sec)

mysql> create table test_tbl (a int);
Query OK, 0 rows affected (0.40 sec)

mysql> select a, count(*) as cnt from test_tbl group by a having `cnt`>0;
Empty set (0.00 sec)

mysql> select a, count(*) as cnt from test_tbl group by a having acos(cnt)>0;
Empty set (0.00 sec)

mysql> select a, count(*) as cnt from test_tbl group by a having acos(`cnt`)>0;
ERROR 1054 (42S22): Unknown column 'cnt' in 'having clause'
mysql> exit
Bye
77-52-242-160:5.1 openxs$ bin/mysqladmin -uroot -pmysql shutdown
091019 17:09:37 mysqld_safe mysqld from pid file /Users/openxs/dbs/5.1/var/77-52-231-245.dialup.umc.net.ua.pid ended
[1]+  Done                    bin/mysqld_safe
77-52-242-160:5.1 openxs$ cd ../5.0
77-52-242-160:5.0 openxs$ bin/mysqld_safe &
[1] 45880
77-52-242-160:5.0 openxs$ chown: /Users/openxs/dbs/5.0/var/77-52-209-190.dialup.umc.net.ua.err: Operation not permitted
Starting mysqld daemon with databases from /Users/openxs/dbs/5.0/var

77-52-242-160:5.0 openxs$ bin/mysql -uroot test
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
Server version: 5.0.88-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test_tbl (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> select a, count(*) as cnt from test_tbl group by a having acos(`cnt`)>0;
Empty set (0.01 sec)

miguel@txg:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.87-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test_tbl (a int);
Query OK, 0 rows affected (0.12 sec)

mysql>  select a, count(*) as cnt from test_tbl group by a having acos(`cnt`)>0;
Empty set (0.00 sec)

mysql>

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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test_tbl (a int);
Query OK, 0 rows affected (0.07 sec)

mysql>  select a, count(*) as cnt from test_tbl group by a having acos(`cnt`)>0;
ERROR 1054 (42S22): Unknown column 'cnt' in 'having clause'
mysql>

Using 'single quotes' and not `backquotes` in the HAVING-clause does not repeat the problem:

These 
SELECT a, COUNT(*) AS cnt FROM test_tbl GROUP BY a HAVING ACOS('cnt')>0;
SELECT a, COUNT(*) AS `cnt` FROM test_tbl GROUP BY a HAVING ACOS('cnt')>0;
SELECT a, COUNT(*) AS 'cnt' FROM test_tbl GROUP BY a HAVING ACOS('cnt')>0;
.. all work.

But not only in HAVING-clause it is a problem:

works:
SELECT a, COUNT(*) AS cnt FROM test_tbl WHERE 'cnt'>0;

does not work:
SELECT a, COUNT(*) AS cnt FROM test_tbl WHERE `cnt`>0;
-- Error Code : 1054
-- Unknown column 'cnt' in 'where clause'

Documentation states that an alias may be quoted as a string or as an identifier.

Please forget my post.  Alias do not apply in WHERE.

Minimal test case:

CREATE TABLE t1 (c INT);
SELECT 1 AS a FROM t1 HAVING ISNULL(a);
SELECT 1 AS a FROM t1 HAVING ISNULL(`a`);

A fix:

=== modified file 'sql/sql_yacc.yy'
--- old/sql/sql_yacc.yy 2010-03-14 16:01:45 +0000
+++ new/sql/sql_yacc.yy 2010-03-15 21:10:19 +0000
@@ -8132,7 +8132,8 @@ udf_expr:
                parse it out. If we hijack the input stream with
                remember_name we may get quoted or escaped names.
             */
-            else if ($2->type() != Item::FIELD_ITEM)
+            else if ($2->type() != Item::FIELD_ITEM &&
+                     $2->type() != Item::REF_ITEM)
               $2->set_name($1, (uint) ($3 - $1), YYTHD->charset());
             $$= $2;
           }

Also see [15 Mar 20:14], Markus Wolff's commentary at the bug #40825 page.

I.e. this bug #48150 also affects *views* those have or *don't* have any quotations in their HAVING condition in a weird way:

we transform

  CREATE VIEW v1 AS SELECT 1 AS A FROM t1 HAVING A IS NULL

to

  CREATE VIEW v1 AS SELECT 1 AS `A` FROM `test`.`t1` HAVING ISNULL(`A`)
                                                            ^^^^^^^^^^^
for .frm file. 

Then we are unable to load this statement (i.e. find Item_ref field with a name "`A`"), so any SELECT from this view fails with an error:

  1356: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Bug #54958 was marked as a duplicate of this one.

this is still an issue on 5.5.16

A fix for this will be in the upcoming 5.6.11 and 5.7.1 releases.