Bug #109226 Contribution by Tencent: Unable to write json normally in the prepare mode

• Submitted: 29 Nov 2022 7:59
• Modified: 29 Nov 2022 14:24
• Reporter: Quanan Han (OCA)
• Status: Verified
• Category: MySQL Server: JSON
• Severity: S4 (Feature request)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: json

[29 Nov 2022 7:59] Quanan Han

Description:
Insert the json field in the prepare mode. When an insert fails due to a type mismatch, subsequent normal inserts will fail.

How to repeat:
CREATE TABLE `t1` (
  `j` json DEFAULT NULL
) ENGINE=InnoDB;

set @a=123; set @b='{"key1": "value1"}';
prepare ps1 from 'insert into t1 values (?)';
execute ps1 using @b;  -- successful execution of insert
execute ps1 using @a;
execute ps1 using @b; -- Failed to execute insert

Suggested fix:
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index fe1c4cc..9b4ca0f 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -3184,8 +3184,17 @@ bool Prepared_statement::check_parameter_types() {
             item->data_type_actual() == MYSQL_TYPE_TIME)
           continue;
         break;
-      case MYSQL_TYPE_VARCHAR:
       case MYSQL_TYPE_JSON:
+        if (item->data_type_actual() == MYSQL_TYPE_LONGLONG ||
+            item->data_type_actual() == MYSQL_TYPE_NEWDECIMAL ||
+            item->data_type_actual() == MYSQL_TYPE_DOUBLE ||
+            item->data_type_actual() == MYSQL_TYPE_DATETIME ||
+            item->data_type_actual() == MYSQL_TYPE_TIMESTAMP ||
+            item->data_type_actual() == MYSQL_TYPE_DATE ||
+            item->data_type_actual() == MYSQL_TYPE_TIME)
+          continue;
+        break;
+      case MYSQL_TYPE_VARCHAR:
       case MYSQL_TYPE_TINY_BLOB:
       case MYSQL_TYPE_MEDIUM_BLOB:
       case MYSQL_TYPE_BLOB:

But after this fix, there is other preblem and i am confused. Mtr 'json.json_prep_stmts' test case failed。

insert into t1 values('{"int" : 123, "dec" : 3.14, "flt" : 3.14E1, "str" : "xyz", "array" : [1, 2, 4]}');
set @int=123;
set @intstr='123';
prepare ps_get_int from "select * from t1 where json_extract(j, '$.int') = ?";
execute ps_get_int using @int;
execute ps_get_int using @intstr; -- This sql is affected and the result cannot be obtained

[29 Nov 2022 14:24] MySQL Verification Team

Hi Mr. Han,

Thank you for your bug report.

We successfully repeated the error and it is evident that our prepared statement require a new feature for the JSON data type.

Verified as a feature request.