MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703)

MDEV-5555: Incorrect index_merge on BTREE indices
- In ha_partition, make ordered index reads return rows in rowid order
  when index columns are the same.

3 files changed, 400 insertions, 1 deletions

diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result
index c1cbbffdbfa..521d31ae114 100644
--- a/mysql-test/r/partition.result
+++ b/mysql-test/r/partition.result
@@ -2493,3 +2493,70 @@ i
 3
 4
 DROP TABLE t1;
+#
+# MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703)
+#
+create table t1 (
+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 t1 values (1,2,4);
+insert into t1 values (1,0,17);
+insert into t1 values (1,2,25);
+insert into t1 values (10,20,122);
+insert into t1 values (10,20,123);
+create table t2 (a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+insert into t1 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t2 A, t2 B, t2 C;
+insert into t1 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 t2 A, t2 B, t2 C ,t2 D;
+explain select * from t1 where a=1 and b=2 and  pk between 1 and 999999 ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	PRIMARY,a,b	b	4	const	982	Using where
+create temporary table t3 as
+select * from t1 where a=1 and b=2 and  pk between 1 and 999 ;
+select count(*) from t3;
+count(*)
+802
+drop table t3;
+create temporary table t3 as
+select * from t1 ignore index(a,b)  where a=1 and b=2 and  pk between 1 and 999 ;
+select count(*) from t3;
+count(*)
+802
+drop table t3;
+drop table t1,t2;
+#
+# MDEV-5555: Incorrect index_merge on BTREE indices
+#
+CREATE TABLE t1 (
+id bigint(20) unsigned NOT NULL,
+id2 bigint(20) unsigned NOT NULL,
+dob date DEFAULT NULL,
+address char(100) DEFAULT NULL,
+city char(35) DEFAULT NULL,
+hours_worked_per_week smallint(5) unsigned DEFAULT NULL,
+weeks_worked_last_year tinyint(3) unsigned DEFAULT NULL,
+KEY dob (dob),
+KEY address (address),
+KEY city (city),
+KEY hours_worked_per_week (hours_worked_per_week),
+KEY weeks_worked_last_year (weeks_worked_last_year)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+PARTITION BY KEY (id) PARTITIONS 5;
+# Insert some rows 
+select * from t1 where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21';
+id	id2	dob	address	city	hours_worked_per_week	weeks_worked_last_year
+16	16	1949-11-07	address16	city16	40	52
+50	50	1923-09-08	address50	city50	40	52
+select * from t1 IGNORE INDEX(dob, weeks_worked_last_year, hours_worked_per_week) where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21';
+id	id2	dob	address	city	hours_worked_per_week	weeks_worked_last_year
+16	16	1949-11-07	address16	city16	40	52
+50	50	1923-09-08	address50	city50	40	52
+drop table t1;
diff --git a/mysql-test/r/partition_order.result b/mysql-test/r/partition_order.result
index 78ff7cd3121..06c1b63a382 100644
--- a/mysql-test/r/partition_order.result
+++ b/mysql-test/r/partition_order.result
@@ -752,8 +752,8 @@ select * from t1 force index (b) where b < 10 ORDER BY b DESC;
 a	b
 6	6
 4	5
-2	4
 30	4
+2	4
 3	3
 35	2
 7	1
diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test
index 038907702d5..63f29ffd978 100644
--- a/mysql-test/t/partition.test
+++ b/mysql-test/t/partition.test
@@ -2494,3 +2494,335 @@ INSERT INTO t1 VALUES (1),(2),(2),(3),(4);
 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
 SELECT * from t1 order by i;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-5177: ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703)
+--echo #
+create table t1 (
+  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 t1 values (1,2,4); # both
+insert into t1 values (1,0,17);  # left
+insert into t1 values (1,2,25);   # both
+
+insert into t1 values (10,20,122); 
+insert into t1 values (10,20,123);
+
+# Now, fill in some data so that the optimizer choses index_merge
+create table t2 (a int);
+insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+insert into t1 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t2 A, t2 B, t2 C;
+
+insert into t1 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 t2 A, t2 B, t2 C ,t2 D;
+
+# This should show index_merge, using intersect
+explain select * from t1 where a=1 and b=2 and  pk between 1 and 999999 ;
+# 794 rows in output
+create temporary table t3 as
+select * from t1 where a=1 and b=2 and  pk between 1 and 999 ;
+select count(*) from t3;
+drop table t3;
+
+# 802 rows in output
+create temporary table t3 as
+select * from t1 ignore index(a,b)  where a=1 and b=2 and  pk between 1 and 999 ;
+select count(*) from t3;
+drop table t3;
+
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-5555: Incorrect index_merge on BTREE indices
+--echo #
+
+CREATE TABLE t1 (
+  id bigint(20) unsigned NOT NULL,
+  id2 bigint(20) unsigned NOT NULL,
+  dob date DEFAULT NULL,
+  address char(100) DEFAULT NULL,
+  city char(35) DEFAULT NULL,
+  hours_worked_per_week smallint(5) unsigned DEFAULT NULL,
+  weeks_worked_last_year tinyint(3) unsigned DEFAULT NULL,
+  KEY dob (dob),
+  KEY address (address),
+  KEY city (city),
+  KEY hours_worked_per_week (hours_worked_per_week),
+  KEY weeks_worked_last_year (weeks_worked_last_year)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+PARTITION BY KEY (id) PARTITIONS 5;
+
+--echo # Insert some rows 
+--disable_query_log
+INSERT INTO t1 VALUES (123,123,'2001-04-14','address123','city123',40,51),
+(127,127,'1977-03-30','address127','city127',0,0), 
+(131,131,'1985-07-29','address131','city131',50,52),
+(135,135,'1997-01-20','address135','city135',0,0),
+(139,139,'1963-04-27','address139','city139',27,52),
+(143,143,'1979-01-28','address143','city143',40,52),
+(147,147,'1985-08-28','address147','city147',0,0),
+(151,151,'1997-01-24','address151','city151',40,52),
+(156,156,'1975-02-19','address156','city156',46,52),
+(158,158,'1996-07-06','address158','city158',46,8),
+(164,164,'1925-12-30','address164','city164',0,0),
+(166,166,'2010-12-30','address166','city166',0,0),
+(172,172,'1996-08-15','address172','city172',0,0),
+(174,174,'2008-05-20','address174','city174',40,52),
+(180,180,'1969-09-05','address180','city180',25,52),
+(182,182,'1977-08-11','address182','city182',40,8),
+(188,188,'2012-03-29','address188','city188',0,0),
+(190,190,'1978-02-19','address190','city190',0,0),
+(215,215,'1982-02-07','address215','city215',40,36),
+(223,223,'2005-02-11','address223','city223',55,52),
+(247,247,'2005-07-02','address247','city247',30,51),
+(255,255,'1997-08-15','address255','city255',0,0),
+(2,2,'1973-05-05','address2','city2',25,52),
+(4,4,'2012-07-21','address4','city4',40,12),
+(6,6,'1982-07-15','address6','city6',0,0),
+(8,8,'1979-02-16','address8','city8',0,0),
+(10,10,'1955-10-06','address10','city10',60,52),
+(12,12,'1977-08-09','address12','city12',40,52),
+(14,14,'2005-03-28','address14','city14',0,0),
+(16,16,'1949-11-07','address16','city16',40,52),
+(18,18,'2012-01-04','address18','city18',0,0),
+(20,20,'2011-01-23','address20','city20',0,0),
+(22,22,'1954-10-14','address22','city22',20,52),
+(24,24,'2010-01-22','address24','city24',0,0),
+(26,26,'1999-08-15','address26','city26',0,0),
+(28,28,'1964-07-05','address28','city28',10,20),
+(30,30,'2004-10-13','address30','city30',0,0),
+(32,32,'2007-06-08','address32','city32',0,0),
+(34,34,'1977-02-23','address34','city34',40,52),
+(36,36,'2007-06-11','address36','city36',75,52),
+(38,38,'1932-04-12','address38','city38',0,0),
+(40,40,'1968-11-16','address40','city40',0,0),
+(42,42,'1996-10-01','address42','city42',38,52),
+(44,44,'1977-08-23','address44','city44',18,6),
+(46,46,'1978-11-23','address46','city46',0,0),
+(48,48,'1998-02-27','address48','city48',0,0),
+(50,50,'1923-09-08','address50','city50',40,52),
+(52,52,'1964-09-09','address52','city52',0,0),
+(55,55,'2001-01-27','address55','city55',40,40),
+(56,56,'1938-08-28','address56','city56',48,52),
+(59,59,'1967-12-19','address59','city59',40,52),
+(60,60,'1969-06-30','address60','city60',40,46),
+(63,63,'2002-04-05','address63','city63',0,0),
+(64,64,'1972-11-21','address64','city64',48,52),
+(67,67,'1988-04-04','address67','city67',0,0),
+(68,68,'1964-07-14','address68','city68',4,16),
+(71,71,'1998-03-09','address71','city71',0,0),
+(72,72,'1960-10-28','address72','city72',35,52),
+(75,75,'1968-04-14','address75','city75',40,52),
+(76,76,'1977-05-13','address76','city76',0,0),
+(79,79,'1982-12-19','address79','city79',0,0),
+(80,80,'1966-01-07','address80','city80',15,12),
+(83,83,'1947-02-09','address83','city83',10,18),
+(84,84,'1976-06-23','address84','city84',40,52),
+(87,87,'2000-10-24','address87','city87',25,4),
+(88,88,'2002-05-23','address88','city88',20,52),
+(91,91,'2000-11-28','address91','city91',60,52),
+(92,92,'1965-07-17','address92','city92',40,52),
+(95,95,'1977-09-16','address95','city95',30,52),
+(96,96,'1994-09-26','address96','city96',0,0),
+(99,99,'2008-02-19','address99','city99',0,0),
+(100,100,'1953-01-07','address100','city100',0,0),
+(103,103,'2010-12-29','address103','city103',0,0),
+(104,104,'1990-12-03','address104','city104',40,52),
+(107,107,'2003-10-27','address107','city107',0,0),
+(108,108,'1998-03-05','address108','city108',40,17),
+(111,111,'2002-10-18','address111','city111',0,0),
+(112,112,'1960-04-02','address112','city112',0,0),
+(115,115,'1989-05-28','address115','city115',40,52),
+(116,116,'1985-10-25','address116','city116',15,52),
+(119,119,'1974-04-15','address119','city119',0,0),
+(120,120,'1926-03-21','address120','city120',0,0),
+(157,157,'1972-03-23','address157','city157',0,0),
+(159,159,'2002-11-08','address159','city159',0,0),
+(165,165,'1998-07-10','address165','city165',0,0),
+(167,167,'1973-11-16','address167','city167',0,0),
+(173,173,'1966-06-26','address173','city173',0,0),
+(175,175,'1957-02-02','address175','city175',0,0),
+(181,181,'1964-11-16','address181','city181',45,26),
+(183,183,'1943-12-02','address183','city183',0,0),
+(189,189,'1986-06-30','address189','city189',0,0),
+(191,191,'2005-05-14','address191','city191',0,0),
+(196,196,'1961-03-23','address196','city196',0,0),
+(197,197,'1955-07-13','address197','city197',0,0),
+(198,198,'2006-11-26','address198','city198',0,0),
+(199,199,'1978-02-06','address199','city199',0,0),
+(208,208,'2012-04-13','address208','city208',48,52),
+(210,210,'1989-08-18','address210','city210',0,0),
+(211,211,'1982-08-17','address211','city211',40,52),
+(212,212,'1919-08-29','address212','city212',0,0),
+(213,213,'1987-03-25','address213','city213',0,0),
+(228,228,'1988-05-05','address228','city228',40,52),
+(229,229,'1936-10-15','address229','city229',0,0),
+(230,230,'1973-08-19','address230','city230',40,52),
+(231,231,'2002-06-18','address231','city231',50,52),
+(240,240,'2011-10-17','address240','city240',60,52),
+(242,242,'1981-07-24','address242','city242',0,0),
+(243,243,'1978-10-12','address243','city243',0,0),
+(244,244,'2003-01-15','address244','city244',0,0),
+(245,245,'1950-09-26','address245','city245',0,0),
+(125,125,'1939-08-02','address125','city125',28,32),
+(126,126,'1984-02-10','address126','city126',0,0),
+(129,129,'1992-01-20','address129','city129',0,0),
+(130,130,'1992-09-18','address130','city130',0,0),
+(133,133,'1996-05-07','address133','city133',24,20),
+(134,134,'1987-07-13','address134','city134',0,0),
+(137,137,'2004-03-10','address137','city137',0,0),
+(138,138,'1989-02-10','address138','city138',0,0),
+(141,141,'1970-03-21','address141','city141',0,0),
+(142,142,'1984-05-25','address142','city142',40,50),
+(145,145,'1959-05-24','address145','city145',0,0),
+(146,146,'1946-07-28','address146','city146',35,16),
+(149,149,'1993-09-16','address149','city149',0,0),
+(150,150,'1975-12-18','address150','city150',0,0),
+(153,153,'1993-12-20','address153','city153',0,0),
+(155,155,'1934-10-29','address155','city155',0,0),
+(161,161,'1969-11-04','address161','city161',50,50),
+(163,163,'1976-05-03','address163','city163',40,52),
+(169,169,'1982-12-19','address169','city169',0,0),
+(171,171,'1976-07-01','address171','city171',0,0),
+(177,177,'2002-11-16','address177','city177',0,0),
+(179,179,'1964-02-05','address179','city179',40,32),
+(185,185,'1981-02-06','address185','city185',0,0),
+(187,187,'1962-06-04','address187','city187',40,52),
+(216,216,'1996-05-21','address216','city216',48,52),
+(248,248,'1963-09-06','address248','city248',0,0),
+(256,256,'1966-07-14','address256','city256',0,0),
+(53,53,'1992-05-25','address53','city53',0,0),
+(57,57,'2003-11-12','address57','city57',25,20),
+(61,61,'1953-01-29','address61','city61',0,0),
+(65,65,'1975-05-02','address65','city65',10,10),
+(69,69,'1938-03-20','address69','city69',0,0),
+(73,73,'1969-05-05','address73','city73',0,0),
+(77,77,'1996-05-19','address77','city77',0,0),
+(81,81,'1985-06-22','address81','city81',0,0),
+(85,85,'2002-10-10','address85','city85',0,0),
+(89,89,'1958-06-16','address89','city89',0,0),
+(93,93,'1962-06-16','address93','city93',0,0),
+(97,97,'1964-10-08','address97','city97',0,0),
+(101,101,'1986-06-11','address101','city101',40,52),
+(105,105,'1999-05-14','address105','city105',40,45),
+(109,109,'2000-05-23','address109','city109',0,0),
+(113,113,'1960-08-03','address113','city113',8,15),
+(117,117,'1982-02-15','address117','city117',50,36),
+(121,121,'1998-10-18','address121','city121',24,52),
+(192,192,'1964-07-24','address192','city192',40,52),
+(193,193,'1973-05-03','address193','city193',0,0),
+(194,194,'1980-01-14','address194','city194',40,52),
+(195,195,'1975-07-15','address195','city195',45,52),
+(200,200,'2006-03-09','address200','city200',0,0),
+(201,201,'2008-05-20','address201','city201',3,28),
+(202,202,'2000-06-30','address202','city202',12,52),
+(203,203,'1992-07-08','address203','city203',50,52),
+(204,204,'1988-07-05','address204','city204',14,40),
+(205,205,'1950-10-29','address205','city205',0,0),
+(206,206,'1962-11-25','address206','city206',0,0),
+(207,207,'1946-06-03','address207','city207',0,0),
+(214,214,'1973-12-14','address214','city214',0,0),
+(217,217,'1945-11-06','address217','city217',40,36),
+(218,218,'2007-07-20','address218','city218',0,0),
+(219,219,'1979-10-05','address219','city219',0,0),
+(220,220,'1992-06-20','address220','city220',10,12),
+(221,221,'2007-03-26','address221','city221',50,52),
+(222,222,'1989-12-24','address222','city222',0,0),
+(224,224,'1975-07-14','address224','city224',0,0),
+(225,225,'1976-02-23','address225','city225',20,52),
+(226,226,'1974-06-22','address226','city226',0,0),
+(227,227,'2004-01-16','address227','city227',0,0),
+(232,232,'1958-01-01','address232','city232',0,0),
+(233,233,'1966-08-03','address233','city233',40,32),
+(234,234,'1975-10-22','address234','city234',40,52),
+(235,235,'1983-10-25','address235','city235',0,0),
+(236,236,'1974-03-07','address236','city236',0,0),
+(237,237,'1965-12-31','address237','city237',45,20),
+(238,238,'1971-10-16','address238','city238',0,0),
+(239,239,'1989-07-19','address239','city239',0,0),
+(246,246,'1960-07-08','address246','city246',0,0),
+(249,249,'1943-07-01','address249','city249',40,30),
+(250,250,'1983-10-15','address250','city250',30,52),
+(251,251,'1979-07-03','address251','city251',0,0),
+(252,252,'1985-10-04','address252','city252',15,4),
+(253,253,'1966-10-24','address253','city253',0,0),
+(254,254,'1956-02-02','address254','city254',0,0),
+(1,1,'2003-11-23','address1','city1',40,52),
+(3,3,'1938-01-23','address3','city3',0,0),
+(5,5,'2006-12-27','address5','city5',40,48),
+(7,7,'1969-04-09','address7','city7',0,0),
+(9,9,'2006-06-14','address9','city9',0,0),
+(11,11,'1999-01-12','address11','city11',40,52),
+(13,13,'1968-01-13','address13','city13',50,12),
+(15,15,'1960-04-11','address15','city15',0,0),
+(17,17,'2006-10-13','address17','city17',40,52),
+(19,19,'1950-08-19','address19','city19',0,0),
+(21,21,'2000-05-01','address21','city21',40,30),
+(23,23,'1952-06-09','address23','city23',40,52),
+(25,25,'1934-12-08','address25','city25',32,40),
+(27,27,'1995-04-19','address27','city27',40,45),
+(29,29,'1986-01-14','address29','city29',44,52),
+(31,31,'1978-04-19','address31','city31',10,20),
+(33,33,'1989-11-23','address33','city33',25,10),
+(35,35,'2012-01-02','address35','city35',8,48),
+(37,37,'2005-08-24','address37','city37',40,42),
+(39,39,'1973-11-02','address39','city39',40,52),
+(41,41,'2011-10-12','address41','city41',20,30),
+(43,43,'1960-12-24','address43','city43',0,0),
+(45,45,'1990-04-17','address45','city45',35,40),
+(47,47,'1964-04-02','address47','city47',0,0),
+(49,49,'1957-01-25','address49','city49',40,52),
+(51,51,'1970-10-20','address51','city51',0,0),
+(54,54,'1987-09-30','address54','city54',0,0),
+(58,58,'1975-05-07','address58','city58',0,0),
+(62,62,'1972-08-03','address62','city62',40,52),
+(66,66,'1995-11-04','address66','city66',0,0),
+(70,70,'1985-10-19','address70','city70',40,52),
+(74,74,'1969-06-09','address74','city74',0,0),
+(78,78,'2003-01-16','address78','city78',66,52),
+(82,82,'2012-04-29','address82','city82',50,30),
+(86,86,'2008-02-03','address86','city86',0,0),
+(90,90,'1973-05-15','address90','city90',35,12),
+(94,94,'1987-10-28','address94','city94',40,50),
+(98,98,'1973-06-10','address98','city98',65,50),
+(102,102,'2009-09-13','address102','city102',0,0),
+(106,106,'1986-07-03','address106','city106',0,0),
+(110,110,'1982-06-10','address110','city110',35,52),
+(114,114,'1963-10-08','address114','city114',48,52),
+(118,118,'1948-03-07','address118','city118',0,0),
+(122,122,'1997-12-19','address122','city122',0,0),
+(124,124,'1966-03-25','address124','city124',0,0),
+(128,128,'1968-08-13','address128','city128',0,0),
+(132,132,'1989-09-25','address132','city132',20,20),
+(136,136,'1993-09-02','address136','city136',0,0),
+(140,140,'1981-05-31','address140','city140',48,52),
+(144,144,'1960-09-15','address144','city144',0,0),
+(148,148,'1945-02-13','address148','city148',40,38),
+(152,152,'2010-11-13','address152','city152',20,52),
+(154,154,'1950-11-07','address154','city154',55,52),
+(160,160,'1981-01-17','address160','city160',0,0),
+(162,162,'2001-03-19','address162','city162',0,0),
+(168,168,'2003-03-28','address168','city168',0,0),
+(170,170,'1977-06-18','address170','city170',50,52),
+(176,176,'1967-04-15','address176','city176',30,50),
+(178,178,'1989-10-25','address178','city178',60,12),
+(184,184,'2004-04-21','address184','city184',0,0),
+(186,186,'1952-11-08','address186','city186',50,48),
+(209,209,'1943-03-15','address209','city209',40,30),
+(241,241,'1979-12-02','address241','city241',0,0),
+(257,257,'2010-03-06','address257','city257',40,47);
+--enable_query_log
+
+select * from t1 where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21';
+select * from t1 IGNORE INDEX(dob, weeks_worked_last_year, hours_worked_per_week) where hours_worked_per_week = 40 and weeks_worked_last_year = 52 and dob < '1949-11-21';
+
+drop table t1;