--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;

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

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';

commit 58cd0bd59ef011be54f162237f2ff017c3148e7b

Author: Igor Babaev

Date:   Mon Oct 17 16:44:10 2022 -0700

    MDEV-28846 Poor performance when rowid filter contains no elements