Bug #91245 json column wtih integer value got incorrect result

• Submitted: 14 Jun 2018 2:08
• Modified: 14 Jun 2018 5:29
• Reporter: chen kris
• Status: Verified
• Category: MySQL Server: JSON
• Severity: S2 (Serious)
• Version: 5.7.19, 5.7.22, 8.0.11
• OS: MacOS
• CPU Architecture: Any

[14 Jun 2018 2:08] chen kris

Description:
This bug is Similar to previous ones I hava reported.I have a table with json column ,the value type is integer. I use single quotation marks of query key , when query key is one ,I got empty set but got all result when query key is more than one in sql.

How to repeat:
CREATE TABLE testjson (jdoc JSON);

INSERT INTO testjson VALUES('{"key1": 1, "key2": 11}');
INSERT INTO testJson VALUES('{"key1": 2, "key2": 22}');

select * from testjson where JSON_EXTRACT(jdoc,'$.key1') IN ('1') ;
return:
Empty set (0.00 sec)

select * from testjson where JSON_EXTRACT(jdoc,'$.key1') IN ('1', '2') ;
+-------------------------+
| jdoc                    |
+-------------------------+
| {"key1": 1, "key2": 11} |
| {"key1": 2, "key2": 22} |
+-------------------------+
2 rows in set, 1 warning (0.00 sec)

select * from testjson where JSON_EXTRACT(jdoc,'$.key1') IN (1) ;
+-------------------------+
| jdoc                    |
+-------------------------+
| {"key1": 1, "key2": 11} |
+-------------------------+
1 row in set (0.00 sec)

[14 Jun 2018 5:28] MySQL Verification Team

Hello chen kris,

Thank you for the report and test case.

Thanks,
Umesh

[25 Jun 2024 13:20] Daniël van Eeden

Not sure this is actually a bug. Looks like the types don't match.

mysql-8.4.0> select * from testjson where JSON_EXTRACT(jdoc,'$.key1') IN ("1") ;
Empty set (0.01 sec)

mysql-8.4.0> select * from testjson where JSON_EXTRACT(jdoc,'$.key1') IN (1) ;
+-------------------------+
| jdoc                    |
+-------------------------+
| {"key1": 1, "key2": 11} |
+-------------------------+
1 row in set (0.00 sec)

mysql-8.4.0> select * from testjson where CAST(JSON_EXTRACT(jdoc,'$.key1') AS CHAR) IN ("1") ;
+-------------------------+
| jdoc                    |
+-------------------------+
| {"key1": 1, "key2": 11} |
+-------------------------+
1 row in set (0.00 sec)

mysql-8.4.0> select * from testjson where JSON_EXTRACT(jdoc,'$.key1') IN (CAST("1" AS JSON)) ;
+-------------------------+
| jdoc                    |
+-------------------------+
| {"key1": 1, "key2": 11} |
+-------------------------+
1 row in set (0.00 sec)