Details
Major Description
A JSON string value extracted by JSON_EXTRACT() / col->'$.path' participating in a BETWEEN predicate is constantly FALSE (byte comparison against the quoted JSON serialized text), while the semantically equivalent x >= low AND x <= high returns the correct result (unquoted comparison).
On the same value, BETWEEN returns the opposite of its defining equivalent — range queries silently return empty sets.
This problem shares its origin with BUG-M1 (= reflexivity break) but manifests differently: Arg_comparator::set_cmp_func_string() installs JSON-semantic comparison functions (compare_e_json_str etc.) only for binary comparators (=/</>/<=>); the three-argument comparison path of Item_func_between is not covered and falls back to plain sortcmp (comparing the quoted text "apple", where '"'=0x22 is smaller than almost all printable characters, so the lower-bound check always fails).
-- Single-statement reproduction: three semantically equivalent expressions, two different results
|
SELECT
|
('{"a":"apple"}'->'$.a') BETWEEN 'a' AND 'c' AS bt, -- 0 WRONG! |
('{"a":"apple"}'->'$.a') >= 'a' |
AND ('{"a":"apple"}'->'$.a') <= 'c' AS eq_range, -- 1 correct |
('{"a":"apple"}'->'$.a') = 'apple' AS eq_val; -- 1 (= works) |
|
|
-- Table level: range query silently returns an empty set
|
CREATE TABLE t(j JSON);
|
INSERT INTO t VALUES ('{"a":"apple"}'),('{"a":"Banana"}'),('{"a":"cherry"}'); |
SELECT * FROM t WHERE j->'$.a BETWEEN 'a' AND 'c'; -- empty! (should return apple) |
SELECT * FROM t WHERE j->'$.a >= 'a' AND j->'$.a <= 'c'; -- apple ✓ |
|
|
-- CASE / SELECT list: constantly false there too |
SELECT CASE WHEN j->'$.a' BETWEEN 'a' AND 'c' THEN 'IN' ELSE 'OUT' END FROM t; -- all OUT |
|
|
-- JSON_EXTRACT direct call reproduces too (not specific to the -> operator)
|
SELECT JSON_EXTRACT('{"a":"apple"}','$.a') BETWEEN 'a' AND 'c'; -- 0 |