1#
2# Bug 84366: InnoDB index dives do not detect concurrent tree changes, return
3# bogus estimates
4#
5CREATE TABLE t1 (key1 INT NOT NULL, key2 INT NOT NULL,
6INDEX i1(key1), INDEX i2(key2)) ENGINE = InnoDB;
7SET @@GLOBAL.innodb_purge_stop_now = TRUE;
8ALTER TABLE t1 ADD keyC INT NOT NULL, ADD INDEX iC(keyC);
9UPDATE t1 SET keyC = key1;
10ANALYZE TABLE t1;
11Table	Op	Msg_type	Msg_text
12test.t1	analyze	status	OK
13EXPLAIN SELECT * FROM t1 WHERE key1 = 1 OR key2 = 2 OR keyC = 12;
14id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
151	SIMPLE	t1	NULL	index_merge	i1,i2,iC	i1,i2,iC	4,4,4	NULL	3	100.00	Using union(i1,i2,iC); Using where
16Warnings:
17Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`keyC` AS `keyC` from `test`.`t1` where ((`test`.`t1`.`key1` = 1) or (`test`.`t1`.`key2` = 2) or (`test`.`t1`.`keyC` = 12))
18SET DEBUG_SYNC = "btr_estimate_n_rows_in_range_between_dives SIGNAL estimate_ready WAIT_FOR estimate_finish";
19EXPLAIN SELECT * FROM t1 WHERE key1 = 1 OR key2 = 2 OR keyC = 12;
20SET DEBUG_SYNC = "now WAIT_FOR estimate_ready";
21SET @@GLOBAL.innodb_purge_run_now = TRUE;
22SET DEBUG_SYNC = "now SIGNAL estimate_finish";
23id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
241	SIMPLE	t1	NULL	index_merge	i1,i2,iC	i1,i2,iC	4,4,4	NULL	3	100.00	Using union(i1,i2,iC); Using where
25Warnings:
26Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`keyC` AS `keyC` from `test`.`t1` where ((`test`.`t1`.`key1` = 1) or (`test`.`t1`.`key2` = 2) or (`test`.`t1`.`keyC` = 12))
27EXPLAIN SELECT * FROM t1 WHERE key1=1 or key2=2 or keyC=12;
28id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
291	SIMPLE	t1	NULL	index_merge	i1,i2,iC	i1,i2,iC	4,4,4	NULL	3	100.00	Using union(i1,i2,iC); Using where
30Warnings:
31Note	1003	/* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`keyC` AS `keyC` from `test`.`t1` where ((`test`.`t1`.`key1` = 1) or (`test`.`t1`.`key2` = 2) or (`test`.`t1`.`keyC` = 12))
32DROP TABLE t1;
33