| 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 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)
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:
[30 Nov 2022 13:06]
MySQL Verification Team
Thank you, Mr, Han, For your contribution. It has been forwarded to the team in charge .....
Description: When accessing json fields through prepare, there are some problems with type conversion. The returned result is not stable. How to repeat: CREATE TABLE `t1` ( `j` json DEFAULT NULL ) ENGINE=InnoDB; 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)