Bug #121822 Presense of INDEX .. AS DATE ARRAY changes the comparison semantics of JSON_CONTAINS
| Submitted: | 18 Nov 7:05 | Modified: | 20 Nov 12:01 |
|---|---|---|---|
| Reporter: | Alexander Barkov | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.46, 8.4.13, 97.4, 26.10.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any |
[18 Nov 7:10]
Alexander Barkov
A similar problem is repeatable with string comparison.
It changes comparison semantics from case sensitive to case insensitive after adding INDEX AS CHAR ARRAY.
-- 1. Create table with a JSON column
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
tags JSON NOT NULL
);
-- Insert lowercase tag inside a JSON array
INSERT INTO products (tags) VALUES ('["electronics", "sale"]');
-- 2. WITHOUT INDEX: Strict JSON binary string semantics (utf8mb4_bin).
-- Searching for uppercase '"ELECTRONICS"' does not match '"electronics"'.
-- Returns 0 rows.
SELECT id, tags
FROM products
WHERE JSON_CONTAINS(tags, '"ELECTRONICS"');
-- 3. Add a multi-valued index casting array elements to CHAR(50) ARRAY.
-- Because no COLLATE clause is specified, CHAR(50) defaults to utf8mb4_0900_ai_ci (case-insensitive & accent-insensitive).
ALTER TABLE products
ADD INDEX idx_tags ((CAST(tags AS CHAR(50) ARRAY)));
-- 4. WITH INDEX ENABLED: Case-insensitive SQL CHAR semantics.
-- The optimizer uses idx_tags and matches '"ELECTRONICS"' against '"electronics"'.
-- Returns 1 row!
SELECT id, tags
FROM products
WHERE JSON_CONTAINS(tags, '"ELECTRONICS"');
-- Verify the optimizer used idx_tags (type: range / ref on idx_tags)
EXPLAIN SELECT id, tags
FROM products
WHERE JSON_CONTAINS(tags, '"ELECTRONICS"');
-- 5. WITH INDEX DISABLED (IGNORE INDEX): Reverts back to 0 rows.
SELECT id, tags
FROM products IGNORE INDEX (idx_tags)
WHERE JSON_CONTAINS(tags, '"ELECTRONICS"');
[20 Nov 12:01]
Chaithra Marsur Gopala Reddy
Hi Alexander Barkov, Thank you for the test case. Verified as described.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Result set changes after adding: ALTER TABLE events ADD INDEX idx_event_dates ((CAST(event_dates AS DATE ARRAY))); This is not expected. How to repeat: -- 1. Create table with a JSON column CREATE TABLE events ( id INT PRIMARY KEY AUTO_INCREMENT, event_dates JSON NOT NULL ); -- Insert a canonical zero-padded date string inside a JSON array INSERT INTO events (event_dates) VALUES ('["2027-01-01", "2027-06-15"]'); -- 2. WITHOUT INDEX: Strict JSON string semantics. -- JSON has no native DATE type when parsed from text; both sides are JSON strings. -- Because "2027-1-1" != "2027-01-01" byte-for-byte, this returns 0 rows. SELECT id, event_dates FROM events WHERE JSON_CONTAINS(event_dates, '"2027-1-1"'); -- 3. Add a multi-valued index casting array elements to SQL DATE ALTER TABLE events ADD INDEX idx_event_dates ((CAST(event_dates AS DATE ARRAY))); -- 4. WITH INDEX ENABLED: SQL DATE semantics. -- The optimizer uses idx_event_dates, parses '"2027-1-1"' as DATE('2027-01-01'), -- and matches the indexed entry! Returns 1 row. SELECT id, event_dates FROM events WHERE JSON_CONTAINS(event_dates, '"2027-1-1"'); -- Verify the optimizer actually used the index (look for "Using where; Using index" / range scan on idx_event_dates) EXPLAIN SELECT id, event_dates FROM events WHERE JSON_CONTAINS(event_dates, '"2027-1-1"'); -- 5. WITH INDEX DISABLED (IGNORE INDEX): Reverts back to 0 rows. SELECT id, event_dates FROM events IGNORE INDEX (idx_event_dates) WHERE JSON_CONTAINS(event_dates, '"2027-1-1"'); Suggested fix: The result set should be equal after adding an index. Adding a data type specifier inside JSON_CONTAINS could solve the problem: WHERE JSON_CONTAINS(event_dates, '"2027-1-1"' AS DATE); or WHERE JSON_CONTAINS(event_dates, CAST('"2027-1-1"' AS DATE)); (or any other reasonable syntax). The DATE data type specifier inside JSON_CONTAINS would enforce the DATE data type both with and without index, and it could stay with usual string semantics if no type specifier is used. The "INDEX idx_event_dates ((CAST(event_dates AS DATE ARRAY)" would not be use for optimizing JSON_CONTAINS does not contain AS DATE.