-- 
--  Partition pruning tests. Currently we only detect which partitions to
--  prune, so the test is EXPLAINs.
-- 
--   source include/have_partition.inc
--   source include/default_optimizer_switch.inc

--disable_warnings
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
--enable_warnings

--echo #
--echo # Bug#53806: Wrong estimates for range query in partitioned MyISAM table
--echo # Bug#46754: 'rows' field doesn't reflect partition pruning
--echo #
CREATE TABLE t1 (a INT PRIMARY KEY)
PARTITION BY RANGE (a) (
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2),
PARTITION p2 VALUES LESS THAN (3),
PARTITION p3 VALUES LESS THAN (4),
PARTITION p4 VALUES LESS THAN (5),
PARTITION p5 VALUES LESS THAN (6),
PARTITION max VALUES LESS THAN MAXVALUE);

INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);

--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= 1;
set optimizer_trace=1;
SELECT * FROM t1 WHERE a <= 1;
select trace from information_schema.optimizer_trace into @trace;
select json_detailed(json_extract(@trace, '$**.steps[*].join_optimization.steps[*].partition_prune') );
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < 7;
set optimizer_trace=1;
SELECT * FROM t1 WHERE a < 7;
select trace from information_schema.optimizer_trace into @trace;
select json_detailed(json_extract(@trace, '$**.steps[*].join_optimization.steps[*].partition_prune') );
--replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= 2;
set optimizer_trace=1;
SELECT * FROM t1 WHERE a >= 2;
select trace from information_schema.optimizer_trace into @trace;
select json_detailed(json_extract(@trace, '$**.steps[*].join_optimization.steps[*].partition_prune') );
DROP TABLE t1;

