inner select with HAVING results in WARNING 1292

Bug #1241097 reported by mgrennan on 2013-10-17
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.1
Won't Fix
Low
Unassigned
5.5
Triaged
Low
Unassigned
5.6
Triaged
Low
Unassigned
5.7
Triaged
Low
Unassigned

Bug Description

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.

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

tags: added: upstream

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.