partition_index_innodb.test

--source include/have_innodb.inc
--source include/have_partition.inc
--source include/have_innodb_16k.inc

--echo #
--echo # Bug#18167648: WRONG RESULTS WITH PARTITIONING, INDEX_MERGE AND NO PK
--echo #
CREATE TABLE t1
(a smallint,
 b smallint,
 c smallint,
 KEY  a (a),
 KEY  b (b)
) ENGINE=InnoDB
PARTITION BY HASH(c) PARTITIONS 3;

--echo # c will simulate DB_ROW_ID + force to chosen partition.
--echo # c is unique so we can identify rows in the test.
--echo # 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);
--echo # row N..N+3
INSERT INTO t1 VALUES (1,2,1+12), (2,2,2+15), (2,2,2+18), (1,2,3+21);
--echo # 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);
CREATE TABLE t2 (a int primary key) ENGINE = InnoDB;
INSERT INTO t2 VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
INSERT INTO t1 SELECT 1, 1, 97 FROM t2 LIMIT 10;
INSERT INTO t1 SELECT 2, 1, 98 FROM t2 LIMIT 4;
INSERT INTO t1 SELECT 1, 2, 99 FROM t2 LIMIT 4;
ANALYZE TABLE t1;

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";
if ($use_optimizer_trace)
{
SET SESSION optimizer_trace="enabled=on";
}
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND  c > 0 AND c < 100;
if ($use_optimizer_trace)
{
SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
}
# Before fix:
# Index start of scan (ha_partition::handle_ordered_index_scan)
# 0xNNN is the DB_ROW_ID, kind of internal global auto increment.
# a reads row N+4 (0x208) from p1 c = 25
# a reads row N+1 (0x205) from p2 c = 17
# a returns row N+4, c = 25 (All same, first insert, no cmp of ref/DB_ROW_ID!)
# b reads row N+3 (0x207) from p0 c = 24
# b reads row N   (0x204) from p1 c = 13
# b reads row N+1 (0x205) from p2 c = 17
# b returns row N+3, c = 24 (All same, first insert, no cmp of ref/DB_ROW_ID!)
# b skips to next (0x207 < 0x208)
# b finds no more rows in p0.
# b returns row from p2 (0x205) c = 17
# b skips to next row (0x205 < 0x208)
# b reads row N+2 (0x206) from p2 c = 20
# b skips to next row (0x206 < 0x208)
# b finds no more rows in p2.
# b return row from p1 (0x204) c = 13
# b skips to next row (0x204 < 0x208)
# b reads row N+4 (0x208) from p1 c = 25
# a == b Match found!
# Fetches whole row for 0x208 (really p1 + 0x208) -> (2,2,25)
# Continues with next a:
# a reads row N+5 (0x209) from p1 c = 28
# No more match...

SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND  c > 0 AND c < 100;
EXPLAIN SELECT a,b,c FROM t1 WHERE a = 2 AND b = 2 AND c IN (13,25,28);
SELECT a,b,c FROM t1 WHERE a = 2 AND b = 2 AND c IN (13,25,28);
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;
EXPLAIN SELECT a,b,c FROM t1 WHERE b = 2 AND a = 2 AND  c > 0 AND c < 100;

--echo # Adding more fillers to get index_merge_union
INSERT INTO t1 SELECT 1, 1, 97 FROM t2, t2 t3 LIMIT 32;
ANALYZE TABLE t1;
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;
if ($use_optimizer_trace)
{
SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
}
# First there where a bug where index_init() was called with sorted=false :(
# Before fix of secondary sort in ha_partition:
# b reads row N+3 (0x207) from p0 c = 24
# b reads row N   (0x204) from p1 c = 13
# b reads row N+1 (0x205) from p2 c = 17
# b returns row N+3, c = 24 (All same, first insert, no cmp of ref/DB_ROW_ID!)
# a reads row N+4 (0x208) from p1 c = 25
# a reads row N+1 (0x205) from p2 c = 17
# a returns row N+4, c = 25 (All same, first insert, no cmp of ref/DB_ROW_ID!)

# first row from both indexes done, first is b, 0x207 c = 24
# Get next from b
# b finds no more rows in p0.
# b returns row from p2 (0x205) c = 17
# Fetching whole row for (0x207) c = 24 (1,2,24) and sends it
# b reads row N+2 (0x206) from p2 c = 20
# b returns row from p2 (0x206) c = 20
# No duplicate (0x206 != 0x208)
# Fetching whole row for (0x205) c = 17 (2,2,17) and sends it
# b finds no more rows in p2
# b returns row from p1 (0x204) c = 13
# No duplicate (0x205 != 0x206)
# Fetching whole row for (0x206) c = 20 (2,2,20) and sends it
# b reads row N+4 (0x208) from p1 c = 25
# b returns row from p1 (0x208) c = 25
# No duplicate (0x204 != 0x206)
# Fetching whole row for (0x204) c = 13 (1,2,13) and sends it
# Fetching whole row for (0x208) c = 25 (2,2,25)
# a reads row N+5 (0x209) from p1 c = 28
# a returns row from p1 (0x209) c = 28
# Duplicate (0x209 == 0x209)
# a returns row from p2 (0x205)
# Fetching whole row for (0x209) c = 28 (2,1,28)
# a reads row N+2 (0x206) from p2 c = 20
# Fetching whole row for (0x205) c = 17 (2,2,17)
# Fetching whole row for (0x206) c = 20 (2,2,20)

SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND  c > 0 AND c < 100;
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;
EXPLAIN SELECT a,b,c FROM t1 WHERE (b = 2 OR a = 2) AND  c > 0 AND c < 100;

--echo Insert a few more rows to trigger sort_union
INSERT INTO t1 SELECT 1, 1, 97 FROM t2, t2 t3 LIMIT 48;
ANALYZE TABLE t1;
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;
if ($use_optimizer_trace)
{
SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET SESSION optimizer_trace="enabled=off";
}
--echo # Not affected, added for completeness...
SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND  c > 0 AND c < 100;
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;
EXPLAIN SELECT a,b,c FROM t1 WHERE (b >= 2 OR a >= 2) AND  c > 0 AND c < 100;

SET @@session.optimizer_switch = @old_opt_switch;
DROP TABLE t1, t2;