Description:
A multi-valued index KEY ((CAST(doc->'$.a' AS UNSIGNED ARRAY))) accepts a
document whose array holds a non-integral JSON number such as 1.5, silently
(no error, no warning, no note), and stores the element under the key 2
(CAST(1.5 AS UNSIGNED) = 2). After that:
- 2 MEMBER OF (doc->'$.a'), JSON_CONTAINS(doc->'$.a', CAST(2 AS JSON)) and
JSON_OVERLAPS(doc->'$.a', CAST('[2]' AS JSON)) return the row, although
the array is [1.5] and the same predicate evaluates to 0 on that row;
- IGNORE INDEX returns the correct empty result, so CREATE INDEX changes
the answer;
- DELETE and UPDATE with the same WHERE clause modify the row.
The plan shows why there is no correction after the lookup:
-> Filter: json'2' member of (cast(json_extract(doc,_utf8mb4'$.a') as unsigned array))
-> Index lookup on mv using mvx (cast(json_extract(doc,_utf8mb4'$.a') as unsigned array) = json'2')
The residual filter is evaluated on cast(... as unsigned array) -- the generated
column expression that substitute_gc() put in place of doc->'$.a' -- i.e. on
the already-rounded value [2], not on the JSON array [1.5]. The filter can
therefore never reject what the index wrongly returned.
The stored key is the rounded value (0.5 -> 0, 1.5 -> 2, 2.5 -> 2, 2.6 -> 3;
see the table in How to repeat), and the same happens with
SIGNED ARRAY (also for negative values), with DECIMAL(M,D) ARRAY when the
element has more than D decimals (that one at least raises Note 3751 "Data
truncated for functional index" at INSERT time, but the query still returns the
wrong row), and from the probe side (a fractional probe against integer
elements).
Inconvertible elements are already rejected at INSERT (null -> ERROR 3903,
["x"] -> ERROR 3903, [-1] for UNSIGNED -> ERROR 3752). A non-integral number
is the one lossy case that is accepted without any diagnostic.
Related: Bug #108659 and Bug #112147 (both Verified, open) show the same
missing re-check with a JSON string vs a number / a DATE. This report needs no
type mismatch at all: an integer probe, a JSON number stored, and the index
changing the number.
How to repeat:
Stock server, default configuration.
CREATE DATABASE b068; USE b068;
CREATE TABLE mv (id INT PRIMARY KEY, doc JSON,
KEY mvx ((CAST(doc->'$.a' AS UNSIGNED ARRAY))));
INSERT INTO mv VALUES (1, '{"a":[1.5]}');
SHOW WARNINGS; -- Empty set
SELECT id FROM mv WHERE 2 MEMBER OF (doc->'$.a');
Expected: empty set -- the array is [1.5].
Actual (trunk 26.10.0, 9.7.2, 8.4.11):
+----+
| id |
+----+
| 1 |
+----+
SELECT id FROM mv IGNORE INDEX (mvx) WHERE 2 MEMBER OF (doc->'$.a');
-- Empty set (correct)
SELECT id FROM mv WHERE JSON_CONTAINS(doc->'$.a', CAST(2 AS JSON)); -- 1
SELECT id FROM mv WHERE JSON_OVERLAPS(doc->'$.a', CAST('[2]' AS JSON)); -- 1
1. THE RETURNED ROW FAILS ITS OWN WHERE CLAUSE:
SELECT id, doc->'$.a' AS arr, 2 MEMBER OF (doc->'$.a') AS says
FROM mv WHERE 2 MEMBER OF (doc->'$.a');
+----+-------+------+
| id | arr | says |
+----+-------+------+
| 1 | [1.5] | 0 |
+----+-------+------+
2. DELETE REMOVES A ROW THAT DOES NOT MATCH:
CREATE TABLE mv3 (id INT PRIMARY KEY, doc JSON,
KEY mvx ((CAST(doc->'$.a' AS UNSIGNED ARRAY))));
INSERT INTO mv3 VALUES (1, '{"a":[1.5]}'), (2, '{"a":[2]}');
DELETE FROM mv3 WHERE 2 MEMBER OF (doc->'$.a');
-- Query OK, 2 rows affected
SELECT * FROM mv3;
-- Empty set (expected: row 1, {"a": [1.5]}, to remain)
UPDATE behaves the same:
CREATE TABLE mv4 (id INT PRIMARY KEY, doc JSON,
KEY mvx ((CAST(doc->'$.a' AS UNSIGNED ARRAY))));
INSERT INTO mv4 VALUES (1, '{"a":[1.5]}');
UPDATE mv4 SET id = 100 WHERE 2 MEMBER OF (doc->'$.a');
-- Rows matched: 1 Changed: 1 (expected 0)
3. WHICH STORED VALUE LANDS ON WHICH KEY (UNSIGNED ARRAY, probes 0..3,
"index only" = returned with the index, not with IGNORE INDEX):
stored [0.4] -> matches 0 (index only)
stored [0.5] -> matches 0 (index only)
stored [1.4] -> matches 1 (index only)
stored [1.5] -> matches 2 (index only)
stored [2.5] -> matches 2 (index only)
stored [2.6] -> matches 3 (index only)
stored [1.9999] -> matches 2 (index only)
stored [1e0] -> matches 1 (both -- correct, 1e0 = 1)
stored [1.0] -> matches 1 (both -- correct)
4. OTHER KEY TYPES AND THE PROBE SIDE:
-- SIGNED ARRAY, both signs
CREATE TABLE ms (id INT PRIMARY KEY, doc JSON,
KEY mvx ((CAST(doc->'$.a' AS SIGNED ARRAY))));
INSERT INTO ms VALUES (1, '{"a":[1.5]}'), (2, '{"a":[-1.5]}');
SELECT id FROM ms WHERE 2 MEMBER OF (doc->'$.a'); -- 1 (expected none)
SELECT id FROM ms WHERE -2 MEMBER OF (doc->'$.a'); -- 2 (expected none)
-- DECIMAL(10,2) ARRAY (INSERT raises Note 3751, the query is still wrong)
CREATE TABLE md (id INT PRIMARY KEY, doc JSON,
KEY mvx ((CAST(doc->'$.a' AS DECIMAL(10,2) ARRAY))));
INSERT INTO md VALUES (1, '{"a":[1.006]}');
SELECT id FROM md WHERE 1.01 MEMBER OF (doc->'$.a'); -- 1 (expected none)
-- fractional probe against integer elements
CREATE TABLE mp (id INT PRIMARY KEY, doc JSON,
KEY mvx ((CAST(doc->'$.a' AS SIGNED ARRAY))));
INSERT INTO mp VALUES (1, '{"a":[0,1,2]}');
SELECT id FROM mp WHERE JSON_CONTAINS(doc->'$.a', CAST(1.5 AS JSON));
-- 1 (expected none; IGNORE INDEX (mvx) returns none)
Reproduction script: repro.py in the attached folder.
Suggested fix:
A multi-valued index on CAST(... AS <type> ARRAY) is lossy whenever a JSON
element does not convert exactly to <type>. Either of these closes the hole:
a) Reject (or at least warn on) inexact conversions when the index key is
built, as is already done for null, strings and out-of-range values
(ERROR 3903 / 3752). For UNSIGNED/SIGNED ARRAY a JSON number with a
fractional part should be ERROR 3903 or 3752 rather than silently rounded;
for DECIMAL(M,D) ARRAY the existing Note 3751 should be an error under
strict mode.
b) Keep the lossy key, but evaluate the residual predicate on the original
JSON expression (doc->'$.a') rather than on the substituted
cast(... as <type> array) expression, so that rows over-returned by the
index lookup are filtered out. This also fixes Bug #108659 and Bug
#112147.
(b) is the more general fix; (a) is the smaller one for the numeric case.
A regression test: KEY ((CAST(doc->'$.a' AS UNSIGNED ARRAY))), one row
{"a":[1.5]}, assert that SELECT count(*) ... WHERE 2 MEMBER OF (doc->'$.a') is
0 (or that the INSERT fails), and that DELETE ... WHERE 2 MEMBER OF (...) on
the rows {"a":[1.5]}, {"a":[2]} leaves the first.
© 2026, Oracle Corporation and/or its affiliates
Description: A multi-valued index KEY ((CAST(doc->'$.a' AS UNSIGNED ARRAY))) accepts a document whose array holds a non-integral JSON number such as 1.5, silently (no error, no warning, no note), and stores the element under the key 2 (CAST(1.5 AS UNSIGNED) = 2). After that: - 2 MEMBER OF (doc->'$.a'), JSON_CONTAINS(doc->'$.a', CAST(2 AS JSON)) and JSON_OVERLAPS(doc->'$.a', CAST('[2]' AS JSON)) return the row, although the array is [1.5] and the same predicate evaluates to 0 on that row; - IGNORE INDEX returns the correct empty result, so CREATE INDEX changes the answer; - DELETE and UPDATE with the same WHERE clause modify the row. The plan shows why there is no correction after the lookup: -> Filter: json'2' member of (cast(json_extract(doc,_utf8mb4'$.a') as unsigned array)) -> Index lookup on mv using mvx (cast(json_extract(doc,_utf8mb4'$.a') as unsigned array) = json'2') The residual filter is evaluated on cast(... as unsigned array) -- the generated column expression that substitute_gc() put in place of doc->'$.a' -- i.e. on the already-rounded value [2], not on the JSON array [1.5]. The filter can therefore never reject what the index wrongly returned. The stored key is the rounded value (0.5 -> 0, 1.5 -> 2, 2.5 -> 2, 2.6 -> 3; see the table in How to repeat), and the same happens with SIGNED ARRAY (also for negative values), with DECIMAL(M,D) ARRAY when the element has more than D decimals (that one at least raises Note 3751 "Data truncated for functional index" at INSERT time, but the query still returns the wrong row), and from the probe side (a fractional probe against integer elements). Inconvertible elements are already rejected at INSERT (null -> ERROR 3903, ["x"] -> ERROR 3903, [-1] for UNSIGNED -> ERROR 3752). A non-integral number is the one lossy case that is accepted without any diagnostic. Related: Bug #108659 and Bug #112147 (both Verified, open) show the same missing re-check with a JSON string vs a number / a DATE. This report needs no type mismatch at all: an integer probe, a JSON number stored, and the index changing the number. How to repeat: Stock server, default configuration. CREATE DATABASE b068; USE b068; CREATE TABLE mv (id INT PRIMARY KEY, doc JSON, KEY mvx ((CAST(doc->'$.a' AS UNSIGNED ARRAY)))); INSERT INTO mv VALUES (1, '{"a":[1.5]}'); SHOW WARNINGS; -- Empty set SELECT id FROM mv WHERE 2 MEMBER OF (doc->'$.a'); Expected: empty set -- the array is [1.5]. Actual (trunk 26.10.0, 9.7.2, 8.4.11): +----+ | id | +----+ | 1 | +----+ SELECT id FROM mv IGNORE INDEX (mvx) WHERE 2 MEMBER OF (doc->'$.a'); -- Empty set (correct) SELECT id FROM mv WHERE JSON_CONTAINS(doc->'$.a', CAST(2 AS JSON)); -- 1 SELECT id FROM mv WHERE JSON_OVERLAPS(doc->'$.a', CAST('[2]' AS JSON)); -- 1 1. THE RETURNED ROW FAILS ITS OWN WHERE CLAUSE: SELECT id, doc->'$.a' AS arr, 2 MEMBER OF (doc->'$.a') AS says FROM mv WHERE 2 MEMBER OF (doc->'$.a'); +----+-------+------+ | id | arr | says | +----+-------+------+ | 1 | [1.5] | 0 | +----+-------+------+ 2. DELETE REMOVES A ROW THAT DOES NOT MATCH: CREATE TABLE mv3 (id INT PRIMARY KEY, doc JSON, KEY mvx ((CAST(doc->'$.a' AS UNSIGNED ARRAY)))); INSERT INTO mv3 VALUES (1, '{"a":[1.5]}'), (2, '{"a":[2]}'); DELETE FROM mv3 WHERE 2 MEMBER OF (doc->'$.a'); -- Query OK, 2 rows affected SELECT * FROM mv3; -- Empty set (expected: row 1, {"a": [1.5]}, to remain) UPDATE behaves the same: CREATE TABLE mv4 (id INT PRIMARY KEY, doc JSON, KEY mvx ((CAST(doc->'$.a' AS UNSIGNED ARRAY)))); INSERT INTO mv4 VALUES (1, '{"a":[1.5]}'); UPDATE mv4 SET id = 100 WHERE 2 MEMBER OF (doc->'$.a'); -- Rows matched: 1 Changed: 1 (expected 0) 3. WHICH STORED VALUE LANDS ON WHICH KEY (UNSIGNED ARRAY, probes 0..3, "index only" = returned with the index, not with IGNORE INDEX): stored [0.4] -> matches 0 (index only) stored [0.5] -> matches 0 (index only) stored [1.4] -> matches 1 (index only) stored [1.5] -> matches 2 (index only) stored [2.5] -> matches 2 (index only) stored [2.6] -> matches 3 (index only) stored [1.9999] -> matches 2 (index only) stored [1e0] -> matches 1 (both -- correct, 1e0 = 1) stored [1.0] -> matches 1 (both -- correct) 4. OTHER KEY TYPES AND THE PROBE SIDE: -- SIGNED ARRAY, both signs CREATE TABLE ms (id INT PRIMARY KEY, doc JSON, KEY mvx ((CAST(doc->'$.a' AS SIGNED ARRAY)))); INSERT INTO ms VALUES (1, '{"a":[1.5]}'), (2, '{"a":[-1.5]}'); SELECT id FROM ms WHERE 2 MEMBER OF (doc->'$.a'); -- 1 (expected none) SELECT id FROM ms WHERE -2 MEMBER OF (doc->'$.a'); -- 2 (expected none) -- DECIMAL(10,2) ARRAY (INSERT raises Note 3751, the query is still wrong) CREATE TABLE md (id INT PRIMARY KEY, doc JSON, KEY mvx ((CAST(doc->'$.a' AS DECIMAL(10,2) ARRAY)))); INSERT INTO md VALUES (1, '{"a":[1.006]}'); SELECT id FROM md WHERE 1.01 MEMBER OF (doc->'$.a'); -- 1 (expected none) -- fractional probe against integer elements CREATE TABLE mp (id INT PRIMARY KEY, doc JSON, KEY mvx ((CAST(doc->'$.a' AS SIGNED ARRAY)))); INSERT INTO mp VALUES (1, '{"a":[0,1,2]}'); SELECT id FROM mp WHERE JSON_CONTAINS(doc->'$.a', CAST(1.5 AS JSON)); -- 1 (expected none; IGNORE INDEX (mvx) returns none) Reproduction script: repro.py in the attached folder. Suggested fix: A multi-valued index on CAST(... AS <type> ARRAY) is lossy whenever a JSON element does not convert exactly to <type>. Either of these closes the hole: a) Reject (or at least warn on) inexact conversions when the index key is built, as is already done for null, strings and out-of-range values (ERROR 3903 / 3752). For UNSIGNED/SIGNED ARRAY a JSON number with a fractional part should be ERROR 3903 or 3752 rather than silently rounded; for DECIMAL(M,D) ARRAY the existing Note 3751 should be an error under strict mode. b) Keep the lossy key, but evaluate the residual predicate on the original JSON expression (doc->'$.a') rather than on the substituted cast(... as <type> array) expression, so that rows over-returned by the index lookup are filtered out. This also fixes Bug #108659 and Bug #112147. (b) is the more general fix; (a) is the smaller one for the numeric case. A regression test: KEY ((CAST(doc->'$.a' AS UNSIGNED ARRAY))), one row {"a":[1.5]}, assert that SELECT count(*) ... WHERE 2 MEMBER OF (doc->'$.a') is 0 (or that the INSERT fails), and that DELETE ... WHERE 2 MEMBER OF (...) on the rows {"a":[1.5]}, {"a":[2]} leaves the first.