| Bug #108114 | query return different result with or without index | ||
|---|---|---|---|
| Submitted: | 11 Aug 2022 7:59 | Modified: | 11 Aug 2022 8:47 |
| Reporter: | x j | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.30 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | generate columns | ||
[11 Aug 2022 8:47]
MySQL Verification Team
Hello x j, Thank you for the report and test case. regards, Umesh
Description: use or not use index will cause a different result if the index is on generate columns How to repeat: CREATE TABLE person (id INT PRIMARY KEY,address_info JSON,city VARCHAR(2) AS (JSON_UNQUOTE(address_info->"$.city")),KEY (city)); set @@sql_mode='' INSERT INTO `person` (`id`, `address_info`) VALUES('1','{\"city\": \"Beijing\"}'); -- success with warning set @@sql_mode=default SELECT id FROM person ignore index(`city`) WHERE address_info->>"$.city" = 'Beijing'; -- 1 record SELECT id FROM person force index(`city`) WHERE address_info->>"$.city" = 'Beijing'; -- 0 record