inner select with HAVING results in WARNING 1292

[mgrennan]

A statement with an inner SELECT and a HAVING clause results in an WARING 1292.

Example:
mysql> select version();
+-----------------+
| version() |
+-----------------+
| 5.5.33-31.1-log |
+-----------------+
1 row in set (0.00 sec)

mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(20) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from test;
+----+-----------+
| id | data |
+----+-----------+
| 1 | Some data |
| 2 | NULL |
+----+-----------+
2 rows in set (0.00 sec)

mysql> SELECT data, (IF(ISNULL(data) , (SELECT "FOO"), "BAR")) as synth_data FROM test.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> show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'BAR' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'FOO' |
+---------+------+------------------------------------------+
2 rows in set (0.00 sec)

This works in all other versions tested without a warning. By casting the HAVING (CONVERT(synth_data, CHAR) IS NOT NULL) this works find.

[Valerii Kravchuk (valerii-kravchuk)]

I've reported upstream http://bugs.mysql.com/bug.php?id=70684 for this.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2047