Wrong result with sort_union/index_merge in maria-5.1 and a large table

[Philip Stoev]

The following query:

SELECT * FROM `table100000_innodb_int_autoinc` WHERE ( ( `col_bigint_key` >= 1 ) OR `pk` BETWEEN 8 AND 40 + 64 ) OR ( `pk` BETWEEN 1 AND 1 + 185 AND `col_bigint_key` IS NULL )

Returns a different number of rows when executed with an index_merge/sort_union strategy.

[Philip Stoev (pstoev-askmonty)]

The test case appears to require at least 10K rows to work. Further simplification was not possible.

[Philip Stoev (pstoev-askmonty)]

This is also repeatable with maria-5.1

[Philip Stoev (pstoev-askmonty)]

To reproduce, please use the following command:

 perl mysql-test-run.pl --record --no-check-testcases bug640419.test

and then manually examine the .result file to see the different query results.

If you want the test case to classically fail if the bug is present, remove the "--exit" in the test case that is above "--let $result1".

[Philip Stoev (pstoev-askmonty)]

Results:

SELECT COUNT(*) FROM (SELECT * FROM `table100000_innodb_int_autoinc` FORCE KEY ( PRIMARY , `col_smallint_key` , `col_bigint_key` , `col_varchar_10_key` , `col_varchar_64_key` ) WHERE ( ( `col_bigint_key` >= 1 ) OR `pk` BETWEEN 8 AND 40 + 64 ) OR ( `pk` BETWEEN 1 AND 1 + 185 AND `col_bigint_key` IS NULL )) as t1;
COUNT(*)
7027

SELECT COUNT(*) FROM (SELECT * FROM `table100000_innodb_int_autoinc` IGNORE KEY ( PRIMARY , `col_smallint_key` , `col_bigint_key` , `col_varchar_10_key` , `col_varchar_64_key` ) WHERE ( ( `col_bigint_key` >= 1 ) OR `pk` BETWEEN 8 AND 40 + 64 ) OR ( `pk` BETWEEN 1 AND 1 + 185 AND `col_bigint_key` IS NULL )) as t1;
COUNT(*)
7124

SELECT COUNT(*) FROM (SELECT * FROM `table100000_innodb_int_autoinc` WHERE ( ( `col_bigint_key` >= 1 ) OR `pk` BETWEEN 8 AND 40 + 64 ) OR ( `pk` BETWEEN 1 AND 1 + 185 AND `col_bigint_key` IS NULL )) as t1;
COUNT(*)
7124

Explain plans:

EXPLAIN SELECT * FROM `table100000_innodb_int_autoinc` FORCE KEY ( PRIMARY , `col_smallint_key` , `col_bigint_key` , `col_varchar_10_key` , `col_varchar_64_key` ) WHERE ( ( `col_bigint_key` >= 1 ) OR `pk` BETWEEN 8 AND 40 + 64 ) OR ( `pk` BETWEEN 1 AND 1 + 185 AND `col_bigint_key` IS NULL );

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table100000_innodb_int_autoinc index_merge PRIMARY,col_bigint_key col_bigint_key,PRIMARY 9,4 NULL 8439 Using sort_union(col_bigint_key,PRIMARY); Using where

EXPLAIN SELECT * FROM `table100000_innodb_int_autoinc` IGNORE KEY ( PRIMARY , `col_smallint_key` , `col_bigint_key` , `col_varchar_10_key` , `col_varchar_64_key` ) WHERE ( ( `col_bigint_key` >= 1 ) OR `pk` BETWEEN 8 AND 40 + 64 ) OR ( `pk` BETWEEN 1 AND 1 + 185 AND `col_bigint_key` IS NULL );
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table100000_innodb_int_autoinc ALL NULL NULL NULL NULL 12638 Using where

EXPLAIN SELECT * FROM `table100000_innodb_int_autoinc` WHERE ( ( `col_bigint_key` >= 1 ) OR `pk` BETWEEN 8 AND 40 + 64 ) OR ( `pk` BETWEEN 1 AND 1 + 185 AND `col_bigint_key` IS NULL );
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table100000_innodb_int_autoinc ALL PRIMARY,col_bigint_key NULL NULL NULL 12638 Using where

[Philip Stoev (pstoev-askmonty)]

Test case with a smaller table, requiring a small sort_buffer_size. Also, only FORCE and IGNORE KEY queries are used to avoid any ambiguity

[Philip Stoev (pstoev-askmonty)]

The procedure to repeat is as follows:

0. Take a 64-bit machine

1. Branch a fresh tree

bzr branch lp:maria/5.1 maria-5.1-bug637962

2. Clear ccache

ccache -C

3. Compile

 ./BUILD/compile-pentium-debug-max

4. Run.

perl mysql-test-run.pl --record --no-check-testcases t/bug640419-2.test

If successfull, MTR will report

mysqltest: At line 1462: "Bug is repeatable"

[Igor Babaev (igorb-seattle)]

The bug can be demonstrated on any platform with the following test case:

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;
SELECT COUNT(*) FROM
  (SELECT * FROM t1
     WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;

EXPLAIN
SELECT COUNT(*) FROM
  (SELECT * FROM t1 IGNORE INDEX(idx)
     WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
SELECT COUNT(*) FROM
  (SELECT * FROM t1 IGNORE INDEX(idx)
     WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;

Running this test case one can see that the execution with index merge returns a wrong result:

MariaDB [test]> 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 |
+----+-------------+-------+-------------+---------------+-------------+---------+------+-------+--------------------------------------------+
2 rows in set (0.12 sec)

MariaDB [test]> SELECT COUNT(*) FROM
    -> (SELECT * FROM t1
    -> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
+----------+
| COUNT(*) |
+----------+
| 6144 |
+----------+
1 row in set (0.12 sec)

MariaDB [test]> 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 ...

[Igor Babaev (igorb-seattle)]

The bug has just been reported for mysql-5.1 as bug #56862