Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.0(EOL), 11.1(EOL)
Description
--source include/have_innodb.inc
|
|
CREATE TABLE t ( |
a INT, |
b INT, |
vb INT GENERATED ALWAYS AS (b) VIRTUAL, |
pk INT AUTO_INCREMENT, |
PRIMARY KEY (pk), |
KEY (a), |
KEY (vb) |
) ENGINE=InnoDB;
|
|
INSERT INTO t (a, b) VALUES |
(9,1),(7,1),(1,1),(3,1),(5,2),(1,2),(5,2),(9,1),(8,2),(2,1),
|
(2,9),(2,0),(8,2),(1,8),(8,3),(2,5),(9,3);
|
|
INSERT INTO t (a,b) SELECT a,b FROM t; |
|
ANALYZE TABLE t PERSISTENT FOR ALL; # Optional, fails either way |
|
SET optimizer_switch='rowid_filter=on'; |
SELECT * FROM t WHERE a in (1,2) AND vb = 1; |
|
SET optimizer_switch='rowid_filter=off'; |
SELECT * FROM t WHERE a in (1,2) AND vb = 1; |
|
DROP TABLE t; |
11.0 5fb2c031 |
SET optimizer_switch='rowid_filter=on'; |
SELECT * FROM t WHERE a in (1,2) AND vb = 1; |
a b vb pk
|
SET optimizer_switch='rowid_filter=off'; |
SELECT * FROM t WHERE a in (1,2) AND vb = 1; |
a b vb pk
|
1 1 1 3
|
2 1 1 10
|
1 1 1 20
|
2 1 1 27
|
The result with rowid_filter=off is the correct one.
Plan for the wrong result:
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t ref|filter a,vb vb|a 5|5 const 12 (41%) 41.18 Using where; Using rowid filter |
Warnings:
|
Note 1003 select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,`test`.`t`.`vb` AS `vb`,`test`.`t`.`pk` AS `pk` from `test`.`t` where `test`.`t`.`vb` = 1 and `test`.`t`.`a` in (1,2) |
SET optimizer_switch='rowid_filter=off'; |
The failure used to be happening on earlier versions, too, but after the commit below in 10.4.27 the provided test case no longer triggers a plan with rowid filter and thus the problem does not occur.
commit 58cd0bd59ef011be54f162237f2ff017c3148e7b
|
Author: Igor Babaev
|
Date: Mon Oct 17 16:44:10 2022 -0700
|
|
MDEV-28846 Poor performance when rowid filter contains no elements
|
However, on 11.x it still happens.