Bug #107399 Broken integer value empty string validation with bind vars (8.0.27 to 8.0.28)
Submitted: 25 May 2022 20:56 Modified: 1 Jun 2022 22:34
Reporter: Александр Ммммммм Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S1 (Critical)
Version:8.0.28, 8.0.29 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[25 May 2022 20:56] Александр Ммммммм
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
[26 May 2022 9:07] MySQL Verification Team
Hello Александр Ммммммм,

Thank you for the report and test case.

regards,
Umesh
[1 Jun 2022 22:34] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL 8.0.30 release, and here's the proposed changelog entry from the documentation team:

A prepared statement could accept an empty string as a valid float value,
which represents a regression from 8.0.27 behavior. This fix explicitly
checks that the length of an interpreted string is non-empty and fully
interpreted as a (float) number. In addition, new verification now ensures
that:
 
* All numeric values are supported with empty strings and strings
  that are all spaces. 
* Regular numeric values are supported, as well as
   numeric values with leading and trailing spaces.

Thank you for the bug report.