Merge 10.2 into 10.3

1 files changed, 1 insertions, 220 deletions

diff --git a/mysql-test/main/innodb_ext_key.test b/mysql-test/main/innodb_ext_key.test
index 8def57ad377..f5edd736490 100644
--- a/mysql-test/main/innodb_ext_key.test
+++ b/mysql-test/main/innodb_ext_key.test
@@ -1,9 +1,4 @@
---source include/have_innodb.inc
-
---disable_warnings
-DROP TABLE IF EXISTS t1,t2,t3,t4;
-DROP DATABASE IF EXISTS dbt3_s001;
---enable_warnings
+--source include/innodb_prefix_index_cluster_optimization.inc
 
 SET SESSION STORAGE_ENGINE='InnoDB';
 
@@ -19,32 +14,12 @@ use dbt3_s001;
 --enable_result_log
 --enable_query_log
 
-set @save_ext_key_optimizer_switch=@@optimizer_switch;
-
-set optimizer_switch='extended_keys=off';
 explain
 select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
 flush status;
 select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
-flush status;
-select count(*) from lineitem where l_orderkey=130 and l_shipdate='1992-07-01';
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
-explain
-select count(*) from lineitem 
-  where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
-flush status;
-select count(*) from lineitem 
-  where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain
 select count(*) from lineitem
   where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
@@ -53,16 +28,6 @@ select count(*) from lineitem
   where l_orderkey=130 and l_linenumber=2 and l_shipdate='1992-07-01';
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
-explain 
-select count(*) from lineitem
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
-flush status;
-select count(*) from lineitem
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain 
 select count(*) from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
@@ -71,16 +36,6 @@ select count(*) from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
-explain
-select l_orderkey, l_linenumber from lineitem
-  where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-flush status;
-select l_orderkey, l_linenumber from lineitem
-  where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain
 select l_orderkey, l_linenumber from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
@@ -89,21 +44,12 @@ select l_orderkey, l_linenumber from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
 explain
 select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
 flush status;
 select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
-flush status;
-select min(l_orderkey) from lineitem where l_shipdate='1992-07-01';
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
 explain
 select min(l_orderkey) from lineitem 
   where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
@@ -112,25 +58,6 @@ select min(l_orderkey) from lineitem
   where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select min(l_orderkey) from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-flush status;
-select min(l_orderkey) from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey between 1001 and 2000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
-explain
-select max(l_linenumber) from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey=130;
-flush status;
-select max(l_linenumber) from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey=130;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain
 select max(l_linenumber) from lineitem 
   where l_shipdate='1992-07-01' and l_orderkey=130;
@@ -139,7 +66,6 @@ select max(l_linenumber) from lineitem
   where l_shipdate='1992-07-01' and l_orderkey=130;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
 explain
 select l_orderkey, l_linenumber 
   from lineitem use index (i_l_shipdate, i_l_receiptdate)
@@ -152,20 +78,6 @@ select l_orderkey, l_linenumber
         or l_receiptdate='1992-07-01' and l_orderkey=5603;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select l_orderkey, l_linenumber 
-  from lineitem use index (i_l_shipdate, i_l_receiptdate)
-  where l_shipdate='1992-07-01' and l_orderkey=130 
-        or l_receiptdate='1992-07-01' and l_orderkey=5603;
-flush status;
-select l_orderkey, l_linenumber 
-  from lineitem use index (i_l_shipdate, i_l_receiptdate)
-  where l_shipdate='1992-07-01' and l_orderkey=130 
-        or l_receiptdate='1992-07-01' and l_orderkey=5603;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
 explain
 select l_orderkey, l_linenumber
   from lineitem use index (i_l_shipdate, i_l_receiptdate)
@@ -178,20 +90,6 @@ select l_orderkey, l_linenumber
          or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select l_orderkey, l_linenumber
-  from lineitem use index (i_l_shipdate, i_l_receiptdate)
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-         or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-flush status;
-select l_orderkey, l_linenumber
-  from lineitem use index (i_l_shipdate, i_l_receiptdate)
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-         or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
 explain
 select l_orderkey, l_linenumber from lineitem 
   where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
@@ -202,28 +100,6 @@ select l_orderkey, l_linenumber from lineitem
         or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=on';
-explain
-select l_orderkey, l_linenumber from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-        or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-flush status;
-select l_orderkey, l_linenumber from lineitem 
-  where l_shipdate='1992-07-01' and l_orderkey between 1 and 1000
-        or l_receiptdate='1992-07-01' and l_orderkey between 5001 and 6000;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=off';
---replace_column 9 #
-explain 
-select max(l_orderkey) from lineitem
-  where l_partkey between 1 and 10 group by l_partkey;
-flush status;
-select max(l_orderkey) from lineitem
-  where l_partkey between 1 and 10 group by l_partkey;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 --replace_column 9 #
 explain 
 select max(l_orderkey) from lineitem
@@ -233,17 +109,6 @@ select max(l_orderkey) from lineitem
   where l_partkey between 1 and 10 group by l_partkey;
 show status like 'handler_read%';
 
-set optimizer_switch='extended_keys=off';
---replace_column 9 #
-explain 
-select max(l_orderkey) from lineitem
-  where l_suppkey in (1,4) group by l_suppkey;
-flush status;
-select max(l_orderkey) from lineitem
-  where l_suppkey in (1,4) group by l_suppkey;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 --replace_column 9 #
 explain 
 select max(l_orderkey) from lineitem
@@ -255,23 +120,6 @@ show status like 'handler_read%';
 
 create index i_p_retailprice on part(p_retailprice);
 
-set optimizer_switch='extended_keys=off';
---replace_column 9 #
-explain
-select o_orderkey, p_partkey
-  from part use index (i_p_retailprice),
-       lineitem use index (i_l_partkey), orders
-  where p_retailprice > 1100 and o_orderdate='1997-01-01' 
-        and o_orderkey=l_orderkey and p_partkey=l_partkey;
-flush status;
-select o_orderkey, p_partkey
-  from part use index (i_p_retailprice),
-       lineitem use index (i_l_partkey), orders
-  where p_retailprice > 1100 and o_orderdate='1997-01-01' 
-        and o_orderkey=l_orderkey and p_partkey=l_partkey;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 --replace_column 9 #
 explain
 select o_orderkey, p_partkey
@@ -296,8 +144,6 @@ create table t0 (a int);
 insert into t0 values (1), (2), (3), (4), (5);
 create index i_p_size on part(p_size);
 
-set optimizer_switch='extended_keys=on';
-
 explain
 select * from t0, part ignore index (primary)
   where p_partkey=t0.a and p_size=1;
@@ -320,7 +166,6 @@ use test;
 set @save_optimizer_switch=@@optimizer_switch;
 
 SET optimizer_switch='materialization=on,semijoin=on';
-SET optimizer_switch='extended_keys=on';
 
 CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (1,1), (2,2);
@@ -339,10 +184,7 @@ set optimizer_switch=@save_optimizer_switch;
 --echo # (valgrinf complains fixed by the patch for bug #914560)
 --echo #
 
-set @save_optimizer_switch=@@optimizer_switch;
-
 SET optimizer_switch = 'derived_with_keys=on';
-SET optimizer_switch = 'extended_keys=on';
 
 CREATE TABLE t1 (a varchar(1)) ENGINE=MyISAM;
 INSERT INTO t1 VALUES ('j'), ('v');
@@ -382,24 +224,12 @@ INSERT INTO t2 VALUES
   (10), (11), (12), (13), (14), 
   (15), (16), (17), (18), (19), (24);
 
-set @save_optimizer_switch=@@optimizer_switch;
-
-SET optimizer_switch = 'extended_keys=off';
-EXPLAIN
-SELECT a FROM t1 AS t, t2 
-  WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
-SELECT a FROM t1 AS t, t2 
-  WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
-
-SET optimizer_switch = 'extended_keys=on';
 EXPLAIN
 SELECT a FROM t1 AS t, t2 
   WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 SELECT a FROM t1 AS t, t2 
   WHERE c = a AND b IN (SELECT b FROM t1, t2 WHERE b = t.b);
 
-set optimizer_switch=@save_optimizer_switch;
-
 DROP TABLE t1,t2;
 
 --echo #
@@ -413,13 +243,11 @@ CREATE TABLE t2 (b int) ENGINE=MyISAM;
 INSERT INTO t1 (a) VALUES (4), (6);
 INSERT INTO t2 (b) VALUES (0), (8);
 
-set @save_optimizer_switch=@@optimizer_switch;
 set @save_join_cache_level=@@join_cache_level;
 
 SET join_cache_level=3;
 SET optimizer_switch='join_cache_hashed=on';
 SET optimizer_switch='join_cache_bka=on';
-SET optimizer_switch='extended_keys=on';
 
 EXPLAIN
 SELECT * FROM t1, t2 WHERE b=a;
@@ -448,24 +276,11 @@ KEY c4 (c4)
 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
 
 
-set @save_optimizer_switch=@@optimizer_switch;
-
-set session optimizer_switch='extended_keys=off';
-INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
-  ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
-INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
-  ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
-
-DELETE FROM t1;
-
-set session optimizer_switch='extended_keys=on';
 INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
   ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
 INSERT INTO t1 (c2, c3, c4) VALUES (58291525, 2580, 'foobar')
   ON DUPLICATE KEY UPDATE c4 = VALUES(c4);
 
-set optimizer_switch=@save_optimizer_switch;
-
 DROP TABLE t1;
 
 --echo #
@@ -474,8 +289,6 @@ DROP TABLE t1;
 --echo # (performance regression introduced in the patch for mdev-3851)
 --echo #
 
-set @save_optimizer_switch=@@optimizer_switch;
-
 create table t1 (a int not null) engine=innodb;
 
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -489,14 +302,6 @@ select
   A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a 
 from t1 A, t1 B;
 
-set optimizer_switch='extended_keys=off';
-explain 
-select * from t1, t2 where t2.a=t1.a and t2.b < 2;
-flush status;
-select * from t1, t2 where t2.a=t1.a and t2.b < 2;
-show status like 'handler_read%';
-
-set optimizer_switch='extended_keys=on';
 explain 
 select * from t1, t2 where t2.a=t1.a and t2.b < 2;
 flush status;
@@ -522,15 +327,6 @@ alter table t3 add primary key (pk1, pk2);
 alter table t3 add  key (col1, col2);
 analyze table t1,t3;
 
-set optimizer_switch='extended_keys=off';
---replace_column 9 #
-explain 
-select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a;
---replace_column 9 #
-explain 
-select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a and t3.pk1=t1.a;
-
-set optimizer_switch='extended_keys=on';
 --replace_column 9 #
 explain 
 select * from t1, t3 where t3.col1=t1.a and  t3.col2=t1.a;
@@ -544,8 +340,6 @@ drop table t1,t2,t3;
 --echo # Bug mdev-4340: performance regression with extended_keys=on   
 --echo #
 
-set @save_optimizer_switch=@@optimizer_switch;
-
 CREATE TABLE t1 (
   page_id int(8) unsigned NOT NULL AUTO_INCREMENT,
   page_namespace int(11) NOT NULL DEFAULT '0',
@@ -649,13 +443,6 @@ INSERT INTO t3 VALUES
 (97,'text-6009',''),(98,'text-7009',''),(99,'text-8009',''),(100,'text-9009','');
 
 
-set optimizer_switch='extended_keys=off';
-EXPLAIN 
-SELECT *  FROM t1, t2 IGNORE INDEX (PRIMARY), t3 
-  WHERE page_id=rev_page AND  rev_text_id=old_id  AND page_namespace=4 AND page_title='Sandbox'
-ORDER BY rev_timestamp ASC LIMIT 10;
-
-set optimizer_switch='extended_keys=on';
 EXPLAIN 
 SELECT *  FROM t1, t2 IGNORE INDEX (PRIMARY), t3 
   WHERE page_id=rev_page AND  rev_text_id=old_id  AND page_namespace=4 AND page_title='Sandbox'
@@ -674,14 +461,10 @@ create table t2 (a bigint not null unique auto_increment, b varchar(10), primary
 insert into t1 (b) values (null), (null), (null);
 insert into t2 (b) values (null), (null), (null);
 
-set optimizer_switch='extended_keys=on';
 explain select a from t1 where b is null order by a desc limit 2;
 select a from t1 where b is null order by a desc limit 2;
 explain select a from t2 where b is null order by a desc limit 2;
 select a from t2 where b is null order by a desc limit 2;
-set optimizer_switch='extended_keys=off';
-explain select a from t2 where b is null order by a desc limit 2;
-select a from t2 where b is null order by a desc limit 2;
 
 explain select a from t2 where b is null order by a desc;
 select a from t2 where b is null order by a desc;
@@ -691,8 +474,6 @@ select a from t2 where b is null order by a desc,a,a;
 
 drop table t1, t2;
 
-set optimizer_switch=@save_optimizer_switch;
-
 --echo # 
 --echo # MDEV-10325: Queries examines all rows of a tables when it should not
 --echo #