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)
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)