Adding upstream version 1:10.11.6.upstream/1%10.11.6

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1044 insertions, 0 deletions

diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
new file mode 100644
index 00000000..29b72fcd
--- /dev/null
+++ b/mysql-test/main/opt_trace.test
@@ -0,0 +1,1044 @@
+--source include/not_embedded.inc
+--source include/have_sequence.inc
+# View protocol changes some plans
+--source include/no_view_protocol.inc
+
+SELECT table_name, column_name FROM information_schema.columns where table_name="OPTIMIZER_TRACE";
+set optimizer_trace="enabled=on";
+show variables like 'optimizer_trace';
+set optimizer_trace="enabled=off";
+show variables like 'optimizer_trace';
+create table t1 (a int, b int);
+insert into t1 values (1,2),(2,3);
+
+create table t2 (b int);
+insert into t2 values (1),(2);
+
+analyze table t1;
+analyze table t2;
+create function f1 (a int) returns INT
+return 1;
+
+create view v1 as select * from t1 where t1.a=1;
+create view v2 as select * from t1 where t1.a=1 group by t1.b;
+set optimizer_trace="enabled=on";
+
+--echo # Mergeable views/derived tables
+--disable_ps2_protocol
+--disable_view_protocol
+select * from v1;
+select * from information_schema.OPTIMIZER_TRACE;
+select * from (select * from t1 where t1.a=1)q;
+select * from information_schema.OPTIMIZER_TRACE;
+
+--echo # Non-Mergeable views
+select * from v2;
+select * from information_schema.OPTIMIZER_TRACE;
+--enable_view_protocol
+--enable_ps2_protocol
+drop table t1,t2;
+drop view v1,v2;
+drop function f1;
+
+create table t1(a int, b int);
+insert into t1 values (0,0),(1,1),(2,1),(3,2),(4,3),
+(5,3),(6,3),(7,3),(8,3),(9,3);
+create table t2(a int, b int);
+insert into t2 values (0,0),(1,1),(2,1),(3,2),(4,3),
+(5,3),(6,3),(7,3),(8,3),(9,3);
+
+ANALYZE TABLE t1;
+ANALYZE TABLE t2;
+
+create view v1 as select a from t1 group by b;
+create view v2 as select a from t2;
+
+# MDEV-27421 ./mtr --ps-protocol main.opt_trace fails to produce some traces
+--disable_ps_protocol
+--echo # Mergeable view
+--optimizer_trace
+explain select * from v2 ;
+
+--echo # Non-Mergeable view
+--optimizer_trace
+explain select * from v1 ;
+--enable_ps_protocol
+drop table t1,t2;
+drop view v1,v2;
+
+--echo #
+--echo # print ref-keyues array
+--echo #
+
+create table t0 (a int);
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (a int, b int, c int, key(a));
+insert into t1 select A.a*10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B;
+
+create table t2(a int, b int, c int , key(a));
+insert into t2 select A.a*10 + B.a, A.a*10 + B.a, A.a*10 + B.a from t0 A, t0 B;
+
+analyze table t1;
+analyze table t2;
+
+explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b;
+select * from information_schema.OPTIMIZER_TRACE;
+drop table t1,t2,t0;
+
+--echo #
+--echo # group_by min max optimization
+--echo #
+CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a));
+--disable_query_log
+INSERT INTO t1(a) VALUES (1), (2), (3), (4);
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+INSERT INTO t1(a) SELECT a FROM t1;
+--enable_query_log
+
+analyze table t1;
+EXPLAIN SELECT DISTINCT a FROM t1;
+select * from information_schema.OPTIMIZER_TRACE;
+drop table t1;
+
+--echo # 
+--echo # With group by , where clause and MIN/MAX function
+--echo # 
+CREATE TABLE t1 (a INT, b INT, c int, d int,  KEY(a,b,c,d));
+INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (1,0,1,1), (3,2,3,3), (4,5,4,4);
+ANALYZE TABLE t1;
+EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3  group by a;
+select * from information_schema.OPTIMIZER_TRACE;
+DROP TABLE t1;
+
+CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a));
+INSERT INTO t1 values (1,'2001-01-01'),(1,'2001-01-02'),
+ (1,'2001-01-03'),(1,'2001-01-04'),
+ (2,'2001-01-01'),(2,'2001-01-02'),
+ (2,'2001-01-03'),(2,'2001-01-04'),
+ (3,'2001-01-01'),(3,'2001-01-02'),
+ (3,'2001-01-03'),(3,'2001-01-04'),
+ (4,'2001-01-01'),(4,'2001-01-02'),
+ (4,'2001-01-03'),(4,'2001-01-04');
+set optimizer_trace='enabled=on';
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+--echo #
+--echo # Late ORDER BY optimization
+--echo #
+
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k(a int primary key);
+insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
+create table t1  (
+  pk int not null,
+  a int,
+  b int,
+  c int,
+  filler char(100),
+  KEY a_a(c),
+  KEY a_c(a,c),
+  KEY a_b(a,b)
+);
+ 
+insert into t1
+select a, a,a,a, 'filler-dataaa' from test.one_k;
+update t1 set a=1 where pk between 0 and 180;
+update t1 set b=2 where pk between 0 and 20;
+analyze table t1;
+set optimizer_trace='enabled=on';
+explain  select * from t1 where a=1 and b=2 order by c limit 1;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1,ten,one_k;
+
+--echo #
+--echo # TABLE ELIMINATION
+--echo #
+
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3);
+create table t0 as select * from t1;
+
+create table t2 (a int primary key, b int)
+  as select a, a as b from t1 where a in (1,2);
+
+create table t3 (a int primary key, b int)
+  as select a, a as b from t1 where a in (1,3);
+
+set optimizer_trace='enabled=on';
+
+analyze table t1;
+analyze table t2;
+analyze table t3;
+
+--echo # table t2 should be eliminated
+explain
+select t1.a from t1 left join t2 on t1.a=t2.a;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo # no tables should be eliminated
+explain select * from t1 left join t2 on t2.a=t1.a;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo # multiple tables are eliminated
+explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t0, t1, t2, t3;
+
+--echo #
+--echo # IN subquery to sem-join is traced
+--echo #
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1(a int, b int);
+insert into t1 values (0,0),(1,1),(2,2);
+create table t2 as select * from t1;
+
+create table t11(a int, b int);
+
+create table t10 (pk int, a int);
+insert into t10 select a,a from t0;
+create table t12 like t10;
+insert into t12 select * from t10;
+
+analyze table t1,t10;
+
+set optimizer_trace='enabled=on';
+explain extended select * from t1 where a in (select pk from t10);
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t0,t1,t11,t10,t12,t2;
+
+--echo #
+--echo # Selectivities for columns and indexes.
+--echo #
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (
+pk int,
+a int,
+b int, 
+key pk(pk),
+key pk_a(pk,a),
+key pk_a_b(pk,a,b));
+insert into t1 select a,a,a from t0;
+
+ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a,b) INDEXES ();
+set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
+set @save_use_stat_tables= @@use_stat_tables;
+set @@optimizer_use_condition_selectivity=4;
+set @@use_stat_tables= PREFERABLY;
+set optimizer_trace='enabled=on';
+explain select * from t1 where pk = 2 and a=5 and b=1;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set @@use_stat_tables= @save_use_stat_tables;
+drop table t0,t1;
+set optimizer_trace="enabled=off";
+
+--echo #
+--echo # Tests added to show that sub-statements are not traced
+--echo #
+
+create table t1(a int);
+insert into t1 values (1),(2),(3),(4);
+create table t2(a int);
+insert into t2 values (1),(2),(3),(4);
+delimiter |;
+create function f1(a int) returns int
+begin
+  declare a int default 0;
+  set a= a+ (select count(*) from t2);
+  return a;
+end|
+
+--enable_prepare_warnings
+create function f2(a int) returns int
+begin
+  declare a int default 0;
+  select count(*) from t2 into a;
+  return a;
+end|
+--disable_prepare_warnings
+
+delimiter ;|
+--disable_view_protocol
+set optimizer_trace='enabled=on';
+select f1(a) from t1;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+select f2(a) from t1;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1,t2;
+drop function f1;
+drop function f2;
+set optimizer_trace='enabled=off';
+--enable_view_protocol
+
+--echo #
+--echo # MDEV-18489: Limit the memory used by the optimizer trace
+--echo #
+--disable_view_protocol
+create table t1 (a int);
+insert into t1 values (1),(2);
+
+set optimizer_trace='enabled=on';
+set @save_optimizer_trace_max_mem_size= @@optimizer_trace_max_mem_size;
+select * from t1;
+select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+set optimizer_trace_max_mem_size=100;
+select * from t1;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+set optimizer_trace_max_mem_size=0;
+select * from t1;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+set optimizer_trace='enabled=off';
+set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size;
+--enable_view_protocol
+--echo #
+--echo # MDEV-18527: Optimizer trace for DELETE query shows table:null
+--echo #
+
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t0 (a int, b int);
+insert into t0 select a,a from ten;
+alter table t0 add key(a);
+
+set optimizer_trace=1;
+explain delete from t0 where t0.a<3;
+select * from information_schema.optimizer_trace;
+drop table ten,t0;
+set optimizer_trace='enabled=off';
+
+--echo #
+--echo # MDEV-18528: Optimizer trace support for multi-table UPDATE and DELETE
+--echo #
+
+set optimizer_trace=1;
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t0 (a int, b int);
+insert into t0 select a,a from ten;
+alter table t0 add key(a);
+create table t1 like t0;
+insert into t1 select * from t0;
+explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3;
+select * from information_schema.optimizer_trace;
+drop table ten,t0,t1;
+set optimizer_trace='enabled=off';
+
+--echo #
+--echo # Merged to Materialized for derived tables
+--echo #
+
+set optimizer_trace=1;
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+explain select * from (select rand() from t1)q;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+set optimizer_trace='enabled=off';
+
+--echo #
+--echo # Semi-join nest
+--echo #
+
+set optimizer_trace=1;
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+create table t2(a int);
+insert into t2 values (1),(2),(3),(1),(2),(3),(1),(2),(3);
+set @save_optimizer_switch= @@optimizer_switch;
+explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_inner_2);
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+--echo # with Firstmatch, mostly for tracing fix_semijoin_strategies_for_picked_join_order
+
+set optimizer_switch='materialization=off';
+explain select * from t1 t_outer_1,t2 t_outer_2  where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and
+                                                 t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4);
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+set optimizer_switch='materialization=on';
+explain select * from t1 t_outer_1,t2 t_outer_2  where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and
+                                                 t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4);
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+set @@optimizer_switch= @save_optimizer_switch;
+drop table t1,t2;
+
+--echo #
+--echo # MDEV-18942: Json_writer::add_bool: Conditional jump or move depends on uninitialised value upon 
+--echo # fulltext search under optimizer trace
+--echo #
+
+CREATE TABLE t1 (f VARCHAR(255), FULLTEXT(f));
+CREATE VIEW v1 AS SELECT * FROM t1;
+INSERT INTO t1 VALUES ('fooba'),('abcde'),('xyzab');
+SET optimizer_trace = 'enabled=on';
+SELECT COUNT(*) FROM v1 WHERE MATCH (f) AGAINST ('fooba');
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-18741: Optimizer trace: multi-part key ranges are printed incorrectly.
+--echo #
+
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 ( a int, b int, key a_b(a,b));
+insert into t1 select a,a from one_k;
+set optimizer_trace='enabled=on';
+
+explain select * from t1 force index (a_b) where a=2 and b=4;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+explain select * from t1 where a >= 900 and b between 10 and 20;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t0,t1;
+
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+--disable_warnings
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+--enable_warnings
+explain select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1,one_k;
+
+create table ten(a int);
+insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (
+  a int not null,
+  b int not null,
+  c int not null,
+  d int not null,
+  key a_b_c(a,b,c)
+);
+
+insert into t1 select a,a, a,a from ten;
+explain select * from t1 force index(a_b_c) where a between 1 and 4 and b < 50;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table ten,t1;
+
+--echo # Ported test from MYSQL for ranges involving Binary column
+
+CREATE TABLE t1(i INT PRIMARY KEY, b BINARY(16), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, x'D95B94336A9946A39CF5B58CFE772D8C');
+INSERT INTO t1 VALUES (2, NULL);
+
+EXPLAIN SELECT * FROM t1 WHERE b IN (0xD95B94336A9946A39CF5B58CFE772D8C);
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+EXPLAIN SELECT * FROM t1 WHERE b IS NULL;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-18880: Optimizer trace prints date in hexadecimal
+--echo #
+
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10) CHARSET BINARY , INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+ALTER TABLE t1 modify column b BINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+ALTER TABLE t1 modify column b VARBINARY(10) AFTER i;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+CREATE TABLE t1(i INT PRIMARY KEY, b CHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+CREATE TABLE t1(i INT PRIMARY KEY, b blob , INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, NULL);
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b= 'ab\n';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+CREATE TABLE t1(i INT PRIMARY KEY, b VARCHAR(10), INDEX i_b(b));
+INSERT INTO t1 VALUES (1, 'ab\n');
+INSERT INTO t1 VALUES (2, 'ab\n');
+set optimizer_trace=1;
+EXPLAIN SELECT * FROM t1 WHERE b='ab\n';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int);
+insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date)) ;
+--disable_warnings
+insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;
+--enable_warnings
+explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1, t0, one_k;
+
+--echo #
+--echo # MDEV-19776: Assertion `to_len >= 8' failed in convert_to_printable with optimizer trace enabled
+--echo #
+
+CREATE TABLE t1 (f VARBINARY(16) NOT NULL, KEY(f));
+INSERT INTO t1 VALUES ('a'),('b');
+SET optimizer_trace = 'enabled=on';
+DELETE FROM t1 WHERE f = 'x';
+DROP TABLE t1;
+
+--echo #
+--echo # Print cost_for_plan and rows_for_plan for join prefix
+--echo #
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table one_k (a int, b int, key(b));
+insert into one_k select A.a + B.a*10 + C.a*100, A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C;
+
+analyze table t0, one_k persistent for all;
+
+set @tmp_jcl=@@join_cache_level;
+set join_cache_level=0;
+set optimizer_trace=1;
+
+--echo # Check cost/row numbers when multiple tables are joined
+--echo #  (cost_for_plan is the same as best_access_path.cost for single-table SELECTs
+--echo #   but for joins using condition selectivity it is not as trivial. So,
+--echo #   now we are printing it)
+explain select * from t0 A, one_k B where A.a<5 and B.a<800;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+set join_cache_level=@tmp_jcl;
+
+--echo # This shows post-join selectivity
+explain select * from t0 A, one_k B where A.a=B.b and B.a<800;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t0, one_k;
+
+--echo #
+--echo # Assertion `to_len >= 8' failed in convert_to_printable
+--echo #
+
+CREATE TABLE t1 ( a blob, KEY (a(255)));
+insert into t1 values ('foo'), ('bar');
+
+EXPLAIN SELECT * FROM t1 WHERE a= REPEAT('a', 0);
+SELECT * FROM t1 WHERE a= REPEAT('a', 0);
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+DROP TABLE t1;
+
+--echo #
+--echo # Test for Semi-Join table pullout element
+--echo #
+create table t1 (a int primary key, b int);
+insert into t1 (a) values (1),(2),(3),(4),(5);
+
+create table t2 (a int primary key, b int);
+insert into t2 (a) values (1),(2),(3),(4),(5);
+
+create table t3 (a int);
+insert into t3 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+
+explain 
+select * from t3 where (a,a) in (select t1.a, t2.a from t1, t2 where t1.b=t2.b);
+
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+
+drop table t1,t2,t3;
+
+--echo #
+--echo # MDEV-22401: Optimizer trace: multi-component range is not printed correctly
+--echo #
+
+create table t1 (kp1 int, kp2 int, key(kp1, kp2));
+insert into t1 values (1,1),(1,5),(5,1),(5,5);
+set optimizer_trace=1;
+select * from t1 force index(kp1) where (kp1=2 and kp2 >=4);
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+--echo #
+--echo # MDEV-21626: Optimizer misses the details about the picked join order
+--echo #
+
+CREATE TABLE t1(a INT, b INT, key(a));
+INSERT INTO t1 SELECT seq, seq from seq_1_to_10;
+CREATE TABLE t2(a INT, b INT, key(a));
+INSERT INTO t2 SELECT seq, seq from seq_1_to_100;
+
+SET OPTIMIZER_TRACE=1;
+EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a ORDER BY t2.b;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+DROP TABLE t1,t2;
+
+--echo #
+--echo # MDEV-22665: Print ranges in the optimizer trace created for non-indexed columns when
+--echo # optimizer_use_condition_selectivity >2
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+INSERT INTO t1 SELECT seq, seq from seq_1_to_100;
+SET optimizer_trace=1;
+ANALYZE TABLE t1 PERSISTENT FOR ALL;
+EXPLAIN EXTENDED SELECT * from t1 WHERE a between 1 and 5 and b <= 5;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+EXPLAIN EXTENDED SELECT * from t1 WHERE a != 5;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+EXPLAIN EXTENDED SELECT * from t1 WHERE b >= 10 and b < 25;
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+drop table t1;
+
+--echo #
+--echo # MDEV-22910:SIGSEGV in Opt_trace_context::is_started & SIGSEGV in Json_writer::add_table_name
+--echo # (on optimized builds)
+--echo #
+
+CREATE TABLE t1( a INT, b INT, PRIMARY KEY( a ) );
+SELECT sum(b), row_number() OVER (order by b) FROM t1 WHERE a = 101;
+UPDATE t1 SET b=10 WHERE a=1;
+SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) AS JS from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+DROP TABLE t1;
+
+set optimizer_trace='enabled=off';
+
+--echo #
+--echo # MDEV-24975 Server consumes extra 4G memory upon querying INFORMATION_SCHEMA.OPTIIMIZER_TRACE
+--echo #
+set max_session_mem_used=1024*1024*1024;
+--disable_result_log
+select count(*) from information_schema.optimizer_trace;
+select * from information_schema.optimizer_trace;
+--enable_result_log
+set max_session_mem_used=default;
+
+--echo #
+--echo # MDEV-22380 Assertion `name.length == strlen(name.str)' failed in Item::print_item_w_name on SELECT w/ optimizer_trace enabled
+--echo #
+--disable_view_protocol
+SET optimizer_trace="enabled=on";
+SELECT 'a\0' LIMIT 0;
+SELECT query, trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+SET optimizer_trace=DEFAULT;
+--enable_view_protocol
+
+--echo #
+--echo # MDEV-27238: Assertion `got_name == named_item_expected()' failed in Json_writer::on_start_object
+--echo #
+
+CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=MEMORY;
+SET optimizer_trace=1;
+INSERT INTO t1 VALUES (0,0);
+SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d);
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-30964: MAX_SEL_ARG memory exhaustion is not visible in the optimizer trace
+--echo #
+create table t1 (
+  c1 int,
+  c2 int,
+  c3 int,
+  c4 int,
+  c5 int,
+  c6 int,
+  c7 int,
+  c8 int,
+  key(c1,c2,c3,c4,c5,c6,c7,c8)
+);
+insert into t1 () values (),(),();
+
+explain select *
+from t1
+where
+  (c1 in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16) and c2=1) and
+  c3 in (1,2,3,4,5,6,7,8,9,10) and
+  c4 in (1,2,3,4,5,6,7,8,9,10) and
+  c5 in (1,2,3,4,5,6,7,8,9,10) and
+  c6 in (1,2,3,4);
+
+select
+  json_detailed(json_extract(trace, '$**.setup_range_conditions'))
+from
+  information_schema.optimizer_trace;
+
+drop table t1;
+
+--echo #
+--echo # MDEV-31085: multi-update using view with optimizer trace enabled
+--echo #
+
+SET SESSION optimizer_trace = 'enabled=on';
+
+CREATE TABLE t (a int, b int);
+CREATE VIEW v AS SELECT 1 AS c UNION SELECT 2 AS c;
+INSERT INTO t VALUES (0,4),(5,6);
+UPDATE t, v SET t.b = t.a, t.a = v.c WHERE v.c < t.a;
+SELECT * FROM information_schema.optimizer_trace;
+
+SELECT * FROM t;
+
+SET optimizer_trace=DEFAULT;
+
+DROP VIEW v;
+DROP TABLE t;
+
+--echo #
+--echo # MDEV-26301: Split optimization improvements: Optimizer Trace coverage
+--echo #
+
+# 5 values
+create table t1(a int, b int);
+insert into t1 select seq,seq from seq_1_to_5;
+ 
+# 5 value groups of size 2 each
+create table t2(a int, b int, key(a));
+insert into t2
+select A.seq,B.seq from seq_1_to_25 A, seq_1_to_2 B;
+ 
+# 5 value groups of size 3 each
+create table t3(a int, b int, key(a));
+insert into t3
+select A.seq,B.seq from seq_1_to_5 A, seq_1_to_3 B;
+ 
+analyze table t1,t2,t3 persistent for all;
+
+create table t10 (
+  grp_id int,
+  col1 int,
+  key(grp_id)
+);
+ 
+# 100 groups of 100 values each
+insert into t10
+select
+  A.seq,
+  B.seq
+from
+  seq_1_to_100 A,
+  seq_1_to_100 B;
+ 
+# and X10 multiplier
+create table t11 (
+  col1 int,
+  col2 int
+);
+insert into t11
+select A.seq, A.seq from seq_1_to_10 A;
+ 
+analyze table t10,t11 persistent for all;
+
+set optimizer_trace=1;
+explain
+select * from
+  (
+    (t1 left join t2 on t2.a=t1.b)
+    left join t3 on t3.a=t1.b
+  ) left join (select grp_id, count(*)
+               from t10 left join t11 on t11.col1=t10.col1
+               group by grp_id) T on T.grp_id=t1.b;
+
+# Not sure how MDEV-27871 is related but this test uses this reason
+# all over the place:
+#enable after fix MDEV-27871
+--disable_view_protocol
+
+# Enable after fix MDEV-31408
+# On the first creation of the view from information_schema.optimizer_trace
+# everything is fine, but on the second creation of the view is
+# from information_schema.optimizer_trace the result of select
+# from this view is already returned NULL.
+# That's why view-protocol is disabled here
+
+--disable_view_protocol
+
+select json_detailed(json_extract(trace, '$**.check_split_materialized')) as JS
+from information_schema.optimizer_trace;
+
+select 
+  json_detailed(
+    json_remove(
+      json_extract(trace, '$**.choose_best_splitting')
+      , '$[0].split_plan_search[0]'
+    )
+  ) as JS
+from information_schema.optimizer_trace;
+
+--enable_view_protocol
+drop table t1,t2,t3,t10,t11;
+set optimizer_trace=DEFAULT;
+
+--echo #
+--echo # End of 10.4 tests
+--echo #
+
+set optimizer_trace='enabled=on';
+
+--echo #
+--echo # Test many rows to see output of big cost numbers
+--echo #
+
+--disable_view_protocol
+select count(*) from seq_1_to_10000000;
+select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+--enable_view_protocol
+
+--echo #
+--echo # MDEV-22891: Optimizer trace: const tables are not clearly visible
+--echo #
+create table t0(a int primary key);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (pk int primary key, a int);
+insert into t1 select a,a from t0;
+
+create table t2 (pk int primary key, a int);
+insert into t2 select a,a from t0;
+
+create table t3 (pk int primary key, a int);
+insert into t3 select a,a from t0;
+
+explain
+select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null;
+
+#enable after fix MDEV-27871
+--disable_view_protocol
+select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const'))
+from information_schema.optimizer_trace;
+--enable_view_protocol
+
+drop table t0, t1, t2, t3;
+
+--echo #
+--echo # MDEV-23767: IN-to-subquery conversion is not visible in optimizer trace
+--echo #
+
+#enable after fix MDEV-27871
+--disable_view_protocol
+create table t0 (a int);
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+set @tmp=@@in_predicate_conversion_threshold;
+set in_predicate_conversion_threshold=3;
+
+explain select * from t0 where a in (1,2,3,4,5,6);
+
+select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
+from information_schema.optimizer_trace;
+
+explain select * from t0 where a in (1,2,3,4,5,a+1);
+
+select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
+from information_schema.optimizer_trace;
+
+explain select * from t0 where a in ('1','2','3','4','5','6');
+select json_detailed(json_extract(trace, '$**.in_to_subquery_conversion'))
+from information_schema.optimizer_trace;
+
+set in_predicate_conversion_threshold=@tmp;
+drop table t0;
+--enable_view_protocol
+
+--echo #
+--echo # End of 10.5 tests
+--echo #
+
+--echo #
+--echo # MDEV-23677: Optimizer trace ... (test coverage)
+--echo #
+create table t1(a int, b int, c int, primary key (a,b,c));
+insert into t1 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4);
+
+create table t2 (a int, b int);
+insert into t2 values (1,1),(2,2);
+
+create table t3 (a int, b int, c int);
+insert into t3 values (0,0,0),(1,1,1),(2,2,2),(3,3,3),(4,4,4);
+
+explain select * from t2,t1,t3 where t2.b= t1.b and t1.a=t3.a;
+
+set @trace=(select trace from information_schema.optimizer_trace);
+set @path= (select json_search(@trace, 'one', 'no predicate for first keypart'));
+set @sub_path= substr(@path, 2, locate('.best_access_path', @path)-2);
+
+select @sub_path;
+select 
+  json_detailed(json_extract(
+     @trace,
+     concat(@sub_path,'.best_access_path.considered_access_paths[0]')
+  )) as S;
+
+drop table t1,t2,t3;
+
+--echo #
+--echo # MDEV-23645: Optimizer trace: print conditions after substitute_for_best_equal_field
+--echo #
+create table t1 (a int, b int, c int);
+insert into t1 values (1,1,1),(2,2,2);
+
+create table t2 as select * from t1;
+insert into t2 select * from t2;
+
+create table t3 as select * from t2;
+insert into t3 select * from t3;
+
+--echo # Check how HAVING is printed
+explain 
+select 
+  a,b, count(*)
+from t1
+where a=3
+group by b,b
+having a+b < 10;
+
+select
+  json_detailed(json_extract(trace, '$**.substitute_best_equal'))
+from
+  information_schema.optimizer_trace;
+
+--echo # Check ON expression
+explain 
+select 
+ *
+from t1 left join t2 on t2.a=t1.a and t2.a<3 
+where 
+  t1.b > 5555;
+
+select
+  json_detailed(json_extract(trace, '$**.substitute_best_equal'))
+from
+  information_schema.optimizer_trace;
+
+--echo # Check nested ON expression
+explain 
+select 
+ *
+from t1 left join (t2,t3) on t2.a=t1.a and t3.a=t2.a and t3.a + t2.a <1000
+where 
+  t1.b > 5555;
+
+--disable_view_protocol
+select
+  json_detailed(json_extract(trace, '$**.substitute_best_equal'))
+from
+  information_schema.optimizer_trace;
+--enable_view_protocol
+
+--echo # The next query is test for:
+--echo # MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing
+select
+  json_detailed(json_extract(trace, '$**.condition_processing'))
+from
+  information_schema.optimizer_trace;
+
+drop table t1,t2,t3;
+
+
+--echo #
+--echo # MDEV-24325: Optimizer trace doesn't cover LATERAL DERIVED
+--echo #
+create table t1 (a int, b int, index idx_b(b)) engine=myisam;
+insert into t1 values
+(8,3), (5,7), (1,2), (2,1), (9,7), (7,5), (2,2), (7,3),
+(9,3), (8,1), (4,5), (2,3);
+
+create table t2 (a int, b int, c char(127), index idx_a(a)) engine=myisam;
+insert into t2 values
+  (7,10,'x'), (1,20,'a'), (2,23,'b'), (7,18,'z'), (1,30,'c'),
+  (4,71,'d'), (3,15,'x'), (7,82,'y'), (8,12,'t'), (4,15,'b'),
+  (11,33,'a'), (10,42,'u'), (4,53,'p'), (10,17,'r'), (2,90,'x'),
+  (17,10,'s'), (11,20,'v'), (12,23,'y'), (17,18,'a'), (11,30,'d'),
+  (24,71,'h'), (23,15,'i'), (27,82,'k'), (28,12,'p'), (24,15,'q'),
+  (31,33,'f'), (30,42,'h'), (40,53,'m'), (30,17,'o'), (21,90,'b'),
+  (37,10,'e'), (31,20,'g'), (32,23,'f'), (37,18,'n'), (41,30,'l'),
+  (54,71,'j'), (53,15,'w'), (57,82,'z'), (58,12,'k'), (54,15,'p'),
+  (61,33,'c'), (60,42,'a'), (62,53,'x'), (67,17,'g'), (64,90,'v');
+
+insert into t2 select a+10, b+10, concat(c,'f') from t2;
+
+analyze table t1,t2;
+
+explain
+select t1.a,t.s,t.m
+from t1 join
+     (select a, sum(t2.b) as s, min(t2.c) as m from t2 group by t2.a) t
+     on t1.a=t.a
+where t1.b < 3;
+
+#
+# Just show that choose_best_splitting function has coverage in the 
+# optimizer trace and re-optmization of child select inside it is distinct 
+# from the rest of join optimization.
+--disable_view_protocol
+select
+  json_detailed(json_extract(trace, '$**.choose_best_splitting')) 
+from
+  information_schema.optimizer_trace;
+--enable_view_protocol
+
+drop table t1,t2;
+
+--echo #
+--echo # Test table functions.
+--echo #
+
+CREATE TABLE t1(id INT, f1 JSON);
+INSERT INTO t1 VALUES (1, '{\"1\": 1}'), (2, '{\"1\": 2}'), (3, '{\"1\": 3}'),
+       (4, '{\"1\": 4}'), (5, '{\"1\": 5}'), (6, '{\"1\": 6}');
+
+--disable_view_protocol
+SELECT * FROM t1 WHERE id IN
+  (SELECT id FROM t1 as tt2,
+         JSON_TABLE(f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl);
+--enable_view_protocol
+
+select json_detailed(json_extract(trace, '$**.best_join_order'))
+    from information_schema.OPTIMIZER_TRACE;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace
+--echo #
+set optimizer_trace=0;
+set statement optimizer_trace=1 for select * from seq_1_to_10 where seq<2;
+--echo # The trace must not be empty:
+select left(trace, 100) from information_schema.optimizer_trace;
+set optimizer_trace='enabled=off';
+
+--echo # End of 10.6 tests
+