Re: Wrong result with sort_union/index_merge in maria-5.1-wl24 and a large table
The test case appears to require at least 10K rows to work. Further simplification was not possible.

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

Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table
This is also repeatable with maria-5.1

Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table
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".

Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table
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

Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table
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

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
test case
LPexportBug640419_bug640419-2.test

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

Diff between the FORCE KEY and IGNORE KEY result sets
LPexportBug640419_bug640419.diff

Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table
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"

Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table
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;
-----------------------------------------------------------------------------------------------------------------+

-----------------------------------------------------------------------------------------------------------------+

-----------------------------------------------------------------------------------------------------------------+
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

----------

----------
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;
--------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------+
2 rows in set (0.50 sec)

MariaDB [test]> SELECT COUNT FROM
-> (SELECT * FROM t1 IGNORE INDEX(idx)
-> WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
----------

COUNT

----------

----------
1 row in set (0.51 sec)

Re: Wrong result with sort_union/index_merge in maria-5.1 and a large table
The bug has just been reported for mysql-5.1 as bug #56862

Launchpad bug id: 640419