Description:
Constants work fine, but empty string bind variables just converts to zero
How to repeat:
MySQL 8.0.27 - correct behavior
SELECT VERSION(), @@sql_mode
;
+---------+---------------------------------------------------------------------------------------------------------------------+
|VERSION()|@@sql_mode |
+---------+---------------------------------------------------------------------------------------------------------------------+
|8.0.27 |ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION|
+---------+---------------------------------------------------------------------------------------------------------------------+
DROP TABLE IF EXISTS test_empty_string
;
CREATE TABLE test_empty_string (int_col INT)
;
INSERT test_empty_string VALUES ('')
;
[HY000][1366] Incorrect integer value: '' for column 'int_col' at row 1
SET @val = ''
;
INSERT test_empty_string VALUES (@val)
;
[HY000][1366] Incorrect integer value: '' for column 'int_col' at row 1
PREPARE stmt1 FROM 'INSERT test_empty_string VALUES (?)'
;
EXECUTE stmt1 USING @val
;
[HY000][1366] Incorrect integer value: '' for column 'int_col' at row 1
SELECT * FROM test_empty_string
;
0 rows retrieved
MySQL 8.0.28 (8.0.29) - incorrect behavior
SELECT VERSION(), @@sql_mode
;
+---------+---------------------------------------------------------------------------------------------------------------------+
|VERSION()|@@sql_mode |
+---------+---------------------------------------------------------------------------------------------------------------------+
|8.0.28 |ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION|
+---------+---------------------------------------------------------------------------------------------------------------------+
DROP TABLE IF EXISTS test_empty_string
;
CREATE TABLE test_empty_string (int_col INT)
;
INSERT test_empty_string VALUES ('')
;
[HY000][1366] Incorrect integer value: '' for column 'int_col' at row 1
SET @val = ''
;
INSERT test_empty_string VALUES (@val)
;
[HY000][1366] Incorrect integer value: '' for column 'int_col' at row 1
Incorrect behavior:
PREPARE stmt1 FROM 'INSERT test_empty_string VALUES (?)'
;
EXECUTE stmt1 USING @val
;
1 row affected
SELECT * FROM test_empty_string
;
+-------+
|int_col|
+-------+
|0 |
+-------+
similar error in PHP PDO drivers
$PDO_sth->query with static statement - correctly raising error
$PDO_sth->prepare + execute - inserting incorrect value
also incorrect statement saved to mysql-bin-changelog (binlog_format = 'MIXED' OR 'STATEMENT')
There are incorrect statement
INSERT test_empty_string VALUES ('')
which executed with error
[HY000][1366] Incorrect integer value: '' for column 'int_col' at row 1
and replication gets broken