diff options
Diffstat (limited to 'mysql-test/r/index_merge_innodb.result')
| -rw-r--r-- | mysql-test/r/index_merge_innodb.result | 55 |
1 files changed, 55 insertions, 0 deletions
diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index 8bbbed2865f..3d3f0e433d5 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -255,6 +255,61 @@ WHERE `RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND `TESTID`='' AND `UCCHECK`=''; drop table t1; +# +# BUG#56862/640419: Wrong result with sort_union index merge when one +# of the merged index scans is the primary key scan +# +CREATE TABLE t1 ( +pk int NOT NULL AUTO_INCREMENT PRIMARY KEY, +a int, +b int, +INDEX idx(a)) +ENGINE=INNODB; +INSERT INTO t1(a,b) VALUES +(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500), +(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800), +(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700), +(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000); +INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1; +INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1; +INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1 VALUES (1000000, 0, 0); +SET SESSION sort_buffer_size = 1024*36; +EXPLAIN +SELECT COUNT(*) FROM +(SELECT * FROM t1 +WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 11419 Using sort_union(idx,PRIMARY); Using where +SELECT COUNT(*) FROM +(SELECT * FROM t1 +WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +COUNT(*) +6145 +EXPLAIN +SELECT COUNT(*) FROM +(SELECT * FROM t1 IGNORE INDEX(idx) +WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL # Select tables optimized away +2 DERIVED t1 ALL PRIMARY NULL NULL NULL # Using where +SELECT COUNT(*) FROM +(SELECT * FROM t1 IGNORE INDEX(idx) +WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t; +COUNT(*) +6145 +DROP TABLE t1; # # Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB # |