^^&&^^

Edit File: partition_index_innodb.result

#
# Bug#18167648: WRONG RESULTS WITH PARTITIONING, INDEX_MERGE AND NO PK
#
CREATE TABLE t1
(a smallint,
b smallint,
c smallint,
KEY  a (a),
KEY  b (b)
) ENGINE=InnoDB
PARTITION BY HASH(c) PARTITIONS 3;
# c will simulate DB_ROW_ID + force to chosen partition.
# c is unique so we can identify rows in the test.
# Fillers to make the optimizer choose index_merge_intersect/union:
INSERT INTO t1 VALUES (1,1,1), (1,1,1+3), (1,1,1+6), (1,1,1+9);
# row N..N+3
INSERT INTO t1 VALUES (1,2,1+12), (2,2,2+15), (2,2,2+18), (1,2,3+21);
# More index matching rows for index_merge_intersect: N+4, N+5
INSERT INTO t1 VALUES (2,2,1+24);
INSERT INTO t1 VALUES (2,1,1+27);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SET @old_opt_switch = @@session.optimizer_switch;
SET SESSION optimizer_switch="index_merge=on";
SET SESSION optimizer_switch="index_merge_intersection=on";
SET SESSION optimizer_switch="index_merge_sort_union=off";
SET SESSION optimizer_switch="index_merge_union=off";
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND  c > 0 AND c < 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	a,b	a,b	3,3	NULL	1	Using intersect(a,b); Using where
SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND  c > 0 AND c < 100;
a	b	c
2	2	17
2	2	20
2	2	25
EXPLAIN PARTITIONS SELECT a,b,c FROM t1 WHERE a = 2 AND b = 2 AND c IN (13,25,28);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	p1	index_merge	a,b	a,b	3,3	NULL	1	Using intersect(a,b); Using where
SELECT a,b,c FROM t1 WHERE a = 2 AND b = 2 AND c IN (13,25,28);
a	b	c
2	2	25
SET SESSION optimizer_switch="index_merge_intersection=off";
SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND  c > 0 AND c < 100;
a	b	c
2	2	25
2	2	17
2	2	20
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND  c > 0 AND c < 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a,b	a	3	const	2	Using where
# Adding more fillers to get index_merge_union
INSERT INTO t1 VALUES (1,1,1+30), (1,1,1+33), (1,1,1+36), (1,1,1+39),
(1,1,1+42), (1,1,1+45), (1,1,1+48), (1,1,1+51), (1,1,1+54), (1,1,1+57),
(1,1,1+60), (1,1,1+63), (1,1,1+66), (1,1,1+69), (1,1,1+72), (1,1,1+75),
(1,1,1+78);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SET SESSION optimizer_switch="index_merge_union=on";
EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND  c > 0 AND c < 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	a,b	b,a	3,3	NULL	4	Using union(b,a); Using where
SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND  c > 0 AND c < 100;
a	b	c
1	2	13
2	2	17
2	2	20
1	2	24
2	2	25
2	1	28
SET SESSION optimizer_switch="index_merge_union=off";
SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND  c > 0 AND c < 100;
a	b	c
1	2	24
1	2	13
2	2	25
2	1	28
2	2	17
2	2	20
EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND  c > 0 AND c < 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	a,b	NULL	NULL	NULL	27	Using where
Insert a few more rows to trigger sort_union
INSERT INTO t1 VALUES (1,1,1+81), (1,1,1+84), (1,1,1+87), (1,1,1+90),
(1,1,1+93);
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SET SESSION optimizer_switch="index_merge_sort_union=on";
EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND  c > 0 AND c < 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	a,b	b,a	3,3	NULL	4	Using sort_union(b,a); Using where
# Not affected, added for completeness...
SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND  c > 0 AND c < 100;
a	b	c
1	2	13
2	2	17
2	2	20
1	2	24
2	2	25
2	1	28
SET SESSION optimizer_switch="index_merge_sort_union=off";
SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND  c > 0 AND c < 100;
a	b	c
1	2	24
1	2	13
2	2	25
2	1	28
2	2	17
2	2	20
EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND  c > 0 AND c < 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	a,b	NULL	NULL	NULL	32	Using where
SET @@session.optimizer_switch = @old_opt_switch;
DROP TABLE t1;