Bug #70703 InnoDB index intersection returns less results than expected (again)
Submitted: 23 Oct 2013 14:38 Modified: 20 Feb 2014 19:39
Reporter: Sergey Petrunya Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.14-debug OS:Any
Assigned to: CPU Architecture:Any
Tags: index merge, intersection, partitioning
Triage: Needs Triage: D2 (Serious)

[23 Oct 2013 14:38] Sergey Petrunya
Description:
Fix for Bug#69581 is incomplete. One can still come up with cases where a wrong query result will be produced (most likely, rows will be missing).

How to repeat:
create table t11 (
  a int not null,
  b int not null,
  pk int not null,
  primary key (pk),
  key(a),
  key(b)
) partition by hash(pk) partitions 10;

insert into t11 values (1,2,4); -- both
insert into t11 values (1,0,17);  -- left
insert into t11 values (1,2,25);   -- both

insert into t11 values (10,20,122); 
insert into t11 values (10,20,123);

-- Now, fill in some data so that the optimizer choses index_merg

create table t12 (a int);
insert into t12 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

insert into t11 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t12 A, t12 B, t12 C;

insert into t11 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a,
                       10+A.a + 10*B.a + 100*C.a  + 1000*D.a, 
                       2000 + A.a + 10*B.a + 100*C.a + 1000*D.a
                       from t12 A, t12 B, t12 C ,t12 D;

-- This should show index_merge, using intersect
explain select * from t11 where a=1 and b=2 and  pk between 1 and 999999 ;

-- 794 rows in output
select * from t11 where a=1 and b=2 and  pk between 1 and 999 ;

-- 802 rows in output
select * from t11 ignore index(a,b)  where a=1 and b=2 and  pk between 1 and 999 ;
[23 Oct 2013 15:22] Godofredo Miguel Solorzano
Thank you for the bug report.
[24 Oct 2013 14:54] Mattias Jonsson
Looks like a duplicate of bug#70588.

This is a different bug then the one in bug#69581.

This bug is about ha_partition::cmp_ref is different from order of rows returned from sorted index, which for primary key clustered index tables will do a secondary sort on PK.
[31 Jan 2014 14:35] Arnaud Adant
I can not reproduce in 5.6.17.  This bug should be closed.

-- This should show index_merge, using intersect
explain select * from t11 where a=1 and b=2 and  pk between 1 and 999999 ;

-- 794 rows in output
select * from t11 where a=1 and b=2 and  pk between 1 and 999 ;

gives :

mysql> select count(*) from t11 where a=1 and b=2 and  pk between 1 and 999 ;
+----------+
| count(*) |
+----------+
|      802 |
+----------+
1 row in set (0.00 sec)

mysql> explain select count(*) from t11 where a=1 and b=2 and  pk between 1 and 999\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t11
         type: index_merge
possible_keys: PRIMARY,a,b
          key: b,a
      key_len: 4,4
          ref: NULL
         rows: 91
        Extra: Using intersect(b,a); Using where; Using index
1 row in set (0.00 sec)
[31 Jan 2014 17:30] Arnaud Adant
see also Bug 18167648 - WRONG RESULTS WITH PARTITIONING AND INDEX_MERGE
[7 Feb 2014 9:33] Arnaud Gadal
It's ok on 5.6.16 too.
[20 Feb 2014 19:46] Arnaud Adant
This bug can be closed as the test case can not be reproduced.

Yet the problem still exists in 5.6.16 due to this other bug :

Bug 18167648 - WRONG RESULTS WITH PARTITIONING, INDEX_MERGE MYISAM AND NO PK INNODB 

Reproduced with MyISAM and when there is no PK on InnoDB tables.
[7 Mar 2014 17:08] Igor Babaev
I checked that with the latest build of mysql-5.6 the bug still could be reproduced. To reproduce it I took the test case from  https://mariadb.atlassian.net/browse/MDEV-5177