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:
None 
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
Description:
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()
   case STRING_VALUE:
   case LONG_DATA_VALUE:
   {
-    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
Hi,

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
Bogdan
[7 Jul 23:21] Jon Stephens
Fixed in MySQL 8.0.22 as part of WL#9384.

Closed.