Description:
Similar to Bug #29740, an incorrect result is returned from an index_merge query but this time it only occurs on partitioned tables. I will post a test database that has data distributed across partitions that reproduces the error. I tried tables of 100, 1000, and 10,000 rows and only could reproduce with the 10,000 row table.
How to repeat:
mysql> show variables like 'version%';
+-------------------------+-------------------------------+
| Variable_name | Value |
+-------------------------+-------------------------------+
| version | 5.1.22-rc-community-log |
| version_comment | MySQL Community Edition (GPL) |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
+-------------------------+-------------------------------+
4 rows in set (0.00 sec)
mysql> explain select * from test_partitions where a=19372095 or b=19372095;
+----+-------------+-----------------+-------------+---------------+------+---------+------+------+-------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------------+---------------+------+---------+------+------+-------------------------------+
| 1 | SIMPLE | test_partitions | index_merge | a,b | a,b | 4,4 | NULL | 190 | Using union(a,b); Using where |
+----+-------------+-----------------+-------------+---------------+------+---------+------+------+-------------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from test_partitions where a=19372095 or b=19372095;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
mysql> explain select count(*) from test_partitions ignore index(a,b) where a=19372095 or b=19372095;
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | test_partitions | ALL | NULL | NULL | NULL | NULL | 10000 | Using where |
+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> select count(*) from test_partitions ignore index(a,b) where a=19372095 or b=19372095;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE test_no_partitions (
-> anID bigint(20) unsigned NOT NULL auto_increment,
-> ThisID tinyint(1) UNSIGNED NOT NULL,
-> Timestamp int(10) unsigned NOT NULL,
-> a int(10) unsigned NOT NULL,
-> b int(10) unsigned NOT NULL,
-> PRIMARY KEY (anID,ThisID,Timestamp),
-> INDEX (ThisID),
-> INDEX (Timestamp),
-> INDEX (a),
-> INDEX (b)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)
mysql> insert test_no_partitions select * from test_partitions;
Query OK, 10000 rows affected (0.12 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> explain select * from test_no_partitions where a=19372095 or b=19372095;
+----+-------------+--------------------+-------------+---------------+------+---------+------+------+-------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+-------------+---------------+------+---------+------+------+-------------------------------+
| 1 | SIMPLE | test_no_partitions | index_merge | a,b | a,b | 4,4 | NULL | 2 | Using union(a,b); Using where |
+----+-------------+--------------------+-------------+---------------+------+---------+------+------+-------------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from test_no_partitions where a=19372095 or b=19372095;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql>
Suggested fix:
Workaround is to not use partitioning, or force MySQL to ignore the offending indexes with "ignore index" as in the example.
