Bug #94802 The behavior between insert stmt and "prepare stmt and execute stmt" different
Submitted: 27 Mar 2019 18:59 Modified: 7 Jul 23:21
Reporter: Weidong Yu Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:5.7.23-4 OS:CentOS
Assigned to: CPU Architecture:Any
Tags: prepare statement

[27 Mar 2019 18:59] Weidong Yu
create table t (a int);
insert into t values (1.0E+01+'a');

It will produce error: ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'a'

However, if I do as follows:

prepare st1 from "insert into t values (? + ?)";
set @a=1.0E+01;
set @b='a';
execute st1 using @a,@b;

No error complain and data 10 is inserted

How to repeat:
See above Description

Suggested fix:
For function double Item_param::val_real()

Add check inside

git diff item.cc
diff --git a/sql/item.cc b/sql/item.cc
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4120,10 +4120,8 @@ double Item_param::val_real()
-    int dummy_err;
-    char *end_not_used;
-    return my_strntod(str_value.charset(), (char*) str_value.ptr(),
-                      str_value.length(), &end_not_used, &dummy_err);
+    return double_from_string_with_check (str_value.charset(), str_value.ptr(),
+                                          (char *) str_value.ptr() + str_value.length());
   case TIME_VALUE:
[27 Mar 2019 19:01] Weidong Yu
This is my fix solution

Attachment: item.cc (application/octet-stream, text), 297.57 KiB.

[4 Apr 2019 13:24] MySQL Verification Team

Thanks for the report. Verified as described.

In order to submit contributions you must first sign the Oracle Contribution Agreement (OCA).
For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html.
If you have any questions, please contact the MySQL community team. 

kind regards
[7 Jul 23:21] Jon Stephens
Fixed in MySQL 8.0.22 as part of WL#9384.