MySQL Bugs: #56572: ERROR 1054: Unknown column when using a function

Bug #56572	ERROR 1054: Unknown column when using a function
Submitted:	5 Sep 2010 17:32	Modified:	10 Sep 2010 21:49
Reporter:	Balazs Odor	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.1.49, 5.5.7-m3-bzr	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
ERROR 1054 when using a function with column alias

How to repeat:
mysql> CREATE TABLE `test` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, `fld` VARCHAR(255));

mysql> INSERT INTO `test` (`fld`) VALUES ('value1'), ('value2'), ('value1'), (NULL);

mysql> SELECT * FROM `test`;
+----+--------+
| id | fld    |
+----+--------+
|  1 | value1 |
|  2 | value2 |
|  3 | value1 |
|  4 | NULL   |
+----+--------+
4 rows in set (0.00 sec)

mysql> SELECT LENGTH(`fld`) AS `fld_length` FROM `test` GROUP BY `fld_length`;
+------------+
| fld_length |
+------------+
|       NULL |
|          6 |
+------------+
2 rows in set (0.02 sec)

mysql> SELECT LENGTH(`fld`) AS `fld_length` FROM `test` GROUP BY `fld_length` HAVING `fld_length` IS NOT NULL;
+------------+
| fld_length |
+------------+
|          6 |
+------------+
1 row in set (0.01 sec)

mysql> SELECT LENGTH(`fld`) AS `fld_length` FROM `test` GROUP BY `fld_length` HAVING `fld_length` IS NOT NULL AND LENGTH(`fld_length`) > 0;
ERROR 1054 (42S22): Unknown column 'fld_length' in 'having clause'

Thank you for the bug report. Verified with current mysql-5.5 on Mac OS X also:

macbook-pro:5.1 openxs$ bin/mysql -uroot testReading 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 6
Server version: 5.5.7-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> CREATE TABLE `test` (`id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, `fld` VARCHAR(255));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO `test` (`fld`) VALUES ('value1'), ('value2'), ('value1'), (NULL);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT LENGTH(`fld`) AS `fld_length` FROM `test` GROUP BY `fld_length`;
+------------+
| fld_length |
+------------+
|       NULL |
|          6 |
+------------+
2 rows in set (0.05 sec)

mysql>  SELECT LENGTH(`fld`) AS `fld_length` FROM `test` GROUP BY `fld_length` HAVING
    -> `fld_length` IS NOT NULL;
+------------+
| fld_length |
+------------+
|          6 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(`fld`) AS `fld_length` FROM `test` GROUP BY `fld_length` HAVING
    -> `fld_length` IS NOT NULL AND LENGTH(`fld_length`) > 0;
ERROR 1054 (42S22): Unknown column 'fld_length' in 'having clause'