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
Triage: Triaged: D3 (Medium)

[5 Sep 2010 17:32] Balazs Odor
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'
[5 Sep 2010 18:17] Valeriy Kravchuk
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'