Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.2.5, 7.6.2
-
Untriaged
-
0
-
No
Description
If an index is defined with a CASE statement as an index key, and the CASE statement contains a not-equal expression, and the same CASE statement is used in the projection clause of a query, then covering index scan is not done properly.
CREATE INDEX sample_index ON `default`:`travel-sample`.`inventory`.`route`(`id`, CASE WHEN airline='AF' THEN 1 ELSE 0 END, CASE WHEN airline<>'AF' THEN 1 ELSE 0 END);
|
|
|
EXPLAIN
|
SELECT CASE WHEN airline='AF' THEN 1 ELSE 0 END AS AF_AIRLINE,
|
CASE WHEN airline<>'AF' THEN 1 ELSE 0 END AS NOT_AF_AIRLINE
|
FROM `travel-sample`.`inventory`.`route`
|
WHERE id < 20000
|
LIMIT 10;
|
The query should use covering index scan but it is not.