Merge 11.0-selectivity into 11.0

14 files changed, 57 insertions, 34 deletions

diff --git a/mysql-test/include/analyze-format.inc b/mysql-test/include/analyze-format.inc
index 7d1c48f3e6f..e65450ff001 100644
--- a/mysql-test/include/analyze-format.inc
+++ b/mysql-test/include/analyze-format.inc
@@ -1,3 +1,3 @@
 # The time on ANALYSE FORMAT=JSON is rather variable
 
---replace_regex /("(r_total_time_ms|r_table_time_ms|r_other_time_ms|r_buffer_size|r_filling_time_ms|r_query_time_in_progress_ms)": )[^, \n]*/\1"REPLACED"/
+--replace_regex /("(r_total_time_ms|r_table_time_ms|r_other_time_ms|r_buffer_size|r_filling_time_ms|r_query_time_in_progress_ms|r_unpack_time_ms|cost)": )[^, \n]*/\1"REPLACED"/
diff --git a/mysql-test/include/analyze-no-filtered.inc b/mysql-test/include/analyze-no-filtered.inc
new file mode 100644
index 00000000000..eb1663167b2
--- /dev/null
+++ b/mysql-test/include/analyze-no-filtered.inc
@@ -0,0 +1,2 @@
+--replace_regex /("(filtered|r_total_time_ms|r_table_time_ms|r_other_time_ms|r_buffer_size|r_filling_time_ms|r_query_time_in_progress_ms|r_unpack_time_ms|cost)": )[^, \n]*/\1"REPLACED"/
+
diff --git a/mysql-test/include/common-tests.inc b/mysql-test/include/common-tests.inc
index 9c6b29858c8..9b54b049f8b 100644
--- a/mysql-test/include/common-tests.inc
+++ b/mysql-test/include/common-tests.inc
@@ -13,6 +13,11 @@
 drop table if exists t1,t2,t3,t4;
 --enable_warnings
 
+# We have to use Aria instead of MyISAM as MyISAM has a very high row
+# access cost which causes some tests to use use join_cache instead of eq_ref
+
+set @@default_storage_engine="aria";
+
 CREATE TABLE t1 (
   Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
   Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
@@ -1429,7 +1434,7 @@ set tmp_memory_table_size=default;
 select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
 
 #
-# A big order by that should trigger a merge in filesort
+# A big order by that should traigger a merge in filesort
 #
 
 select distinct companynr,rtrim(space(512+companynr)) from t3 order by 1,2;
@@ -1446,9 +1451,9 @@ select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr orde
 
 explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
 
-#
-# Some test with ORDER BY and limit
-#
+--echo #
+--echo # Some test with ORDER BY and limit
+--echo #
 
 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
@@ -1501,7 +1506,7 @@ create table t4 (
   companyname char(30) NOT NULL default '',
   PRIMARY KEY (companynr),
   UNIQUE KEY companyname(companyname)
-) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';
+) ENGINE=aria MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';
 
 --disable_query_log
 INSERT INTO t4 (companynr, companyname) VALUES (29,'company 1');
@@ -1555,8 +1560,9 @@ explain select companynr,companyname from t2 left join t4 using (companynr) wher
 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null;
 delete from t2 where fld1=999999;
 
-#
-# Test left join optimization
+--echo #
+--echo # Test left join optimization
+--echo #
 
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0;
 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0;
diff --git a/mysql-test/include/ctype_numconv.inc b/mysql-test/include/ctype_numconv.inc
index 6c7ac3b69fe..00364fd3406 100644
--- a/mysql-test/include/ctype_numconv.inc
+++ b/mysql-test/include/ctype_numconv.inc
@@ -1739,6 +1739,7 @@ CREATE TABLE t1 (
   date_column DATE DEFAULT NULL,
   KEY(date_column));
 INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01');
+INSERT INTO t1 VALUES (3,'2012-09-01'),(4,'2012-10-01'),(5,'2012-10-01');
 EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
 ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL;
 EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01';
diff --git a/mysql-test/include/explain-no-costs-filtered.inc b/mysql-test/include/explain-no-costs-filtered.inc
new file mode 100644
index 00000000000..585d8b3fdcc
--- /dev/null
+++ b/mysql-test/include/explain-no-costs-filtered.inc
@@ -0,0 +1 @@
+--replace_regex /("(cost|filtered)": )[^, \n]*/\1"REPLACED"/
diff --git a/mysql-test/include/explain-no-costs.inc b/mysql-test/include/explain-no-costs.inc
new file mode 100644
index 00000000000..f2f362b8cbe
--- /dev/null
+++ b/mysql-test/include/explain-no-costs.inc
@@ -0,0 +1 @@
+--replace_regex /("(cost)": )[^, \n]*/\1"COST_REPLACED"/
diff --git a/mysql-test/include/explain_non_select.inc b/mysql-test/include/explain_non_select.inc
index d22310c9813..8e60f582f9e 100644
--- a/mysql-test/include/explain_non_select.inc
+++ b/mysql-test/include/explain_non_select.inc
@@ -1,6 +1,7 @@
 # This file is a collection of regression and coverage tests
 # for WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE.
 
+-- source include/have_sequence.inc
 -- disable_query_log
 -- disable_result_log
 # SET GLOBAL innodb_stats_persistent=0;
@@ -73,15 +74,18 @@ INSERT INTO t2 VALUES (1), (2), (3);
 --source include/explain_utils.inc
 DROP TABLE t1, t2;
 
---echo #7
+--echo #7a
 CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (1), (2), (3);
 CREATE TABLE t2 (b INT);
-INSERT INTO t2 VALUES (1), (2), (3);
---let $query =  UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3)
---let $select = SELECT * FROM t1, t2            WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3)
+INSERT INTO t2 VALUES (1), (2), (3), (1000);
+CREATE TABLE t3 like t2;
+insert into t3 select * from t2;
+insert into t3 select seq from seq_1001_to_2000;
+--let $query =  UPDATE        t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t3 WHERE t3.b < 3)
+--let $select = SELECT * FROM t1, t2            WHERE a IN (SELECT b FROM t3 WHERE t3.b < 3)
 --source include/explain_utils.inc
-DROP TABLE t1, t2;
+DROP TABLE t1, t2, t3;
 
 --echo #8
 CREATE TABLE t1 (a INT);
@@ -197,7 +201,7 @@ DROP TABLE t1, t2, t3;
 CREATE TABLE t1 (a INT);
 INSERT INTO t1 VALUES (1), (2), (3);
 CREATE TABLE t2 (a INT);
-INSERT INTO t2 VALUES (1), (2), (3);
+INSERT INTO t2 VALUES (1), (2), (3), (1000);
 --let $query =  UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2)
 --let $select = SELECT * FROM t1     WHERE a IN (SELECT a FROM t2)
 --source include/explain_utils.inc
diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc
index d78fe0dd209..b37f59b46c5 100644
--- a/mysql-test/include/icp_tests.inc
+++ b/mysql-test/include/icp_tests.inc
@@ -486,7 +486,7 @@ CREATE TABLE t1 (
 );
 
 INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1);
-
+insert into t1 select seq,seq from seq_100_to_110;
 EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3);
 
 SET SESSION optimizer_switch='index_condition_pushdown=off';
@@ -723,7 +723,6 @@ DROP TABLE t1;
 
 CREATE TABLE t1 (b int NOT NULL, c int, a varchar(1024), PRIMARY KEY (b));
 INSERT INTO t1 VALUES (1,4,'Ill');
-insert into t1 select seq+100,5,seq from seq_1_to_100;
 
 CREATE TABLE t2 (a varchar(1024), KEY (a(512)));
 INSERT INTO t2 VALUES
@@ -856,6 +855,8 @@ ANALYZE TABLE t1,t2;
 
 SET @save_optimize_switch=@@optimizer_switch;
 SET optimizer_switch='materialization=on';
+set @save_optimizer_where_cost=@@optimizer_where_cost;
+set @@optimizer_where_cost=1;
 
 EXPLAIN
 SELECT COUNT(*) FROM t1 AS t, t2
@@ -873,6 +874,7 @@ WHERE c = g
            OR a = 0 AND h < 'z' );
 
 SET optimizer_switch=@save_optimizer_switch;
+set @@optimizer_where_cost=@save_optimizer_where_cost;
 
 DROP TABLE t1,t2;
 
diff --git a/mysql-test/include/index_merge1.inc b/mysql-test/include/index_merge1.inc
index 91609f628ca..199fc9d3b2f 100644
--- a/mysql-test/include/index_merge1.inc
+++ b/mysql-test/include/index_merge1.inc
@@ -517,7 +517,7 @@ DROP TABLE t1;
 create table t1 (a int);
 insert into t1 values (1),(2);
 create table t2(a int, b int);
-insert into t2 values (1,1), (2, 1000);
+insert into t2 values (1,1), (2, 1000),(5000,5000);
 create table t3 (a int, b int, filler char(100), key(a), key(b));
 
 insert into t3 select 1000, 1000,'filler' from seq_1_to_1000;
diff --git a/mysql-test/include/last_query_cost.inc b/mysql-test/include/last_query_cost.inc
new file mode 100644
index 00000000000..a18fd9e4c04
--- /dev/null
+++ b/mysql-test/include/last_query_cost.inc
@@ -0,0 +1,5 @@
+--disable_query_log
+--disable_column_names
+show status like 'last_query_cost';
+--enable_column_names
+--enable_query_log
diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index 2ec0868c39e..cbb79668b2a 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -1183,14 +1183,14 @@ set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;
 set global innodb_autoextend_increment=8;
 set global innodb_autoextend_increment=@my_innodb_autoextend_increment;
 
-#
-# Bug #37830: ORDER BY ASC/DESC - no difference
-#
+--echo #
+--echo # Bug #37830: ORDER BY ASC/DESC - no difference
+--echo #
 
 CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY (a), KEY t1_b (b))
  ENGINE=InnoDB;
 
-INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1);
+INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1), (100,2,2);
 INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1;
 
 -- disable_query_log
diff --git a/mysql-test/include/percona_nonflushing_analyze_debug.inc b/mysql-test/include/percona_nonflushing_analyze_debug.inc
index 95621c70d5c..8cdf6218609 100644
--- a/mysql-test/include/percona_nonflushing_analyze_debug.inc
+++ b/mysql-test/include/percona_nonflushing_analyze_debug.inc
@@ -8,7 +8,7 @@
 
 --connect con1,localhost,root
 
-SET DEBUG_SYNC="handler_ha_index_next_end SIGNAL idx_scan_in_progress WAIT_FOR finish_scan";
+SET DEBUG_SYNC="handler_rnd_next_end SIGNAL idx_scan_in_progress WAIT_FOR finish_scan";
 
 send_eval SELECT * FROM $percona_nonflushing_analyze_table;
 
diff --git a/mysql-test/include/rowid_filter_debug_kill.inc b/mysql-test/include/rowid_filter_debug_kill.inc
index 513efed8a4c..c672954fc7c 100644
--- a/mysql-test/include/rowid_filter_debug_kill.inc
+++ b/mysql-test/include/rowid_filter_debug_kill.inc
@@ -2,13 +2,18 @@
 --source include/have_debug_sync.inc
 --source include/have_sequence.inc
 --source include/count_sessions.inc
+--source include/have_sequence.inc
+--source include/no_valgrind_without_big.inc
 
 --echo #
 --echo # MDEV-22761 KILL QUERY during rowid_filter, crashes
 --echo #
 
+create table t1(a int);
+insert into t1 select seq from seq_1_to_1000;
+
 create table t2(a int);
-insert into t2 select * from seq_0_to_99;
+insert into t2 select seq from seq_1_to_100;
 
 # 10K rows 
 CREATE TABLE t3 (
@@ -18,22 +23,16 @@ CREATE TABLE t3 (
   KEY (key1),
   KEY (key2)
 );
+insert into t3 select seq,seq, 'filler-data-filler-data' from seq_1_to_2000;
+
 select engine from information_schema.tables
 where table_schema=database() and table_name='t3';
-
-insert into t3
-select
-  A.seq,
-  B.seq,
-  'filler-data-filler-data'
-from seq_0_to_99 A, seq_0_to_99 B;
-
 analyze table t2,t3;
 
 explain
 select * from t2, t3
 where 
-  t3.key1=t2.a and t3.key2 in (2,3);
+  t3.key1=t2.a and t3.key2 in (2,3,4,5,6,7,8,9,10);
 
 let $target_id= `select connection_id()`;
 
@@ -41,7 +40,7 @@ set debug_sync='handler_rowid_filter_check SIGNAL at_rowid_filter_check WAIT_FOR
 send
 select * from t2, t3
 where 
-  t3.key1=t2.a and t3.key2 in (2,3);
+  t3.key1=t2.a and t3.key2 in (2,3,4,5,6,7,8,9,10);
 
 connect (con1, localhost, root,,);
 set debug_sync='now WAIT_FOR at_rowid_filter_check';
@@ -55,5 +54,5 @@ disconnect con1;
 reap;
 set debug_sync='RESET';
 
-drop table t2,t3;
+drop table t1,t2,t3;
 --source include/wait_until_count_sessions.inc
diff --git a/mysql-test/include/world.inc b/mysql-test/include/world.inc
index 1e81c5c1aa7..a6f877ce0cd 100644
--- a/mysql-test/include/world.inc
+++ b/mysql-test/include/world.inc
@@ -4,6 +4,7 @@
 
 # Table Country
 
+BEGIN;
 INSERT IGNORE INTO Country VALUES
 ('AFG','Afghanistan',652090.00,22720000,1),
 ('NLD','Netherlands',41526.00,15864000,5),
@@ -5339,5 +5340,6 @@ INSERT INTO CountryLanguage VALUES
 ('CHN','Dong',0.2),
 ('RUS','Belorussian',0.3),
 ('USA','Portuguese',0.2);
+COMMIT;
 
 ANALYZE TABLE Country, City, CountryLanguage;