MySQL Bugs: #70703: InnoDB index intersection returns less results than expected (again)

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

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 ;

Thank you for the bug report.

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.

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)

see also Bug 18167648 - WRONG RESULTS WITH PARTITIONING AND INDEX_MERGE

It's ok on 5.6.16 too.

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.

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