Adding a JSON index to MYSQL may result in empty query results

After I added an index to the json field in the database, some of Django's queries stopped working, so I looked into the underlying SQL and found something strange.

You can reproduce the problem with the following SQL.

CREATE TABLE `products` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `status` varchar(32) DEFAULT NULL,
  `info` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

ALTER TABLE products ADD INDEX tag_group_idx ((
    CAST(info->>'$."Group"' as CHAR(32)) COLLATE utf8mb4_bin
)) USING BTREE;

INSERT INTO products (status, info) VALUES('active', '{"Group": "G0001"}');

---

MySQL version: 8.0.29.

OS: Windows

---

1. Generated by Django

   SQL:

   SELECT * FROM products
   WHERE status = 'active' AND
   JSON_EXTRACT(info,'$."Group"') = JSON_EXTRACT('"G0001"', '$');
   

   Result:

   id|status|info|
   --+------+----+
   

2. Using plain string

   SQL:

   SELECT * FROM products
   WHERE status = 'active' AND
   JSON_EXTRACT(info,'$."Group"') = 'G0001';
   

   Result:

   id|status|info              |
   --+------+------------------+
   1|active|{"Group": "G0001"}|
   

3. Remove redundant query conditions

   SQL:

   SELECT * FROM products
   WHERE JSON_EXTRACT(info,'$."Group"') = JSON_EXTRACT('"G0001"', '$');
   

   Result:

   id|status|info              |
   --+------+------------------+
   1|active|{"Group": "G0001"}|
   

As shown above, I tried three ways to query, but only the first one had empty results. I guess it was caused by the mismatch of the generated column types.

I found that after I deleted the index, the first worked fine.

ALTER TABLE products DROP INDEX tag_group_idx;
SELECT * FROM products
WHERE status = 'active' AND
JSON_EXTRACT(info,'$."Group"') = JSON_EXTRACT('"G0001"', '$');

Can anyone tell me the real reason for this result and how to avoid the index affecting the query results?

Thank you!

Вернуться на верх