MySQL Bugs: #70182: some data are missing in a SELECT with a partitioned table

Bug #70182	some data are missing in a SELECT with a partitioned table
Submitted:	29 Aug 2013 11:31	Modified:	30 Sep 2013 15:51
Reporter:	Davide Marrone	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.6.12-enterprise-commercial-advanced-lo	OS:	Linux (RHEL 6.4)
Assigned to:		CPU Architecture:	Any

Description:
with table:
CREATE TABLE `pay_sms_message` (
  `id_message` int(10) unsigned NOT NULL,
  `partition_date` int(10) unsigned NOT NULL,
  `fk_dispatch` int(10) unsigned NOT NULL,
  `fk_customer` int(10) unsigned NOT NULL,
  `fk_customer_applicant` int(10) unsigned NOT NULL DEFAULT '0',
  `recipient` bigint(20) unsigned NOT NULL,
  `fk_operator` tinyint(3) unsigned DEFAULT NULL,
  `customer_cost` decimal(8,6) unsigned NOT NULL DEFAULT '0.000000',
  `customer_vat_rate` decimal(3,1) unsigned NOT NULL DEFAULT '21.0',
  `sent_type` enum('fixed','dynamic') NOT NULL,
  `forward_report` tinyint(1) DEFAULT NULL,
  `segments` smallint(1) unsigned NOT NULL DEFAULT '1',
  `length` smallint(5) unsigned NOT NULL DEFAULT '0',
  `customer_ref` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id_message`,`partition_date`),
  KEY `recipient` (`recipient`),
  KEY `fk_dispatch` (`fk_dispatch`),
  KEY `fk_customer` (`fk_customer`),
  KEY `fk_customer_applicant` (`fk_customer_applicant`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (partition_date)
(PARTITION p2013060100 VALUES LESS THAN (2013060100) ENGINE = InnoDB,
 PARTITION p2013060500 VALUES LESS THAN (2013060500) ENGINE = InnoDB,
 PARTITION p2013061000 VALUES LESS THAN (2013061000) ENGINE = InnoDB,
 PARTITION p2013061500 VALUES LESS THAN (2013061500) ENGINE = InnoDB,
 PARTITION p2013062000 VALUES LESS THAN (2013062000) ENGINE = InnoDB,
 PARTITION p2013062500 VALUES LESS THAN (2013062500) ENGINE = InnoDB,
 PARTITION p2013070100 VALUES LESS THAN (2013070100) ENGINE = InnoDB,
 PARTITION p2013070500 VALUES LESS THAN (2013070500) ENGINE = InnoDB,
 PARTITION p2013071000 VALUES LESS THAN (2013071000) ENGINE = InnoDB,
 PARTITION p2013071500 VALUES LESS THAN (2013071500) ENGINE = InnoDB,
 PARTITION p2013072000 VALUES LESS THAN (2013072000) ENGINE = InnoDB,
 PARTITION p2013072500 VALUES LESS THAN (2013072500) ENGINE = InnoDB,
 PARTITION p2013080100 VALUES LESS THAN (2013080100) ENGINE = InnoDB,
 PARTITION p2013080500 VALUES LESS THAN (2013080500) ENGINE = InnoDB,
 PARTITION p2013081000 VALUES LESS THAN (2013081000) ENGINE = InnoDB,
 PARTITION p2013081500 VALUES LESS THAN (2013081500) ENGINE = InnoDB,
 PARTITION p2013082000 VALUES LESS THAN (2013082000) ENGINE = InnoDB,
 PARTITION p2013082500 VALUES LESS THAN (2013082500) ENGINE = InnoDB,
 PARTITION p2013090100 VALUES LESS THAN (2013090100) ENGINE = InnoDB,
 PARTITION p2013090500 VALUES LESS THAN (2013090500) ENGINE = InnoDB,
 PARTITION p2013091000 VALUES LESS THAN (2013091000) ENGINE = InnoDB,
 PARTITION p2013091500 VALUES LESS THAN (2013091500) ENGINE = InnoDB,
 PARTITION p2013092000 VALUES LESS THAN (2013092000) ENGINE = InnoDB,
 PARTITION p2013092500 VALUES LESS THAN (2013092500) ENGINE = InnoDB,
 PARTITION p2013100100 VALUES LESS THAN (2013100100) ENGINE = InnoDB) */
1 row in set (0.01 sec)

this version of mysql: 5.6.12-enterprise-commercial-advanced-log

The table has about 11000000 or rows 

Query the table to get some (correct data)

== query that extract some data from the table, this query it's correct, no problem with it
SELECT m.id_message, m.partition_date, m.fk_dispatch, m.fk_customer_applicant
FROM pay_sms_message m
WHERE m.recipient =39333344555
ORDER BY m.fk_dispatch DESC
LIMIT 0 , 20;

mysql> SELECT m.id_message, m.partition_date, m.fk_dispatch, m.fk_customer_applicant
    -> FROM pay_sms_message m
    -> WHERE m.recipient =39333344555
    -> ORDER BY m.fk_dispatch DESC
    -> LIMIT 0 , 20
    -> ;
+------------+----------------+-------------+-----------------------+
| id_message | partition_date | fk_dispatch | fk_customer_applicant |
+------------+----------------+-------------+-----------------------+
|104492215|2013082713|60883962|2578791|
|104451004|2013082706|60865140|2578791|
|104418866|2013082616|60850239|2578791|
|104309688|2013082412|60800542|2578791|
|104304589|2013082410|60797569|2578791|
|104261828|2013082314|60775551|2578791|
|104257897|2013082313|60773578|2578791|
|104236002|2013082311|60760151|2578791|
|104163195|2013082220|60723448|2578791|
|104038771|2013082209|60653335|2578791|
|104028639|2013082207|60647825|2578791|
|103987460|2013082111|60621862|2578791|
|103944289|2013082015|60597144|2578791|
|103853385|2013081914|60547435|2578791|
|103826662|2013081910|60532014|2578791|
|103772300|2013081714|60505010|2578791|
|103769832|2013081712|60503751|2578791|
|103768192|2013081711|60502733|2578791|
|103721815|2013081616|60486857|2578791|
|103718424|2013081615|60486340|2578791|
+------------+----------------+-------------+-----------------------+
20 rows in set (0.01 sec)

mysql> explain partitions SELECT m.id_message, m.partition_date, m.fk_dispatch, m.fk_customer_applicant
    -> FROM pay_sms_message m
    -> WHERE m.recipient =39333344555
    -> ORDER BY m.fk_dispatch DESC
    -> LIMIT 0 , 20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
   partitions: p2013060100,p2013060500,p2013061000,p2013061500,p2013062000,p2013062500,p2013070100,p2013070500,p2013071000,p2013071500,p2013072000,p2013072500,p2013080100,p2013080500,p2013081000,p2013081500,p2013082000,p2013082500,p2013090100,p2013090500,p2013091000,p2013091500,p2013092000,p2013092500,p2013100100
         type: ref
possible_keys: recipient
          key: recipient
      key_len: 8
          ref: const
         rows: 164
        Extra: Using where; Using filesort
1 row in set (0.01 sec)

== PROBLEM
If in the query is added another constraint it return less data, but as you can se from above
the column fk_customer_applicant is always = 2578791 

SELECT m.id_message, m.partition_date, m.fk_dispatch, m.fk_customer_applicant
FROM pay_sms_message m
WHERE m.recipient =39333344555
AND fk_customer_applicant =2578791		# constraint added
ORDER BY m.fk_dispatch DESC
LIMIT 0 , 20;

mysql> SELECT m.id_message, m.partition_date, m.fk_dispatch, m.fk_customer_applicant
    -> FROM pay_sms_message m
    -> WHERE m.recipient =39333344555
    -> AND fk_customer_applicant =2578791
    -> ORDER BY m.fk_dispatch DESC
    -> LIMIT 0 , 20;
+------------+----------------+-------------+-----------------------+
| id_message | partition_date | fk_dispatch | fk_customer_applicant |
+------------+----------------+-------------+-----------------------+
|104492215|2013082713|60883962|2578791|
|104451004|2013082706|60865140|2578791|
|104418866|2013082616|60850239|2578791|
|  93764890|2013053117|56926693|2578791|
|  93636172|2013053110|56891922|2578791|
|  93609419|2013053106|56881519|2578791|
|  93537077|2013053015|56867546|2578791|
|  93495607|2013053009|56851473|2578791|
|  93488100|2013053009|56848406|2578791|
|  93386467|2013052911|56814812|2578791|
|  93338441|2013052906|56790039|2578791|
|  93110075|2013052707|56691400|2578791|
|  93033805|2013052517|56666384|2578791|
|  93017207|2013052514|56660437|2578791|
+------------+----------------+-------------+-----------------------+
14 rows in set (0.08 sec)
mysql> explain partitions SELECT m.id_message, m.partition_date, m.fk_dispatch, m.fk_customer_applicant
    -> FROM pay_sms_message m
    -> WHERE m.recipient =39333344555
    -> AND fk_customer_applicant =2578791
    -> ORDER BY m.fk_dispatch DESC
    -> LIMIT 0 , 20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: m
   partitions: p2013060100,p2013060500,p2013061000,p2013061500,p2013062000,p2013062500,p2013070100,p2013070500,p2013071000,p2013071500,p2013072000,p2013072500,p2013080100,p2013080500,p2013081000,p2013081500,p2013082000,p2013082500,p2013090100,p2013090500,p2013091000,p2013091500,p2013092000,p2013092500,p2013100100
         type: index_merge
possible_keys: recipient,fk_customer_applicant
          key: recipient,fk_customer_applicant
      key_len: 8,4
          ref: NULL
         rows: 1
        Extra: Using intersect(recipient,fk_customer_applicant); Using where; Using filesort
1 row in set (0.00 sec)

How to repeat:
Unable to reproduce with only 200 rows of data

Workaround with subquery

SELECT id_message, partition_date, fk_dispatch, fk_customer_applicant
FROM (
SELECT m.id_message, m.partition_date, m.fk_dispatch, m.fk_customer_applicant
FROM pay_sms_message m
WHERE m.recipient =39333344555
ORDER BY m.fk_dispatch DESC
) as a WHERE
a.fk_customer_applicant =2578791

update severity

another workaround is

SET optimizer_switch = 'index_merge_intersection=off';

with this flag the bug disappear

Likely a duplicate of a internal bug which is fixed in 5.6.14
Bug 16862316 - QUERY RETURNS DIFFERENT RESULTS DEPENDING ON WHETHER INDEX_MERGE SETTING
Testcase is:
------
drop table if exists t1;
create table t1 (a int,b int,c int,primary key (c),key (a),key (b)) engine=innodb
partition by hash (c) partitions 3;
insert into t1 values (0, 0, 0), (0, 0, 3), (0, 0, 1);
insert into t1 values (1, 0, 6), (1, 0, 9), (1, 0, 7);
insert into t1 values (0, 1, 12), (0, 1, 15), (0, 1, 13);
set @@optimizer_switch='index_merge=on';
select a, b, c from t1 where a = 0 and b = 0 and c between 0 and 10;
set @@optimizer_switch='index_merge=off';
select a, b, c from t1 where a = 0 and b = 0 and c between 0 and 10;
select version();

I have the same problem, but I don't know to reproduce that.

I'm wating for anxious for the fix.

Take a look:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.13-log |
+------------+
1 row in set (0.00 sec)

mysql> select count(id) from mydoors where user_id=135385 and change=1;                                                                                                                                                                                                 
+-----------+
| count(id) |
+-----------+
|         1 |
+-----------+
1 row in set (0.02 sec)

mysql> select count(id) from mydoors where user_id=135385 and change>0;
+-----------+
| count(id) |
+-----------+
|        40 |
+-----------+
1 row in set (0.02 sec)