path: root/mysql-test/main/opt_trace.test

--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 # MDEV-29298: INSERT ... SELECT Does not produce an optimizer trace
--echo #
create table t1 (a int, b int);
create table t2 (a int, b int);
insert into t1 values (1,1), (2,2), (3,3), (4,4), (5,5);
set optimizer_trace=1;

insert into t2 select * from t1 where a<= b and a>4;

select QUERY, LENGTH(trace)>1 from information_schema.optimizer_trace;

drop table t1, t2;

--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