diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2020-01-20 15:49:48 +0200 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2020-01-20 15:49:48 +0200 |
commit | 87a61355e8e499baf7908862711c26aa0225bf32 (patch) | |
tree | 7122052c801b5f1ee4a93af2348fad916aa2f86b /mysql-test/main/show_explain.result | |
parent | 7ea413ac2d80c7f03d1dbad90ac30ecddd8b2835 (diff) | |
parent | bc43bf3e430c20bc2178e584215bd443054709d6 (diff) | |
download | mariadb-git-87a61355e8e499baf7908862711c26aa0225bf32.tar.gz |
Merge 10.3 into 10.4
The MDEV-17062 fix in commit c4195305b2a8431f39a4c75cc1c66ba43685f7a0
was omitted.
Diffstat (limited to 'mysql-test/main/show_explain.result')
-rw-r--r-- | mysql-test/main/show_explain.result | 178 |
1 files changed, 89 insertions, 89 deletions
diff --git a/mysql-test/main/show_explain.result b/mysql-test/main/show_explain.result index 32364d0ec89..1f44f7bc5ed 100644 --- a/mysql-test/main/show_explain.result +++ b/mysql-test/main/show_explain.result @@ -23,7 +23,7 @@ show explain for $thr1; ERROR HY000: Target is not running an EXPLAINable command connection con1; set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; select count(*) from t1 where a < 100000; connection default; show explain for $thr2; @@ -58,10 +58,10 @@ connection con1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan set optimizer_switch= @show_expl_tmp; -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # UNION, first branch set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; explain select a from t0 A union select a+1 from t0 B; connection default; show explain for $thr2; @@ -76,10 +76,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 10 2 UNION B ALL NULL NULL NULL NULL 10 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # UNION, second branch set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; explain select a from t0 A union select a+1 from t0 B; connection default; show explain for $thr2; @@ -94,10 +94,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 10 2 UNION B ALL NULL NULL NULL NULL 10 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # Uncorrelated subquery, select set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; select a, (select max(a) from t0 B) from t0 A where a<1; connection default; show explain for $thr2; @@ -109,10 +109,10 @@ Note 1003 select a, (select max(a) from t0 B) from t0 A where a<1 connection con1; a (select max(a) from t0 B) 0 9 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # Uncorrelated subquery, explain set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; explain select a, (select max(a) from t0 B) from t0 A where a<1; connection default; show explain for $thr2; @@ -125,10 +125,10 @@ connection con1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where 2 SUBQUERY B ALL NULL NULL NULL NULL 10 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # correlated subquery, select set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; connection default; show explain for $thr2; @@ -140,10 +140,10 @@ Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a connection con1; a (select max(a) from t0 b where b.a+a.a<10) 0 9 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # correlated subquery, explain set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; connection default; show explain for $thr2; @@ -155,10 +155,10 @@ Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a connection con1; a (select max(a) from t0 b where b.a+a.a<10) 0 9 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # correlated subquery, select, while inside the subquery set @show_explain_probe_select_id=2; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; connection default; show explain for $thr2; @@ -170,10 +170,10 @@ Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a connection con1; a (select max(a) from t0 b where b.a+a.a<10) 0 9 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # correlated subquery, explain, while inside the subquery set @show_explain_probe_select_id=2; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; connection default; show explain for $thr2; @@ -185,10 +185,10 @@ Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a connection con1; a (select max(a) from t0 b where b.a+a.a<10) 0 9 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # correlated subquery, explain, while inside the subquery set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_end'; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; connection default; show explain for $thr2; @@ -200,12 +200,12 @@ Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a connection con1; a (select max(a) from t0 b where b.a+a.a<10) 0 9 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # Try to do SHOW EXPLAIN for a query that runs a SET command: # I've found experimentally that select_id==2 here... # set @show_explain_probe_select_id=2; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; set @foo= (select max(a) from t0 where sin(a) >0); connection default; show explain for $thr2; @@ -213,13 +213,13 @@ ERROR HY000: Target is not running an EXPLAINable command kill query $thr2; connection con1; ERROR 70100: Query execution was interrupted -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # # Attempt SHOW EXPLAIN for an UPDATE # create table t2 as select a as a, a as dummy from t0 limit 2; set @show_explain_probe_select_id=2; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ; connection default; show explain for $thr2; @@ -236,13 +236,13 @@ Warnings: Note 1003 update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 connection con1; drop table t2; -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # # Attempt SHOW EXPLAIN for a DELETE (UPD: now works) # create table t2 as select a as a, a as dummy from t0 limit 2; set @show_explain_probe_select_id=2; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ; connection default; show explain for $thr2; @@ -259,13 +259,13 @@ Warnings: Note 1003 delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 connection con1; drop table t2; -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # # Multiple SHOW EXPLAIN calls for one select # create table t2 as select a as a, a as dummy from t0 limit 3; set @show_explain_probe_select_id=2; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2; connection default; show explain for $thr2; @@ -292,14 +292,14 @@ a SUBQ 1 0 2 0 drop table t2; -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # # SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort" # explain select * from t0 order by a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using filesort -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; set @show_explain_probe_select_id=1; select * from t0 order by a; connection default; @@ -320,7 +320,7 @@ a 7 8 9 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # # SHOW EXPLAIN for SELECT ... with "Using temporary" # @@ -329,7 +329,7 @@ explain select distinct a from t0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary connection con1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; set @show_explain_probe_select_id=1; select distinct a from t0; connection default; @@ -350,7 +350,7 @@ a 7 8 9 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # # SHOW EXPLAIN for SELECT ... with "Using temporary; Using filesort" # @@ -359,7 +359,7 @@ explain select distinct a from t0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary connection con1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; set @show_explain_probe_select_id=1; select distinct a from t0; connection default; @@ -380,7 +380,7 @@ a 7 8 9 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # # MDEV-238: SHOW EXPLAIN: Server crashes in JOIN::print_explain with FROM subquery and GROUP BY # @@ -390,7 +390,7 @@ explain SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) -set debug_dbug='+d,show_explain_in_find_all_keys'; +SET debug_dbug='+d,show_explain_in_find_all_keys'; SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a; connection default; # FIXED by "conservative assumptions about when QEP is available" fix: @@ -406,7 +406,7 @@ a 1 2 4 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; DROP TABLE t2; # # MDEV-239: Assertion `field_types == 0 ... ' failed in Protocol_text::store(double, uint32, String*) with @@ -421,7 +421,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a` set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_end'; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ; connection default; show explain for $thr2; @@ -436,7 +436,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a` -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; DROP TABLE t2; # # MDEV-240: SHOW EXPLAIN: Assertion `this->optimized == 2' failed in @@ -453,7 +453,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 2 DERIVED t3 system NULL NULL NULL NULL 1 set @show_explain_probe_select_id=2; -set debug_dbug='+d,show_explain_probe_join_exec_end'; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; SELECT * FROM v1, t2; connection default; show explain for $thr2; @@ -461,14 +461,14 @@ ERROR HY000: Target is not running an EXPLAINable command kill query $thr2; connection con1; ERROR 70100: Query execution was interrupted -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; DROP VIEW v1; DROP TABLE t2, t3; # # MDEV-267: SHOW EXPLAIN: Server crashes in JOIN::print_explain on most of queries # set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_end'; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; select sleep(1); connection default; show explain for $thr2; @@ -479,12 +479,12 @@ Note 1003 select sleep(1) connection con1; sleep(1) 0 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # # Same as above, but try another reason for JOIN to be degenerate # set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_end'; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; select * from t0 where 1>10; connection default; show explain for $thr2; @@ -494,14 +494,14 @@ Warnings: Note 1003 select * from t0 where 1>10 connection con1; a -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # # Same as above, but try another reason for JOIN to be degenerate (2) # create table t3(a int primary key); insert into t3 select a from t0; set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_end'; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; select * from t0,t3 where t3.a=112233; connection default; show explain for $thr2; @@ -511,7 +511,7 @@ Warnings: Note 1003 select * from t0,t3 where t3.a=112233 connection con1; a a -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; drop table t3; # # MDEV-270: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with @@ -530,7 +530,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t2 const PRIMARY PRIMARY 4 const 1 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_do_select'; +SET debug_dbug='+d,show_explain_probe_do_select'; SELECT * FROM t2 WHERE a = (SELECT MAX(a) FROM t2 WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3) @@ -552,7 +552,7 @@ pk a 6 7 7 7 9 7 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; drop table t2; # # MDEV-273: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with impossible WHERE @@ -584,7 +584,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 87 Using join buffer (flat, BNL join) 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_do_select'; +SET debug_dbug='+d,show_explain_probe_do_select'; SELECT count(*) FROM t2, t3 WHERE a1 < ALL ( SELECT a1 FROM t2 @@ -605,7 +605,7 @@ WHERE a1 IN ( SELECT a1 FROM t2, t4 ) connection con1; count(*) 1740 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; drop table t2, t3, t4; # # MDEV-275: SHOW EXPLAIN: server crashes in JOIN::print_explain with IN subquery and aggregate function @@ -615,7 +615,7 @@ INSERT INTO t2 VALUES (1,5),(2,4),(3,6),(4,9),(5,2),(6,8),(7,4),(8,8),(9,0),(10,43), (11,23),(12,3),(13,45),(14,16),(15,2),(16,33),(17,2),(18,5),(19,9),(20,2); set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_end'; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; SELECT * FROM t2 WHERE (5, 78) IN (SELECT `a1`, MAX(`a1`) FROM t2 GROUP BY `a1`); connection default; show explain for $thr2; @@ -627,7 +627,7 @@ Warnings: Note 1003 SELECT * FROM t2 WHERE (5, 78) IN (SELECT `a1`, MAX(`a1`) FROM t2 GROUP BY `a1`) connection con1; pk a1 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; DROP TABLE t2; DROP TABLE t1; # @@ -636,7 +636,7 @@ DROP TABLE t1; CREATE TABLE t1(a INT, KEY(a)); INSERT INTO t1 VALUES (3),(1),(5),(1); set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; SELECT 'test' FROM t1 WHERE a=1; connection default; show explain for $thr2; @@ -648,7 +648,7 @@ connection con1; test test test -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; DROP TABLE t1; # # MDEV-299: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN changes back and forth during query execution @@ -666,7 +666,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where 1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1) set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_test_if_quick_select'; +SET debug_dbug='+d,show_explain_probe_test_if_quick_select'; select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100; connection default; show explain for $thr2; @@ -696,7 +696,7 @@ Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND connection con1; count(*) 212 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; drop table t1; # # MDEV-297: SHOW EXPLAIN: Server gets stuck until timeout occurs while @@ -705,7 +705,7 @@ drop table t1; CREATE TABLE t1(a INT, b INT, c INT, KEY(a), KEY(b), KEY(c)); INSERT INTO t1 (a) VALUES (3),(1),(5),(1); set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; SHOW INDEX FROM t1; connection default; show explain for $thr2; @@ -718,7 +718,7 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par t1 1 a 1 a A NULL NULL NULL YES BTREE t1 1 b 1 b A NULL NULL NULL YES BTREE t1 1 c 1 c A NULL NULL NULL YES BTREE -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; DROP TABLE t1; # # MDEV-324: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN for a query with TEMPTABLE view @@ -731,7 +731,7 @@ EXPLAIN SELECT a + 1 FROM v1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 2 DERIVED t1 ALL NULL NULL NULL NULL 2 -set debug_dbug='+d,show_explain_probe_join_tab_preread'; +SET debug_dbug='+d,show_explain_probe_join_tab_preread'; set @show_explain_probe_select_id=1; SELECT a + 1 FROM v1; connection default; @@ -745,7 +745,7 @@ connection con1; a + 1 2 3 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; DROP VIEW v1; DROP TABLE t1; # @@ -761,7 +761,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL -set debug_dbug='+d,show_explain_probe_union_read'; +SET debug_dbug='+d,show_explain_probe_union_read'; SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 ); connection default; show explain for $thr2; @@ -782,7 +782,7 @@ Warnings: Note 1003 SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 ) connection con1; a -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; DROP TABLE t1; # # MDEV-327: SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN @@ -805,7 +805,7 @@ id select_type table type possible_keys key key_len ref rows Extra 3 SUBQUERY t1 ALL NULL NULL NULL NULL 20 3 SUBQUERY t2 ALL NULL NULL NULL NULL 20 Using where set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ); connection default; @@ -820,7 +820,7 @@ Note 1003 SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ) connection con1; a b -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; DROP TABLE t1, t2; # # Test that SHOW EXPLAIN will print 'Distinct'. @@ -842,7 +842,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary 1 SIMPLE t3 ref a a 5 test.t1.a 7 Using index; Distinct set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; select distinct t1.a from t1,t3 where t1.a=t3.a; connection default; show explain for $thr2; @@ -855,7 +855,7 @@ connection con1; a 1 2 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; drop table t1,t3,t4; # # ---------- SHOW EXPLAIN and permissions ----------------- @@ -869,7 +869,7 @@ connection con1; # First, make sure that user 'test2' cannot do SHOW EXPLAIN on us # set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; select * from t0 where a < 3; connection default; connection con2; @@ -886,14 +886,14 @@ a 0 1 2 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; # # Check that user test2 can do SHOW EXPLAIN on its own queries # connect con3, localhost, test2,,; connection con2; set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; select * from t0 where a < 3; connection con1; connection con3; @@ -916,9 +916,9 @@ disconnect con2; grant process on *.* to test2@localhost; connect con2, localhost, test2,,; connection con1; -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; select * from t0 where a < 3; connection default; connection con2; @@ -932,7 +932,7 @@ a 0 1 2 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; revoke all privileges on test.* from test2@localhost; drop user test2@localhost; disconnect con2; @@ -1009,7 +1009,7 @@ ORDER BY b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 8 Using sort_union(a,b); Using where; Using filesort set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; SELECT a+SLEEP(0.01) FROM t1 WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 ORDER BY b; @@ -1030,9 +1030,9 @@ a+SLEEP(0.01) 0 0 0 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_do_select'; +SET debug_dbug='+d,show_explain_probe_do_select'; SELECT a+SLEEP(0.01) FROM t1 WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 ORDER BY b; @@ -1053,7 +1053,7 @@ a+SLEEP(0.01) 0 0 0 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; drop table t1; # # MDEV-298: SHOW EXPLAIN: Plan returned by SHOW EXPLAIN only contains @@ -1067,7 +1067,7 @@ EXPLAIN SELECT a FROM t1 GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4112 Using temporary; Using filesort set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; SELECT a FROM t1 GROUP BY a; connection default; show explain for $thr2; @@ -1093,7 +1093,7 @@ a 14 15 16 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; drop table t1; # # MDEV-408: SHOW EXPLAIN: Some values are chopped off in SHOW EXPLAIN output @@ -1107,7 +1107,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 2 DEPENDENT SUBQUERY t2 index_subquery PRIMARY,c c 5 func 1 Using index; Using where set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; SELECT SUM(a + SLEEP(0.1)) FROM t1 WHERE a IN ( SELECT c FROM t2 WHERE d < b ) OR b < 's'; connection default; show explain for $thr2; @@ -1119,7 +1119,7 @@ Note 1003 SELECT SUM(a + SLEEP(0.1)) FROM t1 WHERE a IN ( SELECT c FROM t2 WHERE connection con1; SUM(a + SLEEP(0.1)) 7862 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; drop table t1, t2; # # MDEV-412: SHOW EXPLAIN: Server crashes in JOIN::print_explain on a query with inner join and ORDER BY the same column twice @@ -1157,7 +1157,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range b b 6 NULL 107 Using where; Using index 1 SIMPLE t3 ref PRIMARY PRIMARY 5 test.t1.b 1 Using index set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_do_select'; +SET debug_dbug='+d,show_explain_probe_do_select'; SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2; connection default; show explain for $thr2; @@ -1169,7 +1169,7 @@ Warnings: Note 1003 SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2 connection con1; field1 field2 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; DROP TABLE t1,t2,t3; # # MDEV-423: SHOW EXPLAIN: 'Using where' for a subquery is shown in EXPLAIN, but not in SHOW EXPLAIN output @@ -1190,7 +1190,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 20 3 SUBQUERY t3 ALL NULL NULL NULL NULL 20 Using where set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; SELECT max(a+b+c) FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias WHERE EXISTS ( SELECT * FROM t3 WHERE b = c ) OR a <= 10; connection default; @@ -1205,7 +1205,7 @@ WHERE EXISTS ( SELECT * FROM t3 WHERE b = c ) OR a <= 10 connection con1; max(a+b+c) 279 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; DROP TABLE t1,t2,t3; # # MDEV-416: Server crashes in SQL_SELECT::cleanup on EXPLAIN with SUM ( DISTINCT ) in a non-correlated subquery (5.5-show-explain tree) @@ -1231,7 +1231,7 @@ select hex('ãû'); hex('ãû') E3FB set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; select * from t0 where length('ãû') = a; connection default; set names utf8; @@ -1244,7 +1244,7 @@ set names default; connection con1; a 2 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; set names default; # # MDEV-462: SHOW EXPLAIN: Assertion `table_list->table' fails in find_field_in_table_ref if FOR contains a non-numeric value @@ -1270,7 +1270,7 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DEPENDENT SUBQUERY t1 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1) 4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 set @show_explain_probe_select_id=1; -set debug_dbug='+d,show_explain_probe_join_exec_start'; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; SELECT SUM(b) FROM ( SELECT * FROM t1 ) AS alias1, t2 WHERE b <= ANY ( SELECT a FROM t1 @@ -1290,7 +1290,7 @@ WHERE a = b + SLEEP(0.2) OR a >= ( SELECT SUM(b) FROM t2 )) connection con1; SUM(b) 0 -set debug_dbug=@old_debug; +SET debug_dbug=@old_debug; DROP TABLE t1,t2; drop table t0; # @@ -1302,7 +1302,7 @@ create table t1 (a int, b int); insert into t1 select a,a from t0; create table t2 as select * from t1; set @show_explain_probe_select_id=2; -set debug_dbug='+d,show_explain_probe_best_ext_lim_search'; +SET debug_dbug='+d,show_explain_probe_best_ext_lim_search'; explain select * from t0 where not exists ( select 1 from t1, t2 where t1.b=t2.b and t2.a=t0.a) and a is null; |