Bug #91232 json column wtih string value got incorrect result
Submitted: 13 Jun 2018 11:11 Modified: 13 Jun 2018 15:11
Reporter: chen kris Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7/8.0 OS:MacOS
Assigned to: CPU Architecture:Any

[13 Jun 2018 11:11] chen kris
Description:
I have a table with json column ,the value type is string. when I use double 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": "111", "key2": "222"}');
INSERT INTO testJson VALUES('{"key1": "333", "key2": "444"}');

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

select * from testjson where JSON_EXTRACT(jdoc,'$.key1') IN ('"111"', '"333"') ;
return: 
+--------------------------------+
| jdoc                           |
+--------------------------------+
| {"key1": "111", "key2": "222"} |
| {"key1": "333", "key2": "444"} |
+--------------------------------+

select * from testjson where JSON_EXTRACT(jdoc,'$.key1') IN ('111') ;
+--------------------------------+
| jdoc                           |
+--------------------------------+
| {"key1": "111", "key2": "222"} |
+--------------------------------+
[13 Jun 2018 15:11] MySQL Verification Team
Thank you for the bug report. Verified as described.