MySQL Bugs: #115560: JSON functional index not being considered for NULL check

Bug #115560	JSON functional index not being considered for NULL check
Submitted:	10 Jul 2024 15:43	Modified:	12 Jul 2024 8:13
Reporter:	Tanel K	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.38	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
When a functional index of a JSON column is queried with IS NULL / IS NOT NULL / "str%" then optimizer will not consider using an index and does table scan.   

How to repeat:
Following example from 8.0.21 release notes (https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html) 

CREATE TABLE inventory(
    items JSON,
    INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING CHAR(50))) )
);

INSERT INTO inventory(`items`) VALUES ('{"name":"blab"}');

EXPLAIN SELECT items->"$.name" FROM inventory WHERE JSON_VALUE(items, "$.name" RETURNING CHAR(50)) LIKE "bla%";

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | inventory | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

Hi Mr. K,

Thank you for your bug report.

However , it is not a bug.

With a small number of rows, it is too expensive to use index.

Scanning rows is much , much faster in those cases.

Try the same with ten thousand rows or similar.

Not a bug.

Hey! I've tried it with all sorts of cardinality and 1-to-1 comparison with a real CHAR column - I can never get the JSON variant to use index when the real one does. The described steps were the shortest route to reproduction on my end, with an actual CHAR column with the same steps, the index was consulted. Thanks for swift answer, hoping you can spare another look!

I think this is a reasonable enhancement request. We already have bug#104713 reported for using functional indexes for LIKE. That bug report does not cover IS NULL/IS NOT NULL, but I think that sounds like a good idea too.

Should I enter a new submission with just the NULL check behavior to differentiate it from the LIKE bug?

I think it's fine to keep this bug for IS NULL/IS NOT NULL. I've adjusted the bug title (removed "wildcard") and reopened it.