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

[29 Aug 2013 11:31] Davide Marrone 
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
[29 Aug 2013 13:10] Davide Marrone 
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
[29 Aug 2013 13:10] Davide Marrone 
update severity
[29 Aug 2013 13:36] Davide Marrone 
another workaround is

SET optimizer_switch = 'index_merge_intersection=off';

with this flag the bug disappear
[29 Aug 2013 15:26] Shane Bester 
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();
[6 Sep 2013 12:29] Fabricio Fonseca 
I have the same problem, but I don't know to reproduce that.

I'm wating for anxious for the fix.
[6 Sep 2013 12:33] Fabricio Fonseca 
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)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.