MySQL Bugs: #76353: "Truncated incorrect INTEGER value" in INSERT...SELECT with CASE tested for NULL

Bug #76353	"Truncated incorrect INTEGER value" in INSERT...SELECT with CASE tested for NULL
Submitted:	17 Mar 2015 14:55	Modified:	17 Mar 2015 18:16
Reporter:	Bernhard Haumacher	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi, 

trying to insert the result of one query into another table. The query contains a WHERE expression of the form "NOT (CASE WHEN ... THEN ... ELSE ... END) IS NULL".

The SELECT query works well, when executed for its own. But when trying to insert the result into another table, I get the error "Error Code: 1292. Truncated incorrect INTEGER value: 'x'".

It's possible that this is related to Bug http://bugs.mysql.com/bug.php?id=43437, where a query produces a similar warning for unknown reasons - but here, the INSERT completely fails.

The problem occurs in MySQL version 5.6, while version 5.1 works fine.

My environment:

> SHOW VARIABLES LIKE '%version%';
# innodb_version, 5.6.22
# protocol_version, 10
# version, 5.6.22-log
# version_comment, MySQL Community Server (GPL)
# version_compile_machine, x86_64
# version_compile_os, Win64

Best regards
Bernhard Haumacher

How to repeat:
#
# Setup
#

# Base table
CREATE TABLE `a` (
  `id` varchar(70) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `a1` varchar(150) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# Association table
CREATE TABLE `ab` (
  `src` varchar(70) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `dst` varchar(70) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  PRIMARY KEY (`src`, `dst`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# Result table
CREATE TABLE `storage` (
  `a` varchar(70) DEFAULT NULL,
  `b` varchar(70) DEFAULT NULL,
  PRIMARY KEY `storage_PKEY` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# Test data
INSERT INTO `a` VALUES ('x', 'foo');
INSERT INTO `a` VALUES ('y', 'bar');
INSERT INTO `ab` VALUES ('x', 'z1');
INSERT INTO `ab` VALUES ('y', 'z2');

# Select alone
SELECT
  t1.id `a`,
  (CASE WHEN t1.a1 = 'foo' THEN t1.`id` ELSE t2.`dst` END) `b`
FROM `a` t1
LEFT OUTER JOIN `ab` t2
ON t1.id = t2.src
WHERE
  NOT (
    (CASE WHEN t1.a1 = 'foo' THEN t1.`id` ELSE t2.`dst` END) 
    IS NULL);

# SELECT above yields the result
#
#  a    b
# ---------
# 'x', 'x'
# 'y', 'z2'

# Trying to insert this into table `storage` fails:
INSERT INTO `storage` (`a`, `b`)
INSERT INTO `storage` (`a`, `b`)
SELECT
  t1.id `a`,
  (CASE WHEN t1.a1 = 'foo' THEN t1.`id` ELSE t2.`dst` END) `b`
FROM `a` t1
LEFT OUTER JOIN `ab` t2
ON t1.id = t2.src
WHERE
  NOT (
    (CASE WHEN t1.a1 = 'foo' THEN t1.`id` ELSE t2.`dst` END) 
    IS NULL);

# Error Code: 1292. Truncated incorrect INTEGER value: 'x'

# When casting the CASE...WHEN in the WHERE to BINARY (before testing 
# for NULL), the query works well - but this isn't a feature, is it?

# Works, but looks strange - casting before checking for NULL:
INSERT INTO `storage` (`a`, `b`)
SELECT
  t1.id `a`,
  (CASE WHEN t1.a1 = 'foo' THEN t1.`id` ELSE t2.`dst` END) `b`
FROM `a` t1
LEFT OUTER JOIN `ab` t2
ON t1.id = t2.src
WHERE
  NOT (
    CAST(
        (CASE WHEN t1.a1 = 'foo' THEN t1.`id` ELSE t2.`dst` END) 
        AS BINARY
    ) IS NULL);

Thank you for the bug report. Looks duplicate of bug you have mentioned the difference is you maybe have a sql_mode which converts the warnings in error see below:

mysql 5.6 > INSERT INTO `storage` (`a`, `b`)
    -> SELECT
    ->   t1.id `a`,
    ->   (CASE WHEN t1.a1 = 'foo' THEN t1.`id` ELSE t2.`dst` END) `b`
    -> FROM `a` t1
    -> LEFT OUTER JOIN `ab` t2
    -> ON t1.id = t2.src
    -> WHERE
    ->   NOT (
    ->     (CASE WHEN t1.a1 = 'foo' THEN t1.`id` ELSE t2.`dst` END)
    ->     IS NULL);
ERROR 1292 (22007): Truncated incorrect INTEGER value: 'x'
mysql 5.6 > SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 > INSERT INTO `storage` (`a`, `b`)
    -> SELECT
    ->   t1.id `a`,
    ->   (CASE WHEN t1.a1 = 'foo' THEN t1.`id` ELSE t2.`dst` END) `b`
    -> FROM `a` t1
    -> LEFT OUTER JOIN `ab` t2
    -> ON t1.id = t2.src
    -> WHERE
    ->   NOT (
    ->     (CASE WHEN t1.a1 = 'foo' THEN t1.`id` ELSE t2.`dst` END)
    ->     IS NULL);
Query OK, 2 rows affected, 2 warnings (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 2

Yes, you are right. When setting sql_mode to empty, the statement completes with as many warnings, as rows are inserted. At least, the inserted result is correct.

Not a feature anyway, isn't it?

And what's not mentioned/not correct/different to http://bugs.mysql.com/bug.php?id=43437 is that its a regression in 5.6, since everything worked fine in 5.1. #43437 states that it also happens in 5.0 and 5.1!

duplicate of http://bugs.mysql.com/bug.php?id=43437.