Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.3, 13.0, 13.1
Description
The single-element IN matching shows it goes through the = JSON-semantic path (compare_e_json_str); with ≥2 elements the multi-value comparison path of Item_func_in falls back to plain string comparison (quoted JSON text "abc" vs the bare value abc, not equal), constantly false. The semantically equivalent OR expansion and IN give different results, and it is incompatible with MySQL.
CREATE TABLE t(j JSON);
|
INSERT INTO t VALUES ('{"a":"abc"}'), ('{"a":"xyz"}'); |
|
|
SELECT COUNT(*) FROM t WHERE j->'$.a' = 'abc'; -- 1 ✓ single = correct |
SELECT COUNT(*) FROM t WHERE j->'$.a' IN ('abc'); -- 1 ✓ single-element IN correct |
SELECT COUNT(*) FROM t WHERE j->'$.a' IN ('abc','ABC'); -- 0 ✗ two-element broken! |
SELECT COUNT(*) FROM t WHERE j->'$.a' IN ('xyz','abc','qqq'); -- 0 ✗ three-element broken! |
SELECT COUNT(*) FROM t WHERE j->'$.a' = 'abc' OR j->'$.a' = 'ABC'; -- 1 ✓ OR expansion correct |
SELECT ('{"a":"abc"}'->'$.a') IN ('abc','ABC'); -- 0 ✗ broken in constant context too |
SELECT COUNT(*) FROM t WHERE j->>'$.a' IN ('abc','ABC'); -- 1 ✓ ->> correct (control group) |
|
|
-- MySQL reference (trunk 26.7.0): |
SELECT COUNT(*) FROM t WHERE JSON_EXTRACT(j,'$.a') IN ('abc','ABC'); -- 1 ✓ |
Attachments
Issue Links
- relates to
-
-
- Confirmed
-