Bug #109227 Contribution by Tencent: Failed to query json content in the prepare mode
Submitted: 29 Nov 2022 8:06 Modified: 30 Nov 2022 2:18
Reporter: Quanan Han (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: JSON Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: json, prepare

[29 Nov 2022 8:06] Quanan Han
When accessing json fields through prepare, there are some problems with type conversion. The returned result is not stable.

How to repeat:
  `j` json DEFAULT NULL
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 @intstr;  -- can't get results
execute ps_get_int using @int;
execute ps_get_int using @intstr; -- can get results

Suggested fix:
I'm confused, I don't know whether the result needs to be returned by using @intstr.
In addition, directly querying through the select statement, I found that when the condition is a string, no results are returned。

mysql> select * from t1 where json_extract(j, '$.int') = 123;
| j                                                                        |
| {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]} |
1 row in set (0.00 sec)

mysql> select * from t1 where json_extract(j, '$.int') = '123';
Empty set (0.00 sec)
[29 Nov 2022 14:01] MySQL Verification Team
Hi Mr. Han,

Thank you for your bug report.

However, this does not seems to be a bug to us.

Evidently, key "int" is of the integer type, so you can prepare it only with an integer.

Henceforth, we do not see what is a bug there.

It is our intention to make new features in MySQL as strict as possible when it comes to the data types, hence you do not get result with a string variable.

If there is anything we have missed in our response, please let us know.
[29 Nov 2022 14:22] MySQL Verification Team
Hi Mr. Han,

Actually, we consider this to be a very good feature request.

Verified as a feature request.
[30 Nov 2022 2:18] Quanan Han
I agree that there are strict requirements on data types, and various type conversions are very confusing.

Using @intstr needs to find no results, so the following patch may fix this problem

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)
       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_BLOB:
[30 Nov 2022 13:06] MySQL Verification Team
Thank you, Mr, Han,

For your contribution.

It has been forwarded to the team in charge .....