Bug #70684 HAVING referring to subquery results in WARNING 1292

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?

Thank you for the bug report. 5.0 affected too.

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 >