1--source include/have_partition.inc 2--source include/have_innodb_16k.inc 3 4--echo # 5--echo # Bug#18167648: WRONG RESULTS WITH PARTITIONING, INDEX_MERGE AND NO PK 6--echo # 7CREATE TABLE t1 8(a smallint, 9 b smallint, 10 c smallint, 11 KEY a (a), 12 KEY b (b) 13) ENGINE=InnoDB 14PARTITION BY HASH(c) PARTITIONS 3; 15 16--echo # c will simulate DB_ROW_ID + force to chosen partition. 17--echo # c is unique so we can identify rows in the test. 18--echo # Fillers to make the optimizer choose index_merge_intersect/union: 19INSERT INTO t1 VALUES (1,1,1), (1,1,1+3), (1,1,1+6), (1,1,1+9); 20--echo # row N..N+3 21INSERT INTO t1 VALUES (1,2,1+12), (2,2,2+15), (2,2,2+18), (1,2,3+21); 22--echo # More index matching rows for index_merge_intersect: N+4, N+5 23INSERT INTO t1 VALUES (2,2,1+24); 24INSERT INTO t1 VALUES (2,1,1+27); 25ANALYZE TABLE t1; 26 27SET @old_opt_switch = @@session.optimizer_switch; 28SET SESSION optimizer_switch="index_merge=on"; 29SET SESSION optimizer_switch="index_merge_intersection=on"; 30SET SESSION optimizer_switch="index_merge_sort_union=off"; 31SET SESSION optimizer_switch="index_merge_union=off"; 32if ($use_optimizer_trace) 33{ 34SET SESSION optimizer_trace="enabled=on"; 35} 36EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100; 37if ($use_optimizer_trace) 38{ 39SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 40} 41# Before fix: 42# Index start of scan (ha_partition::handle_ordered_index_scan) 43# 0xNNN is the DB_ROW_ID, kind of internal global auto increment. 44# a reads row N+4 (0x208) from p1 c = 25 45# a reads row N+1 (0x205) from p2 c = 17 46# a returns row N+4, c = 25 (All same, first insert, no cmp of ref/DB_ROW_ID!) 47# b reads row N+3 (0x207) from p0 c = 24 48# b reads row N (0x204) from p1 c = 13 49# b reads row N+1 (0x205) from p2 c = 17 50# b returns row N+3, c = 24 (All same, first insert, no cmp of ref/DB_ROW_ID!) 51# b skips to next (0x207 < 0x208) 52# b finds no more rows in p0. 53# b returns row from p2 (0x205) c = 17 54# b skips to next row (0x205 < 0x208) 55# b reads row N+2 (0x206) from p2 c = 20 56# b skips to next row (0x206 < 0x208) 57# b finds no more rows in p2. 58# b return row from p1 (0x204) c = 13 59# b skips to next row (0x204 < 0x208) 60# b reads row N+4 (0x208) from p1 c = 25 61# a == b Match found! 62# Fetches whole row for 0x208 (really p1 + 0x208) -> (2,2,25) 63# Continues with next a: 64# a reads row N+5 (0x209) from p1 c = 28 65# No more match... 66 67SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100; 68EXPLAIN PARTITIONS SELECT a,b,c FROM t1 WHERE a = 2 AND b = 2 AND c IN (13,25,28); 69SELECT a,b,c FROM t1 WHERE a = 2 AND b = 2 AND c IN (13,25,28); 70SET SESSION optimizer_switch="index_merge_intersection=off"; 71SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100; 72EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND c > 0 AND c < 100; 73 74--echo # Adding more fillers to get index_merge_union 75INSERT INTO t1 VALUES (1,1,1+30), (1,1,1+33), (1,1,1+36), (1,1,1+39), 76(1,1,1+42), (1,1,1+45), (1,1,1+48), (1,1,1+51), (1,1,1+54), (1,1,1+57), 77(1,1,1+60), (1,1,1+63), (1,1,1+66), (1,1,1+69), (1,1,1+72), (1,1,1+75), 78(1,1,1+78); 79ANALYZE TABLE t1; 80SET SESSION optimizer_switch="index_merge_union=on"; 81EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100; 82if ($use_optimizer_trace) 83{ 84SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 85} 86# First there where a bug where index_init() was called with sorted=false :( 87# Before fix of secondary sort in ha_partition: 88# b reads row N+3 (0x207) from p0 c = 24 89# b reads row N (0x204) from p1 c = 13 90# b reads row N+1 (0x205) from p2 c = 17 91# b returns row N+3, c = 24 (All same, first insert, no cmp of ref/DB_ROW_ID!) 92# a reads row N+4 (0x208) from p1 c = 25 93# a reads row N+1 (0x205) from p2 c = 17 94# a returns row N+4, c = 25 (All same, first insert, no cmp of ref/DB_ROW_ID!) 95 96# first row from both indexes done, first is b, 0x207 c = 24 97# Get next from b 98# b finds no more rows in p0. 99# b returns row from p2 (0x205) c = 17 100# Fetching whole row for (0x207) c = 24 (1,2,24) and sends it 101# b reads row N+2 (0x206) from p2 c = 20 102# b returns row from p2 (0x206) c = 20 103# No duplicate (0x206 != 0x208) 104# Fetching whole row for (0x205) c = 17 (2,2,17) and sends it 105# b finds no more rows in p2 106# b returns row from p1 (0x204) c = 13 107# No duplicate (0x205 != 0x206) 108# Fetching whole row for (0x206) c = 20 (2,2,20) and sends it 109# b reads row N+4 (0x208) from p1 c = 25 110# b returns row from p1 (0x208) c = 25 111# No duplicate (0x204 != 0x206) 112# Fetching whole row for (0x204) c = 13 (1,2,13) and sends it 113# Fetching whole row for (0x208) c = 25 (2,2,25) 114# a reads row N+5 (0x209) from p1 c = 28 115# a returns row from p1 (0x209) c = 28 116# Duplicate (0x209 == 0x209) 117# a returns row from p2 (0x205) 118# Fetching whole row for (0x209) c = 28 (2,1,28) 119# a reads row N+2 (0x206) from p2 c = 20 120# Fetching whole row for (0x205) c = 17 (2,2,17) 121# Fetching whole row for (0x206) c = 20 (2,2,20) 122 123SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100; 124SET SESSION optimizer_switch="index_merge_union=off"; 125SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100; 126EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND c > 0 AND c < 100; 127 128--echo Insert a few more rows to trigger sort_union 129INSERT INTO t1 VALUES (1,1,1+81), (1,1,1+84), (1,1,1+87), (1,1,1+90), 130(1,1,1+93); 131ANALYZE TABLE t1; 132SET SESSION optimizer_switch="index_merge_sort_union=on"; 133EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100; 134if ($use_optimizer_trace) 135{ 136SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 137SET SESSION optimizer_trace="enabled=off"; 138} 139--echo # Not affected, added for completeness... 140SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100; 141SET SESSION optimizer_switch="index_merge_sort_union=off"; 142SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100; 143EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND c > 0 AND c < 100; 144 145SET @@session.optimizer_switch = @old_opt_switch; 146DROP TABLE t1; 147