Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.1.67, 5.2.14, 5.3.12, 5.5(EOL), 10.0(EOL)
-
12.04.5 LTS (GNU/Linux 3.13.0-40-generic x86_64) - replacement of MySQL 5.6
14.04.1 LTS (GNU/Linux 3.13.0-40-generic x86_64) - clean install
Description
Error occurs when using backticks in HAVING clause with function (e.g. UPPER, CONCAT, DATE_FORMAT, etc...).
Views are afected by this behavior - forcing backticks.
How to simulate
CREATE TABLE `articles` (
|
`id` int(11) NOT NULL,
|
`title` varchar(45) DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
INSERT INTO `articles` VALUES ('1', 'Judge delays Oscar Pistorius ruling');
|
INSERT INTO `articles` VALUES ('2', 'Warner dedicates century to Hughes');
|
INSERT INTO `articles` VALUES ('3', 'Torture report: Battle lines being drawn');
|
SELECT `id`, SHA1(`title`) AS `column_1`
|
FROM `articles`
|
HAVING `column_1` LIKE '5%';
|
|
-- 1 row(s) returned
|
SELECT `id`, SHA1(`title`) AS `column_1`
|
FROM `articles`
|
HAVING UPPER(column_1) LIKE '5%';
|
|
-- 1 row(s) returned
|
SELECT `id`, SHA1(`title`) AS `column_1`
|
FROM `articles`
|
HAVING UPPER(`column_1`) LIKE '5%';
|
|
-- Error Code: 1054. Unknown column 'column_1' in 'having clause'
|
Works well on:
- MySQL 5.5 - clean install,
- MySQL 5.6 - upgrade from MySQL 5.5
- MySQL 5.6 - clean install (sql_mode=NO_ENGINE_SUBSTITUTION)
Throws an error code with unknown column:
- MariaDB 10.0.15 - clean install
- MariaDB 10.0.15 - replacement of MySQL 5.6 (sql_mode=NO_ENGINE_SUBSTITUTION)