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
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