diff options
Diffstat (limited to 'mysql-test/main/explain_json.test')
| -rw-r--r-- | mysql-test/main/explain_json.test | 419 |
1 files changed, 419 insertions, 0 deletions
diff --git a/mysql-test/main/explain_json.test b/mysql-test/main/explain_json.test new file mode 100644 index 00000000000..c3665b1818b --- /dev/null +++ b/mysql-test/main/explain_json.test @@ -0,0 +1,419 @@ +# +# EXPLAIN FORMAT=JSON tests. These are tests developed for MariaDB. +# +--disable_warnings +drop table if exists t0,t1,t2; +--enable_warnings + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +explain format=json select * from t0; + +explain format=json select * from t0 where 1>2; + +explain format=json select * from t0 where a<3; + +--echo # Try a basic join +create table t1 (a int, b int, filler char(32), key(a)); +insert into t1 +select + a.a + b.a* 10 + c.a * 100, + a.a + b.a* 10 + c.a * 100, + 'filler' +from t0 a, t0 b, t0 c; + +explain format=json select * from t0,t1 where t1.a=t0.a; + +--echo # Try range and index_merge +create table t2 (a1 int, a2 int, b1 int, b2 int, key(a1,a2), key(b1,b2)); +insert into t2 select a,a,a,a from t1; + +explain format=json select * from t2 where a1<5; + +explain format=json select * from t2 where a1=1 or b1=2; +explain format=json select * from t2 where a1=1 or (b1=2 and b2=3); + +explain format=json select * from t2 where (a1=1 and a2=1) or + (b1=2 and b2=1); + +--echo # Try ref access on two key components + +explain format=json select * from t0,t2 where t2.b1=t0.a and t2.b2=4; + +drop table t1,t2; + +--echo # +--echo # Try a UNION +--echo # +explain format=json select * from t0 A union select * from t0 B; +explain format=json select * from t0 A union all select * from t0 B; + +--echo # +--echo # Subqueries +--echo # +create table t1 (a int, b int); +insert into t1 select a,a from t0; +explain format=json select a, a > (select max(b) from t1 where t1.b=t0.a) from t0; + +explain format=json +select * from t0 where + a > (select max(b) from t1 where t1.b=t0.a) or a < 3 ; + +drop table t1; + +--echo # +--echo # Join buffering +--echo # +create table t1 (a int, b int); +insert into t1 select tbl1.a+10*tbl2.a, tbl1.a+10*tbl2.a from t0 tbl1, t0 tbl2; + +explain format=json +select * from t1 tbl1, t1 tbl2 where tbl1.a=tbl2.a and tbl1.b < 3 and tbl2.b < 5; + +drop table t1; + +--echo # +--echo # Single-table UPDATE/DELETE, INSERT +--echo # +explain format=json delete from t0; +explain format=json delete from t0 where 1 > 2; + +explain format=json delete from t0 where a < 3; + +explain format=json update t0 set a=3 where a in (2,3,4); + +explain format=json insert into t0 values (1); + +create table t1 like t0; +explain format=json insert into t1 values ((select max(a) from t0)); + +drop table t1; + +--echo # +--echo # A derived table +--echo # +create table t1 (a int, b int); +insert into t1 select a,a from t0; +explain format=json +select * from (select a, count(*) as cnt from t1 group by a) as tbl +where cnt>0; + +explain format=json +select * from (select a, count(*) as cnt from t1 group by a) as tbl1, t1 as +tbl2 where cnt=tbl2.a; + +--echo # +--echo # Non-merged semi-join (aka JTBM) +--echo # +explain format=json +select * from t1 where a in (select max(a) from t1 group by b); + +--echo # +--echo # Semi-join Materialization +--echo # +create table t2 like t1; +insert into t2 select * from t1; +explain format=json +select * from t1,t2 where t1.a in ( select a from t0); + +--echo # +--echo # First-Match +--echo # +explain +select * from t2 where t2.a in ( select a from t1 where t1.b=t2.b); +explain format=json +select * from t2 where t2.a in ( select a from t1 where t1.b=t2.b); + +--echo # +--echo # Duplicate Weedout +--echo # +set @tmp= @@optimizer_switch; +set optimizer_switch='firstmatch=off'; +explain +select * from t2 where t2.a in ( select a from t1 where t1.b=t2.b); +explain format=json +select * from t2 where t2.a in ( select a from t1 where t1.b=t2.b); +set optimizer_switch=@tmp; + +drop table t1,t2; + +--echo # +--echo # MRR for range access (no BKA, just MRR) +--echo # +create table t1 (a int, b int, key(a)); +insert into t1 select tbl1.a+10*tbl2.a, 12345 from t0 tbl1, t0 tbl2; +set @tmp= @@optimizer_switch; +set optimizer_switch='mrr=on,mrr_sort_keys=on'; + +explain format=json select * from t1 where a < 3; + +--echo # 'Range checked for each record' +set optimizer_switch=@tmp; +explain format=json +select * from t1 tbl1, t1 tbl2 where tbl2.a < tbl1.b; + +drop table t1; +drop table t0; + +--echo # +--echo # MDEV-7265: "Full scan on NULL key", the join case +--echo # + +CREATE TABLE t1 (a INT, KEY(a)); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); + +EXPLAIN FORMAT=JSON SELECT * FROM t1 AS outer_t1 WHERE a <> ALL ( SELECT a FROM t1, t2 WHERE b <> outer_t1.a ); + +DROP TABLE t1,t2; + +--echo # +--echo # Join's constant expression +--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 select tbl1.a+10*tbl2.a, 1234 from t0 tbl1, t0 tbl2; + +explain format=json +select * from t0 +where + 20000 > all (select max(tbl1.a + tbl2.a) + from t1 tbl1, t1 tbl2 where tbl1.b=tbl2.b); + +drop table t1; +drop table t0; + +--echo # +--echo # MDEV-7264: Assertion `0' failed in subselect_engine::get_identifier() on EXPLAIN JSON +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); + +EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE a <> ALL ( SELECT b FROM t2 ); +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-7927: Server crashes in in Time_and_counter_tracker::incr_loops +--echo # +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1),(2); +EXPLAIN SELECT * FROM t1 WHERE 3 IN ( SELECT 4 UNION SELECT 5 ); +DROP TABLE t1; + + +--echo # +--echo # MDEV-7860: EXPLAIN FORMAT=JSON crashes for loose scan query +--echo # + +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int, d int, key(a,b,c)); +insert into t1 select A.a, B.a, C.a, D.a from t2 A, t2 B, t2 C, t2 D; +explain select count(distinct b) from t1 group by a; +explain format=json select count(distinct b) from t1 group by a; +--source include/analyze-format.inc +analyze format=json select count(distinct b) from t1 group by a; +drop table t1,t2; + +--echo # +--echo # Try both variants of LooseScan (data/queries borrowed from group_min_max.test) +--echo # + +create table t1 ( + a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(248) default ' ' +); + +insert into t1 (a1, a2, b, c, d) values +('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), +('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), +('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), +('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), +('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), +('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), +('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), +('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), +('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), +('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), +('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), +('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), +('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), +('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), +('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), +('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), +('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), +('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), +('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), +('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), +('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), +('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), +('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), +('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), +('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), +('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), +('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), +('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), +('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), +('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), +('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), +('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); + +create index idx_t1_0 on t1 (a1); +create index idx_t1_1 on t1 (a1,a2,b,c); +create index idx_t1_2 on t1 (a1,a2,b); +analyze table t1; + +explain select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); +explain select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); + +explain format=json select count(distinct a1,a2,b) from t1 where (a2 >= 'b') and (b = 'a'); +explain format=json select count(distinct a1,a2,b,c) from t1 where (a2 >= 'b') and (b = 'a') and (c = 'i121'); + +drop table t1; + +--echo # +--echo # MDEV-8786 Wrong result for SELECT FORMAT=JSON * FROM t1 WHERE a=_latin1 0xDF +--echo # +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('a'),('b'); +EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE a=_latin1 0xDF; +DROP TABLE t1; + +--echo # +--echo # MDEV-8785 Wrong results for EXPLAIN EXTENDED...WHERE NULLIF(latin1_col, _utf8'a' COLLATE utf8_bin) IS NOT NULL +--echo # +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('a'),('A'); +EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE NULLIF(a,_utf8'a' COLLATE utf8_bin); +DROP TABLE t1; + +--echo # +--echo # MDEV-7970: EXPLAIN FORMAT=JSON does not print HAVING +--echo # +create table t0(a int); +insert into t0 values (0),(1),(2),(3); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +create table t2 ( + a int, + b int, + key (a) +); +insert into t2 select A.a*1000 + B.a, A.a*1000 + B.a from t0 A, t1 B; +--echo # normal HAVING +explain format=json select a, max(b) as TOP from t2 group by a having TOP > a; +--echo # HAVING is always TRUE (not printed) +explain format=json select a, max(b) as TOP from t2 group by a having 1<>2; +--echo # HAVING is always FALSE (intercepted by message) +explain format=json select a, max(b) as TOP from t2 group by a having 1=2; +--echo # HAVING is absent +explain format=json select a, max(b) as TOP from t2 group by a; +drop table t0, t1, t2; + +--echo # +--echo # MDEV-8829: Assertion `0' failed in Explain_table_access::tag_to_json +--echo # + +--echo # Check ET_CONST_ROW_NOT_FOUND +create table t1 (i int) engine=myisam; +explain +select * from t1; +explain format=json +select * from t1; +analyze format=json +select * from t1; +drop table t1; + +--echo # Check ET_IMPOSSIBLE_ON_CONDITION +create table t1 (a int); +create table t2 (pk int primary key); + +insert into t1 values (1),(2); +insert into t2 values (1),(2); + +explain +select * from t1 left join t2 on t2.pk > 10 and t2.pk < 0; +explain format=json +select * from t1 left join t2 on t2.pk > 10 and t2.pk < 0; +--source include/analyze-format.inc +analyze format=json +select * from t1 left join t2 on t2.pk > 10 and t2.pk < 0; + +--echo # Check ET_NOT_EXISTS: +explain +select * from t1 left join t2 on t2.pk=t1.a where t2.pk is null; +explain format=json +select * from t1 left join t2 on t2.pk=t1.a where t2.pk is null; +--source include/analyze-format.inc +analyze format=json +select * from t1 left join t2 on t2.pk=t1.a where t2.pk is null; + +--echo # Check ET_DISTINCT +explain +select distinct t1.a from t1 join t2 on t2.pk=t1.a; +explain format=json +select distinct t1.a from t1 join t2 on t2.pk=t1.a; +--source include/analyze-format.inc +analyze format=json +select distinct t1.a from t1 join t2 on t2.pk=t1.a; +drop table t1,t2; + +--echo # Check ET_USING_INDEX_CONDITION_BKA +create table t1(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t2(a int); +insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C; + +create table t3(a int, b int); +insert into t3 select a,a from t1; + +create table t4(a int, b int, c int, filler char(100), key (a,b)); +insert into t4 select a,a,a, 'filler-data' from t2; + +set @tmp_optimizer_switch=@@optimizer_switch; +set @tmp_join_cache_level=@@join_cache_level; +set optimizer_switch='mrr=on'; +set join_cache_level=6; +explain +select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1); +explain format=json +select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1); +--source include/analyze-format.inc +analyze format=json +select * from t3,t4 where t3.a=t4.a and (t4.b+1 <= t3.b+1); +set optimizer_switch=@tmp_optimizer_switch; +set join_cache_level=@tmp_join_cache_level; + +drop table t1,t2,t3,t4; + +--echo # +--echo # MDEV-9652: EXPLAIN FORMAT=JSON should show outer_ref_cond +--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 select a,a from t0; + +explain format=json +select a, (select max(a) from t1 where t0.a<5 and t1.b<t0.a) from t0; +drop table t0,t1; + +--echo # +--echo # MDEV-10844: EXPLAIN FORMAT=JSON doesn't show order direction for filesort +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,2),(3,4),(2,3); +explain format=json select * from t1 order by a, b desc; +explain format=json select * from t1 order by a desc, b desc; +explain format=json select * from t1 order by a desc, b ; +drop table t1; + |