Bug #114509 Behavior in 8.0 differs from 5.7
Submitted: 29 Mar 2024 2:58 Modified: 14 Jun 2024 21:24
Reporter: Huaiyu Xu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0, 8.0.11, 8.0.36, 8.3.0 OS:Any
Assigned to: CPU Architecture:Any

[29 Mar 2024 2:58] Huaiyu Xu 
Description:
In MySQL5.7, a warning and an error will be returned when executing the following sqls.
In MySQL8.0, no error or warnings is returned.

How to repeat:
create table t(a varchar(10));
create table t1(a varchar(64));
insert into t1 values('316-354');
select if(a, 1, 0) from t1;
insert into t select if(a, 1, 0) from t1;
set @@sql_mode='';
insert into t select if(a, 1, 0) from t1;

Suggested fix:

Has this behavior modification been intentionally introduced? If so, could you provide details on the fix or change that resulted in this behavior alteration?
[29 Mar 2024 6:25] MySQL Verification Team 
Hello Huaiyu Xu,

Thank you for the report and test case.

regards,
Umesh
[14 Jun 2024 21:24] Jon Stephens 
Documented fix as follows in the MySQL 9.0.0 changelog:

    When a character string is converted to a numeric value, any
    non-numeric data trailing the numeric value should cause an
    error with strict mode and a warning with any other SQL mode,
    but in some cases, depending on the length and character set of
    the string, an invalid string did not raise any errors or
    warnings.

Closed.