| Bug #70684 | HAVING referring to subquery results in WARNING 1292 | ||
|---|---|---|---|
| Submitted: | 22 Oct 2013 8:13 | Modified: | 22 Oct 2013 11:33 |
| Reporter: | Valeriy Kravchuk | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.5.33, 5.6.14 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | having, regression, subquery | ||
[22 Oct 2013 11:33]
MySQL Verification Team
Thank you for the bug report. 5.0 affected too.
[24 Oct 2013 12:20]
MySQL Verification Team
Tested on 5.1:
C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.73-Win X64 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.1 > CREATE DATABASE DX;
Query OK, 1 row affected (0.01 sec)
mysql 5.1 > USE DX
Database changed
mysql 5.1 > CREATE TABLE `test` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `data` varchar(20) DEFAULT NULL,
-> KEY `id` (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)
mysql 5.1 >
mysql 5.1 > insert into test (data) values ('Some data'), (NULL);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.1 >
mysql 5.1 > SELECT data, (IF(ISNULL(data) , (SELECT "FOO"), "BAR")) as synth_data FROM test HAVING (synth_data IS NOT NULL);
+-----------+------------+
| data | synth_data |
+-----------+------------+
| Some data | BAR |
| NULL | FOO |
+-----------+------------+
2 rows in set, 2 warnings (0.00 sec)
mysql 5.1 > show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1292
Message: Truncated incorrect INTEGER value: 'BAR'
*************************** 2. row ***************************
Level: Warning
Code: 1292
Message: Truncated incorrect INTEGER value: 'FOO'
2 rows in set (0.00 sec)
mysql 5.1 > SELECT data, (IF(ISNULL(data) , (SELECT "FOO"), "BAR")) as synth_data FROM test HAVING (convert(synth_data, char) IS NOT NULL);
+-----------+------------+
| data | synth_data |
+-----------+------------+
| Some data | BAR |
| NULL | FOO |
+-----------+------------+
2 rows in set (0.00 sec)
mysql 5.1 >
Description: A statement with an inner SELECT and a HAVING clause referring to it results in WARNING 1292 for the reasons that are not clear: C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3314 test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 51 Server version: 5.6.14-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop table test; Query OK, 0 rows affected (4.76 sec) mysql> CREATE TABLE `test` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `data` varchar(20) DEFAULT NULL, -> KEY `id` (`id`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (1.95 sec) mysql> insert into test (data) values ('Some data'), (NULL); Query OK, 2 rows affected (0.57 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test; +----+-----------+ | id | data | +----+-----------+ | 1 | Some data | | 2 | NULL | +----+-----------+ 2 rows in set (0.05 sec) mysql> SELECT data, (IF(ISNULL(data) , (SELECT "FOO"), "BAR")) as synth_data FRO M test.test HAVING (synth_data IS NOT NULL); +-----------+------------+ | data | synth_data | +-----------+------------+ | Some data | BAR | | NULL | FOO | +-----------+------------+ 2 rows in set, 2 warnings (0.13 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1292 Message: Truncated incorrect INTEGER value: 'BAR' *************************** 2. row *************************** Level: Warning Code: 1292 Message: Truncated incorrect INTEGER value: 'FOO' 2 rows in set (0.00 sec) Why HAVING assumes that the result should be INTEGER is not clear at all. Workaround is to use explicit cast to CHAR: mysql> SELECT data, (IF(ISNULL(data) , (SELECT "FOO"), "BAR")) as synth_data FRO M test.test HAVING (convert(synth_data, char) IS NOT NULL); +-----------+------------+ | data | synth_data | +-----------+------------+ | Some data | BAR | | NULL | FOO | +-----------+------------+ 2 rows in set (0.02 sec) Originally reported for Percona Server 5.5 as https://bugs.launchpad.net/percona-server/+bug/1241097. Also repeatable with 5.5.33, but was it always like this? How to repeat: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `data` varchar(20) DEFAULT NULL, KEY `id` (`id`) ) ENGINE=InnoDB; insert into test (data) values ('Some data'), (NULL); SELECT data, (IF(ISNULL(data) , (SELECT "FOO"), "BAR")) as synth_data FROM test HAVING (synth_data IS NOT NULL); show warnings\G SELECT data, (IF(ISNULL(data) , (SELECT "FOO"), "BAR")) as synth_data FROM test HAVING (convert(synth_data, char) IS NOT NULL); Suggested fix: Data type for the "derived" column should be based on the one from subquery (if not data column) in the case above?