MySQL Bugs: #67800: INSERT...SELECT WHERE clause "Incorrect integer value" with multi-col unique key

Bug #67800	INSERT...SELECT WHERE clause "Incorrect integer value" with multi-col unique key
Submitted:	4 Dec 2012 7:23	Modified:	13 Feb 2018 23:02
Reporter:	Name Family Name	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.27-log	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
A table has a multiple-column unique key.
I use "INSERT ... SELECT" to copy some data form this table to another, with some uncertain conditions in WHERE clause.
There is no syntax error in the SQL statment, and it runs well most of the time.
But it will causes an error "Error Code: 1366. Incorrect integer value" when there is a conditions like "WHERE `INT_field` = 'CHAR_value'".

A workaround is to use type conversion or other functions, like this:
"WHERE `INT_field` = CONCAT('CHAR_value')".

How to repeat:
CREATE DATABASE `bug_test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `bug_test`;

-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `t1` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- t2 has a multiple-column unique key
CREATE TABLE IF NOT EXISTS `t2` (
  `u1` int(11) NOT NULL,
  `u2` int(11) NOT NULL,
  UNIQUE KEY `u1` (`u1`,`u2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- t3 has no unique key
CREATE TABLE IF NOT EXISTS `t3` (
  `u1` int(11) NOT NULL,
  `u2` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

-- This will cause an error "Error Code: 1366. Incorrect integer value: '' for column 'u1' at row 1":

INSERT INTO t1(id)
SELECT u1 FROM `t2` WHERE u1 = '';

-- These will run:

SELECT u1 FROM `t2` WHERE u1 = '1';

INSERT INTO t1(id)
SELECT u1 FROM `t2` WHERE u1 = CONCAT('');

INSERT INTO t1(id)
SELECT u1 FROM `t3` WHERE u1 = '';

Same with 5.5.28 on Linux (warning instead of error, as strict SQL mode is not set):

...
mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: '' for column 'u1' at row 1
1 row in set (0.00 sec)

mysql> INSERT INTO t1(id)
    -> SELECT u1 FROM `t2` WHERE u1 = CONCAT('');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1(id)
    -> SELECT u1 FROM `t3` WHERE u1 = '';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.5.28-debug |
+--------------+
1 row in set (0.00 sec)

MySQL should be consistent about empty strings as integer values, no matter how empty string is created or what kinds of indexes exist.

Thank you for the bug report. Verified on 5.5 source.

d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 Source distribution

Copyright (c) 2000, 2012, 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.5 >CREATE DATABASE `bug_test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql 5.5 >USE `bug_test`;
Database changed

<cut>

mysql 5.5 >INSERT INTO t1(id)
    -> SELECT u1 FROM `t2` WHERE u1 = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql 5.5 >show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'u1' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.5 >INSERT INTO t1(id)
    -> SELECT u1 FROM `t3` WHERE u1 = '';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

--------------------------------------------------------------------------
mysql 5.7 >-- This will cause an error "Error Code: 1366. Incorrect integer value: '' for column 'u1' at row 1":
mysql 5.7 >
mysql 5.7 >INSERT INTO t1(id)
    -> SELECT u1 FROM `t2` WHERE u1 = '';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.7 >SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 1.3.1               |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.7.1-m11-log       |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

Posted by developer:
 
Not reproducible on current 5.7.