maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #05802
Re: Please review: EXPLAIN UPDATE patch + refactoring
Now with attachment.
On Fri, Jun 28, 2013 at 09:18:56PM +0400, Sergei Petrunia wrote:
> Hello Igor,
>
> Please review the attached patch against current 10.0-base which contains
> - EXPLAIN UPDATE/DELETE support
> - "Query Plan Footprint" refactoring.
>
> The tree is at lp:~maria-captains/maria/10.0-base-explain-update-r2
>
>
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
=== modified file 'libmysqld/CMakeLists.txt'
--- libmysqld/CMakeLists.txt 2013-03-11 15:02:40 +0000
+++ libmysqld/CMakeLists.txt 2013-06-21 13:12:55 +0000
@@ -99,6 +99,7 @@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc l
../sql/sql_expression_cache.cc
../sql/my_apc.cc ../sql/my_apc.h
../sql/rpl_gtid.cc
+ ../sql/opt_qpf.cc ../sql/opt_qpf.h
${GEN_SOURCES}
${MYSYS_LIBWRAP_SOURCE}
)
=== modified file 'mysql-test/disabled.def'
--- mysql-test/disabled.def 2013-04-15 16:16:54 +0000
+++ mysql-test/disabled.def 2013-06-21 18:50:25 +0000
@@ -16,4 +16,4 @@ read_many_rows_innodb : Bug#11748886
archive-big : Bug#11817185 2011-03-10 Anitha Disabled since this leads to timeout on Solaris Sparc
log_tables-big : Bug#11756699 2010-11-15 mattiasj report already exists
mysql_embedded : Bug#12561297 2011-05-14 Anitha Dependent on PB2 changes - eventum#41836
-show_explain : Psergey: random timeout in range-checked-for-each record query.
+#show_explain : Psergey: random timeout in range-checked-for-each record query.
=== modified file 'mysql-test/r/derived_opt.result'
--- mysql-test/r/derived_opt.result 2012-11-22 09:19:31 +0000
+++ mysql-test/r/derived_opt.result 2013-06-21 13:12:55 +0000
@@ -109,8 +109,8 @@ count(*)
2
explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE A ALL NULL NULL NULL NULL 2 Using where
-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.A.E2 1 Using where
+1 PRIMARY A ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.A.E2 1 Using where
3 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 2 Using where
drop table t1;
create table t1 (a int);
=== modified file 'mysql-test/r/derived_view.result'
--- mysql-test/r/derived_view.result 2013-03-27 22:41:02 +0000
+++ mysql-test/r/derived_view.result 2013-06-21 13:12:55 +0000
@@ -386,7 +386,7 @@ materialized derived in merged derived
explain extended select * from (select * from
(select * from t1 where f1 < 7 group by f1) tt where f1 > 2) zz;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where
3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort
Warnings:
Note 1003 select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2)
@@ -429,8 +429,8 @@ join
(select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) z
on x.f1 = z.f1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where
-1 SIMPLE <derived5> ref key0 key0 5 tt.f1 2 100.00
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where
+1 PRIMARY <derived5> ref key0 key0 5 tt.f1 2 100.00
5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort
3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort
Warnings:
@@ -522,7 +522,7 @@ materialized view in merged derived
explain extended
select * from ( select * from v1 where f1 < 7) tt;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where
3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort
Warnings:
Note 1003 select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`v1` where (`v1`.`f1` < 7)
@@ -568,8 +568,8 @@ f1 f11
join of above two
explain extended select * from v6 join v7 on f2=f1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t2 ALL NULL NULL NULL NULL 11 100.00 Using where
-1 SIMPLE <derived5> ref key0 key0 5 test.t2.f2 2 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 11 100.00 Using where
+1 PRIMARY <derived5> ref key0 key0 5 test.t2.f2 2 100.00
5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort
Warnings:
Note 1003 select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22`,`v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`t2` join `test`.`v1` where ((`v1`.`f1` = `test`.`t2`.`f2`) and (`test`.`t2`.`f2` < 7) and (`test`.`t2`.`f2` in (2,3)))
@@ -1220,7 +1220,7 @@ Note 1003 select `test`.`t1`.`b` AS `b`
EXPLAIN EXTENDED
SELECT * FROM (SELECT b FROM v2 WHERE b = 0) t WHERE b;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort
Warnings:
Note 1003 select `v2`.`b` AS `b` from `test`.`v2` where 0
=== added file 'mysql-test/r/explain_non_select.result'
--- mysql-test/r/explain_non_select.result 1970-01-01 00:00:00 +0000
+++ mysql-test/r/explain_non_select.result 2013-06-21 13:12:55 +0000
@@ -0,0 +1,104 @@
+drop table if exists t0, t1;
+create table t0 (a int) engine=myisam;
+insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+#
+# Tests for single-table DELETE
+#
+explain select * from t0 where a=3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 8 Using where
+explain delete from t0 where a=3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 8 Using where
+# DELETE without WHERE is a special case:
+explain delete from t0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Deleting all rows
+create table t1 (a int, b int, filler char(100), key(a), key(b));
+insert into t1
+select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler'
+from t0 A, t0 B, t0 C;
+# This should use an index, possible_keys=NULL because there is no WHERE
+explain delete from t1 order by a limit 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL a NULL NULL 512
+# This should use range, possible_keys={a,b}
+explain delete from t1 where a<20 and b < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a,b a 5 NULL 1 Using where
+# This should use ALL + filesort
+explain delete from t1 order by a+1 limit 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 512 Using filesort
+# This should use range + using filesort
+explain delete from t1 where a<20 order by b limit 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 1 Using where; Using filesort
+# Try some subqueries:
+explain delete from t1 where a < (select max(a) from t0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range a a 5 NULL 1 Using where
+2 SUBQUERY t0 ALL NULL NULL NULL NULL 8
+explain delete from t1 where a < (select max(a) from t0 where a < t1.b);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 512 Using where
+2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 8 Using where
+#
+# Tests for multi-table DELETE
+#
+explain delete t1 from t0, t1 where t0.a = t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 8 Using where
+1 SIMPLE t1 ref a a 5 test.t0.a 4 Using index
+drop table t0, t1;
+# ###################################################################
+# ## EXPLAIN UPDATE tests
+# ###################################################################
+create table t0 (a int) engine=myisam;
+insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+explain update t0 set a=3 where a=4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 8 Using where
+create table t1 (a int, b int, filler char(100), key(a), key(b));
+insert into t1
+select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler'
+from t0 A, t0 B, t0 C;
+explain update t1 set a=a+1 where 3>4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible where
+explain update t1 set a=a+1 where a=3 and a=4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible where
+# This should use an index, possible_keys=NULL because there is no WHERE
+explain update t1 set a=a+1 order by a limit 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 512
+# This should use range, possible_keys={a,b}
+explain update t1 set filler='fooo' where a<20 and b < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a,b a 5 NULL 1 Using where
+# This should use ALL + filesort
+explain update t1 set filler='fooo' order by a+1 limit 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 512
+# This should use range + using filesort
+explain update t1 set filler='fooo' where a<20 order by b limit 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 1 Using where
+# Try some subqueries:
+explain update t1 set filler='fooo' where a < (select max(a) from t0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range a a 5 NULL 1 Using where
+2 SUBQUERY t0 ALL NULL NULL NULL NULL 8
+explain update t1 set filler='fooo' where a < (select max(a) from t0 where a < t1.b);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 512 Using where
+2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 8 Using where
+#
+# Tests for multi-table UPDATE
+#
+explain update t0, t1 set t1.a=t1.a+1 where t0.a = t1.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 8 Using where
+1 SIMPLE t1 ref a a 5 test.t0.a 4 Using index
+drop table t0, t1;
=== modified file 'mysql-test/r/limit_rows_examined.result'
--- mysql-test/r/limit_rows_examined.result 2012-10-25 12:50:10 +0000
+++ mysql-test/r/limit_rows_examined.result 2013-06-21 13:12:55 +0000
@@ -439,7 +439,7 @@ from (select * from t1
where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0)) as tmp
LIMIT ROWS EXAMINED 11;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
3 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where
select *
from (select * from t1
=== modified file 'mysql-test/r/selectivity.result'
--- mysql-test/r/selectivity.result 2013-04-20 09:16:55 +0000
+++ mysql-test/r/selectivity.result 2013-06-21 13:12:55 +0000
@@ -393,7 +393,7 @@ and not exists (select * from orders whe
group by cntrycode
order by cntrycode;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort
+1 PRIMARY customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort
4 DEPENDENT SUBQUERY orders ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index
3 SUBQUERY customer ALL NULL NULL NULL NULL 150 100.00 Using where
Warnings:
@@ -434,7 +434,7 @@ and not exists (select * from orders whe
group by cntrycode
order by cntrycode;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort
+1 PRIMARY customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort
4 DEPENDENT SUBQUERY orders ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index
3 SUBQUERY customer ALL NULL NULL NULL NULL 150 91.00 Using where
Warnings:
=== modified file 'mysql-test/r/selectivity_innodb.result'
--- mysql-test/r/selectivity_innodb.result 2013-04-20 09:16:55 +0000
+++ mysql-test/r/selectivity_innodb.result 2013-06-21 13:12:55 +0000
@@ -396,7 +396,7 @@ and not exists (select * from orders whe
group by cntrycode
order by cntrycode;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort
+1 PRIMARY customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort
4 DEPENDENT SUBQUERY orders ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index
3 SUBQUERY customer ALL NULL NULL NULL NULL 150 100.00 Using where
Warnings:
@@ -437,7 +437,7 @@ and not exists (select * from orders whe
group by cntrycode
order by cntrycode;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort
+1 PRIMARY customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort
4 DEPENDENT SUBQUERY orders ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index
3 SUBQUERY customer ALL NULL NULL NULL NULL 150 91.00 Using where
Warnings:
=== modified file 'mysql-test/r/show_explain.result'
--- mysql-test/r/show_explain.result 2013-04-02 16:23:08 +0000
+++ mysql-test/r/show_explain.result 2013-06-27 14:39:34 +0000
@@ -165,7 +165,11 @@ set @show_explain_probe_select_id=1;
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;
show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+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 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where
+Warnings:
+Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
a (select max(a) from t0 b where b.a+a.a<10)
0 9
set debug_dbug=@old_debug;
@@ -186,22 +190,38 @@ set @show_explain_probe_select_id=2;
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 ;
show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where
+Warnings:
+Note 1003 update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where
+Warnings:
+Note 1003 update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
drop table t2;
set debug_dbug=@old_debug;
#
-# Attempt SHOW EXPLAIN for a DELETE
+# 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';
delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where
+Warnings:
+Note 1003 delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where
+Warnings:
+Note 1003 delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
drop table t2;
set debug_dbug=@old_debug;
#
@@ -220,13 +240,13 @@ Note 1003 select t2.a, ((select max(a) f
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 3
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Query plan already deleted
+2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where
Warnings:
Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 3
-2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Query plan already deleted
+2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where
Warnings:
Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
a SUBQ
@@ -327,7 +347,11 @@ SELECT alias.a FROM t2, ( SELECT * FROM
# FIXED by "conservative assumptions about when QEP is available" fix:
# NOTE: current code will not show "Using join buffer":
show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+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)
+Warnings:
+Note 1003 SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a
a
1
2
@@ -412,7 +436,10 @@ set @show_explain_probe_select_id=1;
set debug_dbug='+d,show_explain_probe_join_exec_end';
select * from t0 where 1>10;
show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+Warnings:
+Note 1003 select * from t0 where 1>10
a
set debug_dbug=@old_debug;
#
@@ -424,7 +451,10 @@ set @show_explain_probe_select_id=1;
set debug_dbug='+d,show_explain_probe_join_exec_end';
select * from t0,t3 where t3.a=112233;
show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+Warnings:
+Note 1003 select * from t0,t3 where t3.a=112233
a a
set debug_dbug=@old_debug;
drop table t3;
@@ -529,7 +559,12 @@ set @show_explain_probe_select_id=1;
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`);
show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery2> const distinct_key distinct_key 8 const,const 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
+2 MATERIALIZED t2 index NULL a1 4 NULL 20 Using index
+Warnings:
+Note 1003 SELECT * FROM t2 WHERE (5, 78) IN (SELECT `a1`, MAX(`a1`) FROM t2 GROUP BY `a1`)
pk a1
set debug_dbug=@old_debug;
DROP TABLE t2;
@@ -635,7 +670,7 @@ SELECT a + 1 FROM v1;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
-2 DERIVED NULL NULL NULL NULL NULL NULL NULL Query plan already deleted
+2 DERIVED t1 ALL NULL NULL NULL NULL 2
Warnings:
Note 1003 SELECT a + 1 FROM v1
a + 1
@@ -1045,7 +1080,7 @@ show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY alias1 ALL NULL NULL NULL NULL 14
1 PRIMARY t2 ALL NULL NULL NULL NULL 20
-3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Query plan already deleted
+3 SUBQUERY t3 ALL NULL NULL NULL NULL 20 Using where
Warnings:
Note 1003 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
=== added file 'mysql-test/r/show_explain_non_select.result'
--- mysql-test/r/show_explain_non_select.result 1970-01-01 00:00:00 +0000
+++ mysql-test/r/show_explain_non_select.result 2013-06-21 13:12:55 +0000
@@ -0,0 +1,44 @@
+drop table if exists t0, t1;
+SET @old_debug= @@session.debug;
+set debug_sync='RESET';
+create table t0 (a int) engine=myisam;
+insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+create table t1 (a int, b int, filler char(100), key(a), key(b));
+insert into t1
+select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler'
+from t0 A, t0 B, t0 C;
+#
+# Test SHOW EXPLAIN for single-table DELETE
+#
+set debug_dbug='+d,show_explain_probe_delete_exec_start';
+delete from t1 where a<10 and b+1>1000;
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 1 Using where
+Warnings:
+Note 1003 delete from t1 where a<10 and b+1>1000
+#
+# Test SHOW EXPLAIN for multi-table DELETE
+#
+set @show_explain_probe_select_id=1;
+set debug_dbug='+d,show_explain_probe_do_select';
+delete t1 from t1, t0 where t0.a=t1.a and t1.b +1 > 1000;
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 8 Using where
+1 SIMPLE t1 ref a a 5 test.t0.a 4 Using where
+Warnings:
+Note 1003 delete t1 from t1, t0 where t0.a=t1.a and t1.b +1 > 1000
+#
+# Test SHOW EXPLAIN for single-table UPDATE
+#
+set debug_dbug='+d,show_explain_probe_update_exec_start';
+update t1 set filler='filler-data-2' where a<10 and b+1>1000;
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 1 Using where
+Warnings:
+Note 1003 update t1 set filler='filler-data-2' where a<10 and b+1>1000
+drop table t0,t1;
+set debug_dbug=@old_debug;
+set debug_sync='RESET';
=== modified file 'mysql-test/r/subselect.result'
--- mysql-test/r/subselect.result 2013-03-31 22:18:55 +0000
+++ mysql-test/r/subselect.result 2013-06-21 13:12:55 +0000
@@ -10,7 +10,7 @@ select (select 2);
2
explain extended select (select 2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select 2 AS `(select 2)`
@@ -734,7 +734,7 @@ id
1
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
+1 SIMPLE t2 ref id id 5 const 1 100.00 Using index
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
@@ -746,7 +746,7 @@ id
2
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
+1 SIMPLE t2 ref id id 5 const 1 100.00 Using where; Using index
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
@@ -880,7 +880,7 @@ select 10.5 > ANY (SELECT * from t1);
1
explain extended select (select a+1) from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1249 Select 2 was reduced during optimization
@@ -4554,7 +4554,7 @@ int_nokey int_key
0 0
EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY C ALL NULL NULL NULL NULL 20 100.00 Using where
+1 SIMPLE C ALL NULL NULL NULL NULL 20 100.00 Using where
DROP TABLE C;
# End of test for bug#45061.
#
@@ -6112,7 +6112,7 @@ FROM t1 AS sq4_alias1
WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
sq4_alias1.col_varchar_key = @var3 ) AS alias3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found
+1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
FROM t1 AS sq4_alias1
@@ -6157,7 +6157,7 @@ FROM t2 AS c_sq1_alias1
WHERE (c_sq1_alias1.col_int_nokey != @var2
OR c_sq1_alias1.pk != @var3)) ) AS alias3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
SELECT * FROM ( SELECT sq4_alias1.*
FROM t1 AS sq4_alias1
=== modified file 'mysql-test/r/subselect2.result'
--- mysql-test/r/subselect2.result 2012-11-04 15:09:46 +0000
+++ mysql-test/r/subselect2.result 2013-06-21 13:12:55 +0000
@@ -258,7 +258,7 @@ WHERE date < '2012-12-12 12:12:12'
ORDER BY mirror_date ASC
) AS calculated_result;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE <derived2> ALL NULL NULL NULL NULL 2
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
2 DERIVED t1 range date date 9 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort
SELECT * FROM (
SELECT node_uid, date, mirror_date, @result := 0 AS result
@@ -281,7 +281,7 @@ WHERE date < '2012-12-12 12:12:12'
ORDER BY mirror_date ASC
) AS calculated_result;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE <derived2> ALL NULL NULL NULL NULL 2
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
2 DERIVED t1 range date date 9 NULL 2 Using index condition; Using where; Using filesort
SELECT * FROM (
SELECT node_uid, date, mirror_date, @result := 0 AS result
=== modified file 'mysql-test/r/subselect3.result'
--- mysql-test/r/subselect3.result 2013-03-27 22:41:02 +0000
+++ mysql-test/r/subselect3.result 2013-06-27 12:17:52 +0000
@@ -1031,7 +1031,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Start temporary; Using temporary; Using filesort
1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join)
1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; End temporary; Using join buffer (flat, BNL join)
1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (flat, BNL join)
=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- mysql-test/r/subselect3_jcl6.result 2013-03-27 22:41:02 +0000
+++ mysql-test/r/subselect3_jcl6.result 2013-06-27 12:18:10 +0000
@@ -1041,7 +1041,7 @@ update t22 set c = '2005-12-08 15:58:27'
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Start temporary; Using temporary; Using filesort
1 PRIMARY t12 hash_ALL NULL #hash#$hj 4 test.t11.a 8 Using where; Using join buffer (flat, BNLH join)
1 PRIMARY t22 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; End temporary; Using join buffer (incremental, BNLH join)
1 PRIMARY t21 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; Using join buffer (incremental, BNLH join)
=== modified file 'mysql-test/r/subselect4.result'
--- mysql-test/r/subselect4.result 2013-03-31 22:18:55 +0000
+++ mysql-test/r/subselect4.result 2013-06-21 13:12:55 +0000
@@ -93,12 +93,12 @@ DROP TABLE t1,t2;
#
EXPLAIN SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1249 Select 2 was reduced during optimization
DESCRIBE SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1249 Select 2 was reduced during optimization
# None of the below should crash
@@ -520,8 +520,8 @@ id select_type table type possible_keys
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5
-1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
DEALLOCATE PREPARE stmt;
@@ -558,8 +558,8 @@ id select_type table type possible_keys
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5
-1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1
+1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
DEALLOCATE PREPARE stmt;
@@ -595,8 +595,8 @@ id select_type table type possible_keys
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 5
-1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5
+1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where
2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
DEALLOCATE PREPARE stmt;
=== modified file 'mysql-test/r/subselect_exists_to_in.result'
--- mysql-test/r/subselect_exists_to_in.result 2013-03-31 22:18:55 +0000
+++ mysql-test/r/subselect_exists_to_in.result 2013-06-21 13:12:55 +0000
@@ -14,7 +14,7 @@ select (select 2);
2
explain extended select (select 2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select 2 AS `(select 2)`
@@ -738,7 +738,7 @@ id
1
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
+1 SIMPLE t2 ref id id 5 const 1 100.00 Using index
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
@@ -750,7 +750,7 @@ id
2
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
+1 SIMPLE t2 ref id id 5 const 1 100.00 Using where; Using index
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
@@ -884,7 +884,7 @@ select 10.5 > ANY (SELECT * from t1);
1
explain extended select (select a+1) from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1249 Select 2 was reduced during optimization
@@ -4560,7 +4560,7 @@ int_nokey int_key
0 0
EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY C ALL NULL NULL NULL NULL 20 100.00 Using where
+1 SIMPLE C ALL NULL NULL NULL NULL 20 100.00 Using where
DROP TABLE C;
# End of test for bug#45061.
#
@@ -6120,7 +6120,7 @@ FROM t1 AS sq4_alias1
WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
sq4_alias1.col_varchar_key = @var3 ) AS alias3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found
+1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
FROM t1 AS sq4_alias1
@@ -6165,7 +6165,7 @@ FROM t2 AS c_sq1_alias1
WHERE (c_sq1_alias1.col_int_nokey != @var2
OR c_sq1_alias1.pk != @var3)) ) AS alias3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
SELECT * FROM ( SELECT sq4_alias1.*
FROM t1 AS sq4_alias1
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- mysql-test/r/subselect_no_mat.result 2013-03-31 22:18:55 +0000
+++ mysql-test/r/subselect_no_mat.result 2013-06-21 13:12:55 +0000
@@ -17,7 +17,7 @@ select (select 2);
2
explain extended select (select 2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select 2 AS `(select 2)`
@@ -741,7 +741,7 @@ id
1
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
+1 SIMPLE t2 ref id id 5 const 1 100.00 Using index
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
@@ -753,7 +753,7 @@ id
2
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
+1 SIMPLE t2 ref id id 5 const 1 100.00 Using where; Using index
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
@@ -887,7 +887,7 @@ select 10.5 > ANY (SELECT * from t1);
1
explain extended select (select a+1) from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1249 Select 2 was reduced during optimization
@@ -4556,7 +4556,7 @@ int_nokey int_key
0 0
EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY C ALL NULL NULL NULL NULL 20 100.00 Using where
+1 SIMPLE C ALL NULL NULL NULL NULL 20 100.00 Using where
DROP TABLE C;
# End of test for bug#45061.
#
@@ -6111,7 +6111,7 @@ FROM t1 AS sq4_alias1
WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
sq4_alias1.col_varchar_key = @var3 ) AS alias3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found
+1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
FROM t1 AS sq4_alias1
@@ -6156,7 +6156,7 @@ FROM t2 AS c_sq1_alias1
WHERE (c_sq1_alias1.col_int_nokey != @var2
OR c_sq1_alias1.pk != @var3)) ) AS alias3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
SELECT * FROM ( SELECT sq4_alias1.*
FROM t1 AS sq4_alias1
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- mysql-test/r/subselect_no_opts.result 2013-03-31 22:18:55 +0000
+++ mysql-test/r/subselect_no_opts.result 2013-06-21 13:12:55 +0000
@@ -13,7 +13,7 @@ select (select 2);
2
explain extended select (select 2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select 2 AS `(select 2)`
@@ -737,7 +737,7 @@ id
1
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
+1 SIMPLE t2 ref id id 5 const 1 100.00 Using index
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
@@ -749,7 +749,7 @@ id
2
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
+1 SIMPLE t2 ref id id 5 const 1 100.00 Using where; Using index
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
@@ -883,7 +883,7 @@ select 10.5 > ANY (SELECT * from t1);
1
explain extended select (select a+1) from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1249 Select 2 was reduced during optimization
@@ -4552,7 +4552,7 @@ int_nokey int_key
0 0
EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY C ALL NULL NULL NULL NULL 20 100.00 Using where
+1 SIMPLE C ALL NULL NULL NULL NULL 20 100.00 Using where
DROP TABLE C;
# End of test for bug#45061.
#
@@ -6107,7 +6107,7 @@ FROM t1 AS sq4_alias1
WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
sq4_alias1.col_varchar_key = @var3 ) AS alias3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found
+1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
FROM t1 AS sq4_alias1
@@ -6152,7 +6152,7 @@ FROM t2 AS c_sq1_alias1
WHERE (c_sq1_alias1.col_int_nokey != @var2
OR c_sq1_alias1.pk != @var3)) ) AS alias3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
SELECT * FROM ( SELECT sq4_alias1.*
FROM t1 AS sq4_alias1
=== modified file 'mysql-test/r/subselect_no_scache.result'
--- mysql-test/r/subselect_no_scache.result 2013-03-31 22:18:55 +0000
+++ mysql-test/r/subselect_no_scache.result 2013-06-21 13:12:55 +0000
@@ -16,7 +16,7 @@ select (select 2);
2
explain extended select (select 2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select 2 AS `(select 2)`
@@ -740,7 +740,7 @@ id
1
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
+1 SIMPLE t2 ref id id 5 const 1 100.00 Using index
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
@@ -752,7 +752,7 @@ id
2
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
+1 SIMPLE t2 ref id id 5 const 1 100.00 Using where; Using index
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
@@ -886,7 +886,7 @@ select 10.5 > ANY (SELECT * from t1);
1
explain extended select (select a+1) from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1249 Select 2 was reduced during optimization
@@ -4560,7 +4560,7 @@ int_nokey int_key
0 0
EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY C ALL NULL NULL NULL NULL 20 100.00 Using where
+1 SIMPLE C ALL NULL NULL NULL NULL 20 100.00 Using where
DROP TABLE C;
# End of test for bug#45061.
#
@@ -6118,7 +6118,7 @@ FROM t1 AS sq4_alias1
WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
sq4_alias1.col_varchar_key = @var3 ) AS alias3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found
+1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
FROM t1 AS sq4_alias1
@@ -6163,7 +6163,7 @@ FROM t2 AS c_sq1_alias1
WHERE (c_sq1_alias1.col_int_nokey != @var2
OR c_sq1_alias1.pk != @var3)) ) AS alias3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
SELECT * FROM ( SELECT sq4_alias1.*
FROM t1 AS sq4_alias1
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- mysql-test/r/subselect_no_semijoin.result 2013-03-31 22:18:55 +0000
+++ mysql-test/r/subselect_no_semijoin.result 2013-06-21 13:12:55 +0000
@@ -13,7 +13,7 @@ select (select 2);
2
explain extended select (select 2);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select 2 AS `(select 2)`
@@ -737,7 +737,7 @@ id
1
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using index
+1 SIMPLE t2 ref id id 5 const 1 100.00 Using index
Warnings:
Note 1249 Select 2 was reduced during optimization
Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1)
@@ -749,7 +749,7 @@ id
2
EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index
+1 SIMPLE t2 ref id id 5 const 1 100.00 Using where; Using index
Warnings:
Note 1249 Select 3 was reduced during optimization
Note 1249 Select 2 was reduced during optimization
@@ -883,7 +883,7 @@ select 10.5 > ANY (SELECT * from t1);
1
explain extended select (select a+1) from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
Note 1249 Select 2 was reduced during optimization
@@ -4552,7 +4552,7 @@ int_nokey int_key
0 0
EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY C ALL NULL NULL NULL NULL 20 100.00 Using where
+1 SIMPLE C ALL NULL NULL NULL NULL 20 100.00 Using where
DROP TABLE C;
# End of test for bug#45061.
#
@@ -6107,7 +6107,7 @@ FROM t1 AS sq4_alias1
WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR
sq4_alias1.col_varchar_key = @var3 ) AS alias3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found
+1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found
2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table
SELECT * FROM ( SELECT @var3:=12, sq4_alias1.*
FROM t1 AS sq4_alias1
@@ -6152,7 +6152,7 @@ FROM t2 AS c_sq1_alias1
WHERE (c_sq1_alias1.col_int_nokey != @var2
OR c_sq1_alias1.pk != @var3)) ) AS alias3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1
SELECT * FROM ( SELECT sq4_alias1.*
FROM t1 AS sq4_alias1
=== modified file 'mysql-test/r/subselect_sj.result'
--- mysql-test/r/subselect_sj.result 2013-05-04 16:42:43 +0000
+++ mysql-test/r/subselect_sj.result 2013-06-27 18:36:59 +0000
@@ -941,9 +941,9 @@ id select_type table type possible_keys
1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
+1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
DROP TABLE t1, t2, t3;
DROP VIEW v2, v3;
# End of Bug#49198
=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- mysql-test/r/subselect_sj_jcl6.result 2013-05-04 16:42:43 +0000
+++ mysql-test/r/subselect_sj_jcl6.result 2013-06-27 18:36:59 +0000
@@ -954,9 +954,9 @@ id select_type table type possible_keys
1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index
-1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
-1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
+1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
+1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index
DROP TABLE t1, t2, t3;
DROP VIEW v2, v3;
# End of Bug#49198
=== modified file 'mysql-test/r/table_elim.result'
--- mysql-test/r/table_elim.result 2012-05-21 18:54:41 +0000
+++ mysql-test/r/table_elim.result 2013-06-21 13:12:55 +0000
@@ -119,27 +119,27 @@ t2 where id=f.id);
This should use one table:
explain select id from v1 where id=2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE f const PRIMARY PRIMARY 4 const 1 Using index
+1 PRIMARY f const PRIMARY PRIMARY 4 const 1 Using index
This should use one table:
explain extended select id from v1 where id in (1,2,3,4);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index
+1 PRIMARY f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index
Warnings:
Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3,4))
This should use facts and a1 tables:
explain extended select id from v1 where attr1 between 12 and 14;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition
-1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index
+1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition
+1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index
Warnings:
Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where ((`f`.`id` = `a1`.`id`) and (`a1`.`attr1` between 12 and 14))
This should use facts, a2 and its subquery:
explain extended select id from v1 where attr2 between 12 and 14;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using where
-1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using index
+1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using where
+1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using index
3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.a2.id 2 100.00 Using index
Warnings:
Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
@@ -147,27 +147,27 @@ Note 1003 select `f`.`id` AS `id` from `
This should use one table:
explain select id from v2 where id=2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE f const PRIMARY PRIMARY 4 const 1 Using index
+1 PRIMARY f const PRIMARY PRIMARY 4 const 1 Using index
This should use one table:
explain extended select id from v2 where id in (1,2,3,4);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index
+1 PRIMARY f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index
Warnings:
Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3,4))
This should use facts and a1 tables:
explain extended select id from v2 where attr1 between 12 and 14;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition
-1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index
+1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition
+1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index
Warnings:
Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where ((`f`.`id` = `a1`.`id`) and (`a1`.`attr1` between 12 and 14))
This should use facts, a2 and its subquery:
explain extended select id from v2 where attr2 between 12 and 14;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition
-1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index
+1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition
+1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index
3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.f.id 2 100.00 Using index
Warnings:
Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
=== modified file 'mysql-test/r/view.result'
--- mysql-test/r/view.result 2013-02-28 21:47:29 +0000
+++ mysql-test/r/view.result 2013-06-27 10:13:24 +0000
@@ -119,7 +119,7 @@ select c from v5;
12
explain extended select c from v5;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE <derived3> ALL NULL NULL NULL NULL 5 100.00
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00
3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select (`v2`.`c` + 1) AS `c` from `test`.`v2`
@@ -4475,8 +4475,8 @@ f1 f1
1 1
EXPLAIN EXTENDED SELECT * FROM v2 AS a1, v2 AS a2;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE <derived3> ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
-1 SIMPLE <derived5> ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
+1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+1 PRIMARY <derived5> ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join)
5 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00
3 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00
Warnings:
=== added file 'mysql-test/t/explain_non_select.test'
--- mysql-test/t/explain_non_select.test 1970-01-01 00:00:00 +0000
+++ mysql-test/t/explain_non_select.test 2013-06-21 13:12:55 +0000
@@ -0,0 +1,86 @@
+#
+# MariaDB tests for EXPLAIN UPDATE/DELETE.
+#
+--disable_warnings
+drop table if exists t0, t1;
+--enable_warnings
+
+create table t0 (a int) engine=myisam;
+insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+
+--echo #
+--echo # Tests for single-table DELETE
+--echo #
+
+explain select * from t0 where a=3;
+explain delete from t0 where a=3;
+
+--echo # DELETE without WHERE is a special case:
+explain delete from t0;
+
+create table t1 (a int, b int, filler char(100), key(a), key(b));
+insert into t1
+select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler'
+from t0 A, t0 B, t0 C;
+
+--echo # This should use an index, possible_keys=NULL because there is no WHERE
+explain delete from t1 order by a limit 2;
+
+--echo # This should use range, possible_keys={a,b}
+explain delete from t1 where a<20 and b < 10;
+
+--echo # This should use ALL + filesort
+explain delete from t1 order by a+1 limit 2;
+
+--echo # This should use range + using filesort
+explain delete from t1 where a<20 order by b limit 2;
+
+--echo # Try some subqueries:
+explain delete from t1 where a < (select max(a) from t0);
+explain delete from t1 where a < (select max(a) from t0 where a < t1.b);
+
+--echo #
+--echo # Tests for multi-table DELETE
+--echo #
+explain delete t1 from t0, t1 where t0.a = t1.a;
+drop table t0, t1;
+
+--echo # ###################################################################
+--echo # ## EXPLAIN UPDATE tests
+--echo # ###################################################################
+create table t0 (a int) engine=myisam;
+insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+
+explain update t0 set a=3 where a=4;
+
+create table t1 (a int, b int, filler char(100), key(a), key(b));
+insert into t1
+select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler'
+from t0 A, t0 B, t0 C;
+
+explain update t1 set a=a+1 where 3>4;
+explain update t1 set a=a+1 where a=3 and a=4;
+
+--echo # This should use an index, possible_keys=NULL because there is no WHERE
+explain update t1 set a=a+1 order by a limit 2;
+
+--echo # This should use range, possible_keys={a,b}
+explain update t1 set filler='fooo' where a<20 and b < 10;
+
+--echo # This should use ALL + filesort
+explain update t1 set filler='fooo' order by a+1 limit 2;
+
+--echo # This should use range + using filesort
+explain update t1 set filler='fooo' where a<20 order by b limit 2;
+
+--echo # Try some subqueries:
+explain update t1 set filler='fooo' where a < (select max(a) from t0);
+explain update t1 set filler='fooo' where a < (select max(a) from t0 where a < t1.b);
+
+--echo #
+--echo # Tests for multi-table UPDATE
+--echo #
+explain update t0, t1 set t1.a=t1.a+1 where t0.a = t1.a;
+
+
+drop table t0, t1;
=== modified file 'mysql-test/t/show_explain.test'
--- mysql-test/t/show_explain.test 2013-04-02 16:23:08 +0000
+++ mysql-test/t/show_explain.test 2013-06-27 14:39:24 +0000
@@ -202,7 +202,6 @@ set debug_dbug='+d,show_explain_probe_jo
send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
connection default;
--source include/wait_condition.inc
---error ER_TARGET_NOT_EXPLAINABLE
evalp show explain for $thr2;
connection con1;
reap;
@@ -238,10 +237,10 @@ set debug_dbug='+d,show_explain_probe_jo
send update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
connection default;
--source include/wait_condition.inc
---error ER_TARGET_NOT_EXPLAINABLE
+#--error ER_TARGET_NOT_EXPLAINABLE
evalp show explain for $thr2;
--source include/wait_condition.inc
---error ER_TARGET_NOT_EXPLAINABLE
+#--error ER_TARGET_NOT_EXPLAINABLE
evalp show explain for $thr2;
connection con1;
reap;
@@ -249,7 +248,7 @@ drop table t2;
set debug_dbug=@old_debug;
--echo #
---echo # Attempt SHOW EXPLAIN for a DELETE
+--echo # Attempt SHOW EXPLAIN for a DELETE (UPD: now works)
--echo #
create table t2 as select a as a, a as dummy from t0 limit 2;
set @show_explain_probe_select_id=2;
@@ -257,10 +256,10 @@ set debug_dbug='+d,show_explain_probe_jo
send delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
connection default;
--source include/wait_condition.inc
---error ER_TARGET_NOT_EXPLAINABLE
+#--error ER_TARGET_NOT_EXPLAINABLE
evalp show explain for $thr2;
--source include/wait_condition.inc
---error ER_TARGET_NOT_EXPLAINABLE
+#--error ER_TARGET_NOT_EXPLAINABLE
evalp show explain for $thr2;
connection con1;
reap;
@@ -349,7 +348,7 @@ connection default;
--source include/wait_condition.inc
--echo # FIXED by "conservative assumptions about when QEP is available" fix:
--echo # NOTE: current code will not show "Using join buffer":
---error ER_TARGET_NOT_EXPLAINABLE
+#--error ER_TARGET_NOT_EXPLAINABLE
evalp show explain for $thr2;
connection con1;
reap;
@@ -428,7 +427,7 @@ set debug_dbug='+d,show_explain_probe_jo
send select * from t0 where 1>10;
connection default;
--source include/wait_condition.inc
---error ER_TARGET_NOT_EXPLAINABLE
+#--error ER_TARGET_NOT_EXPLAINABLE
evalp show explain for $thr2;
connection con1;
reap;
@@ -444,7 +443,7 @@ set debug_dbug='+d,show_explain_probe_jo
send select * from t0,t3 where t3.a=112233;
connection default;
--source include/wait_condition.inc
---error ER_TARGET_NOT_EXPLAINABLE
+# --error ER_TARGET_NOT_EXPLAINABLE
evalp show explain for $thr2;
connection con1;
reap;
@@ -540,7 +539,7 @@ send
SELECT * FROM t2 WHERE (5, 78) IN (SELECT `a1`, MAX(`a1`) FROM t2 GROUP BY `a1`);
connection default;
--source include/wait_condition.inc
---error ER_TARGET_NOT_EXPLAINABLE
+# --error ER_TARGET_NOT_EXPLAINABLE
evalp show explain for $thr2;
connection con1;
reap;
=== added file 'mysql-test/t/show_explain_non_select.test'
--- mysql-test/t/show_explain_non_select.test 1970-01-01 00:00:00 +0000
+++ mysql-test/t/show_explain_non_select.test 2013-06-21 13:12:55 +0000
@@ -0,0 +1,78 @@
+#
+# SHOW EXPLAIN tests for non-select subqueries
+#
+#--source include/have_debug.inc
+#--source include/have_innodb.inc
+#--source include/not_embedded.inc
+
+--disable_warnings
+drop table if exists t0, t1;
+--enable_warnings
+
+SET @old_debug= @@session.debug;
+set debug_sync='RESET';
+
+#
+# Setup two threads and their ids
+#
+let $thr1=`select connection_id()`;
+connect (con2, localhost, root,,);
+connection con2;
+let $thr2=`select connection_id()`;
+connection default;
+
+#
+# Create tables
+#
+create table t0 (a int) engine=myisam;
+insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+
+create table t1 (a int, b int, filler char(100), key(a), key(b));
+insert into t1
+select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler'
+from t0 A, t0 B, t0 C;
+
+let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2;
+
+--echo #
+--echo # Test SHOW EXPLAIN for single-table DELETE
+--echo #
+connection con2;
+set debug_dbug='+d,show_explain_probe_delete_exec_start';
+send delete from t1 where a<10 and b+1>1000;
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con2;
+reap;
+
+--echo #
+--echo # Test SHOW EXPLAIN for multi-table DELETE
+--echo #
+set @show_explain_probe_select_id=1;
+set debug_dbug='+d,show_explain_probe_do_select';
+send delete t1 from t1, t0 where t0.a=t1.a and t1.b +1 > 1000;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con2;
+reap;
+
+--echo #
+--echo # Test SHOW EXPLAIN for single-table UPDATE
+--echo #
+connection con2;
+set debug_dbug='+d,show_explain_probe_update_exec_start';
+send update t1 set filler='filler-data-2' where a<10 and b+1>1000;
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con2;
+reap;
+
+drop table t0,t1;
+
+set debug_dbug=@old_debug;
+set debug_sync='RESET';
=== modified file 'sql/CMakeLists.txt'
--- sql/CMakeLists.txt 2013-06-06 15:51:28 +0000
+++ sql/CMakeLists.txt 2013-06-27 18:36:59 +0000
@@ -80,6 +80,7 @@ SET (SQL_SOURCE
sql_reload.cc
# added in MariaDB:
+ opt_qpf.h opt_qpf.cc
sql_lifo_buffer.h sql_join_cache.h sql_join_cache.cc
create_options.cc multi_range_read.cc
opt_index_cond_pushdown.cc opt_subselect.cc
=== modified file 'sql/log_event.cc'
--- sql/log_event.cc 2013-06-21 09:53:46 +0000
+++ sql/log_event.cc 2013-06-27 18:36:59 +0000
@@ -9064,6 +9064,7 @@ int Rows_log_event::do_apply_event(Relay
call might reset the value of current_stmt_binlog_format, so
we need to do any changes to that value after this function.
*/
+ delete_qpf_query(thd->lex);
lex_start(thd);
mysql_reset_thd_for_next_command(thd, 0);
/*
=== modified file 'sql/my_apc.cc'
--- sql/my_apc.cc 2013-04-07 12:03:43 +0000
+++ sql/my_apc.cc 2013-06-21 13:12:55 +0000
@@ -70,6 +70,7 @@ void Apc_target::enable()
void Apc_target::disable()
{
bool process= FALSE;
+ DBUG_ASSERT(enabled);
mysql_mutex_lock(LOCK_thd_data_ptr);
if (!(--enabled))
process= TRUE;
=== modified file 'sql/my_apc.h'
--- sql/my_apc.h 2013-04-07 12:03:43 +0000
+++ sql/my_apc.h 2013-06-21 13:12:55 +0000
@@ -64,6 +64,8 @@ class Apc_target
{
return test(apc_calls);
}
+
+ inline bool is_enabled() { return enabled; }
/* Functor class for calls you can schedule */
class Apc_call
=== added file 'sql/opt_qpf.cc'
--- sql/opt_qpf.cc 1970-01-01 00:00:00 +0000
+++ sql/opt_qpf.cc 2013-06-27 18:36:59 +0000
@@ -0,0 +1,615 @@
+/*
+ TODO MP AB copyright
+*/
+
+#ifdef USE_PRAGMA_IMPLEMENTATION
+#pragma implementation // gcc: Class implementation
+#endif
+
+#include "sql_priv.h"
+#include "sql_select.h"
+
+
+QPF_query::QPF_query()
+{
+ upd_del_plan= NULL;
+ operations= 0;
+ //memset(&unions, 0, sizeof(unions));
+ //memset(&selects, 0, sizeof(selects));
+}
+
+
+QPF_query::~QPF_query()
+{
+ delete upd_del_plan;
+ uint i;
+ for (i= 0 ; i < unions.elements(); i++)
+ delete unions.at(i);
+ for (i= 0 ; i < selects.elements(); i++)
+ delete selects.at(i);
+}
+
+
+QPF_node *QPF_query::get_node(uint select_id)
+{
+ QPF_union *u;
+ if ((u= get_union(select_id)))
+ return u;
+ else
+ return get_select(select_id);
+}
+
+QPF_union *QPF_query::get_union(uint select_id)
+{
+ return (unions.elements() > select_id) ? unions.at(select_id) : NULL;
+}
+
+QPF_select *QPF_query::get_select(uint select_id)
+{
+ return (selects.elements() > select_id) ? selects.at(select_id) : NULL;
+}
+
+
+void QPF_query::add_node(QPF_node *node)
+{
+ operations++;
+ if (node->get_type() == QPF_node::QPF_UNION)
+ {
+ QPF_union *u= (QPF_union*)node;
+ uint select_id= u->get_select_id();
+ if (unions.elements() <= select_id)
+ unions.resize(max(select_id+1, unions.elements()*2), NULL);
+
+ QPF_union *old_node;
+ if ((old_node= get_union(select_id)))
+ delete old_node;
+
+ unions.at(select_id)= u;
+ }
+ else
+ {
+ QPF_select *sel= (QPF_select*)node;
+ if (sel->select_id == (int)UINT_MAX)
+ {
+ //TODO this is a "fake select" from a UNION.
+ DBUG_ASSERT(0);
+ }
+ else
+ {
+ uint select_id= sel->select_id;
+ QPF_select *old_node;
+ //DBUG_ASSERT(!get_select(select_id));
+ if (selects.elements() <= select_id)
+ selects.resize(max(select_id+1, selects.elements()*2), NULL);
+
+ if ((old_node= get_select(select_id)))
+ delete old_node;
+
+ selects.at(select_id)= sel;
+ }
+ }
+}
+
+
+/*
+ The main entry point to print EXPLAIN of the entire query
+*/
+
+int QPF_query::print_explain(select_result_sink *output,
+ uint8 explain_flags)
+{
+ if (upd_del_plan)
+ {
+ upd_del_plan->print_explain(this, output, explain_flags);
+ return 0;
+ }
+ else
+ {
+ // Start with id=1
+ QPF_node *node= get_node(1);
+ return node->print_explain(this, output, explain_flags);
+ }
+}
+
+
+void QPF_union::push_table_name(List<Item> *item_list)
+{
+}
+
+
+static void push_str(List<Item> *item_list, const char *str)
+{
+ item_list->push_back(new Item_string(str,
+ strlen(str), system_charset_info));
+}
+
+
+static void push_string(List<Item> *item_list, String *str)
+{
+ item_list->push_back(new Item_string(str->ptr(), str->length(),
+ system_charset_info));
+}
+
+
+int QPF_union::print_explain(QPF_query *query, select_result_sink *output,
+ uint8 explain_flags)
+{
+ // print all children, in order
+ for (int i= 0; i < (int) union_members.elements(); i++)
+ {
+ QPF_select *sel= query->get_select(union_members.at(i));
+ sel->print_explain(query, output, explain_flags);
+ }
+
+ /* Print a line with "UNION RESULT" */
+ List<Item> item_list;
+ Item *item_null= new Item_null();
+
+ /* `id` column */
+ item_list.push_back(item_null);
+
+ /* `select_type` column */
+ push_str(&item_list, fake_select_type);
+
+ /* `table` column: something like "<union1,2>" */
+ //
+ {
+ char table_name_buffer[SAFE_NAME_LEN];
+ uint childno= 0;
+ uint len= 6, lastop= 0;
+ memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
+
+ for (; childno < union_members.elements() && len + lastop + 5 < NAME_LEN;
+ childno++)
+ {
+ len+= lastop;
+ lastop= my_snprintf(table_name_buffer + len, NAME_LEN - len,
+ "%u,", union_members.at(childno));
+ }
+
+ if (childno < union_members.elements() || len + lastop >= NAME_LEN)
+ {
+ memcpy(table_name_buffer + len, STRING_WITH_LEN("...>") + 1);
+ len+= 4;
+ }
+ else
+ {
+ len+= lastop;
+ table_name_buffer[len - 1]= '>'; // change ',' to '>'
+ }
+ const CHARSET_INFO *cs= system_charset_info;
+ item_list.push_back(new Item_string(table_name_buffer, len, cs));
+ }
+ //
+ push_table_name(&item_list);
+
+ /* `partitions` column */
+ if (explain_flags & DESCRIBE_PARTITIONS)
+ item_list.push_back(item_null);
+
+ /* `type` column */
+ push_str(&item_list, join_type_str[JT_ALL]);
+
+ /* `possible_keys` column */
+ item_list.push_back(item_null);
+
+ /* `key` */
+ item_list.push_back(item_null);
+
+ /* `key_len` */
+ item_list.push_back(item_null);
+
+ /* `ref` */
+ item_list.push_back(item_null);
+
+ /* `rows` */
+ item_list.push_back(item_null);
+
+ /* `filtered` */
+ if (explain_flags & DESCRIBE_EXTENDED)
+ item_list.push_back(item_null);
+
+ /* `Extra` */
+ StringBuffer<256> extra_buf;
+ if (using_filesort)
+ {
+ extra_buf.append(STRING_WITH_LEN("Using filesort"));
+ }
+ const CHARSET_INFO *cs= system_charset_info;
+ item_list.push_back(new Item_string(extra_buf.ptr(), extra_buf.length(), cs));
+
+ //output->unit.offset_limit_cnt= 0;
+ if (output->send_data(item_list))
+ return 1;
+
+ return print_explain_for_children(query, output, explain_flags);
+}
+
+
+int QPF_node::print_explain_for_children(QPF_query *query,
+ select_result_sink *output,
+ uint8 explain_flags)
+{
+ for (int i= 0; i < (int) children.elements(); i++)
+ {
+ QPF_node *node= query->get_node(children.at(i));
+ if (node->print_explain(query, output, explain_flags))
+ return 1;
+ }
+ return 0;
+}
+
+
+QPF_select::~QPF_select()
+{
+ if (join_tabs)
+ {
+ for (uint i= 0; i< n_join_tabs; i++)
+ delete join_tabs[i];
+ my_free(join_tabs);
+ }
+}
+
+
+int QPF_select::print_explain(QPF_query *query, select_result_sink *output,
+ uint8 explain_flags)
+{
+ //output->unit.offset_limit_cnt= 0;
+
+ if (message)
+ {
+ List<Item> item_list;
+ const CHARSET_INFO *cs= system_charset_info;
+ Item *item_null= new Item_null();
+
+ item_list.push_back(new Item_int((int32) select_id));
+ item_list.push_back(new Item_string(select_type,
+ strlen(select_type), cs));
+ for (uint i=0 ; i < 7; i++)
+ item_list.push_back(item_null);
+ if (explain_flags & DESCRIBE_PARTITIONS)
+ item_list.push_back(item_null);
+ if (explain_flags & DESCRIBE_EXTENDED)
+ item_list.push_back(item_null);
+
+ item_list.push_back(new Item_string(message,strlen(message),cs));
+
+ if (output->send_data(item_list))
+ return 1;
+ }
+ else
+ {
+ bool using_tmp= using_temporary;
+ bool using_fs= using_filesort;
+ for (uint i=0; i< n_join_tabs; i++)
+ {
+ join_tabs[i]->print_explain(output, explain_flags, select_id,
+ select_type, using_tmp, using_fs);
+ if (i == 0)
+ {
+ /*
+ "Using temporary; Using filesort" should only be shown near the 1st
+ table
+ */
+ using_tmp= false;
+ using_fs= false;
+ }
+ }
+ }
+
+ return print_explain_for_children(query, output, explain_flags);
+}
+
+
+int QPF_table_access::print_explain(select_result_sink *output, uint8 explain_flags,
+ uint select_id, const char *select_type,
+ bool using_temporary, bool using_filesort)
+{
+ List<Item> item_list;
+ Item *item_null= new Item_null();
+ //const CHARSET_INFO *cs= system_charset_info;
+
+ if (sjm_nest_select_id)
+ select_id= sjm_nest_select_id;
+
+ /* `id` column */
+ item_list.push_back(new Item_int((int32) select_id));
+
+ /* `select_type` column */
+ if (sjm_nest_select_id)
+ push_str(&item_list, "MATERIALIZED");
+ else
+ push_str(&item_list, select_type);
+
+ /* `table` column */
+ push_string(&item_list, &table_name);
+
+ /* `partitions` column */
+ if (explain_flags & DESCRIBE_PARTITIONS)
+ {
+ if (used_partitions_set)
+ {
+ push_string(&item_list, &used_partitions);
+ }
+ else
+ item_list.push_back(item_null);
+ }
+
+ /* `type` column */
+ push_str(&item_list, join_type_str[type]);
+
+ /* `possible_keys` column */
+ if (possible_keys_str.length() > 0)
+ push_string(&item_list, &possible_keys_str);
+ else
+ item_list.push_back(item_null);
+
+ /* `key` */
+ if (key_set)
+ push_string(&item_list, &key);
+ else
+ item_list.push_back(item_null);
+
+ /* `key_len` */
+ if (key_len_set)
+ push_string(&item_list, &key_len);
+ else
+ item_list.push_back(item_null);
+
+ /* `ref` */
+ if (ref_set)
+ push_string(&item_list, &ref);
+ else
+ item_list.push_back(item_null);
+
+ /* `rows` */
+ if (rows_set)
+ {
+ item_list.push_back(new Item_int((longlong) (ulonglong) rows,
+ MY_INT64_NUM_DECIMAL_DIGITS));
+ }
+ else
+ item_list.push_back(item_null);
+
+ /* `filtered` */
+ if (explain_flags & DESCRIBE_EXTENDED)
+ {
+ if (filtered_set)
+ {
+ item_list.push_back(new Item_float(filtered, 2));
+ }
+ else
+ item_list.push_back(item_null);
+ }
+
+ /* `Extra` */
+ StringBuffer<256> extra_buf;
+ bool first= true;
+ for (int i=0; i < (int)extra_tags.elements(); i++)
+ {
+ if (first)
+ first= false;
+ else
+ extra_buf.append(STRING_WITH_LEN("; "));
+ append_tag_name(&extra_buf, extra_tags.at(i));
+ }
+
+ if (using_temporary)
+ {
+ if (first)
+ first= false;
+ else
+ extra_buf.append(STRING_WITH_LEN("; "));
+ extra_buf.append(STRING_WITH_LEN("Using temporary"));
+ }
+
+ if (using_filesort)
+ {
+ if (first)
+ first= false;
+ else
+ extra_buf.append(STRING_WITH_LEN("; "));
+ extra_buf.append(STRING_WITH_LEN("Using filesort"));
+ }
+
+ const CHARSET_INFO *cs= system_charset_info;
+ item_list.push_back(new Item_string(extra_buf.ptr(), extra_buf.length(), cs));
+
+ if (output->send_data(item_list))
+ return 1;
+
+ return 0;
+}
+
+
+const char * extra_tag_text[]=
+{
+ "ET_none",
+ "Using index condition",
+ "Using index condition(BKA)",
+ "Using ", //special
+ "Range checked for each record (index map: 0x", //special
+ "Using where with pushed condition",
+ "Using where",
+ "Not exists",
+
+ "Using index",
+ "Full scan on NULL key",
+ "Skip_open_table",
+ "Open_frm_only",
+ "Open_full_table",
+
+ "Scanned 0 databases",
+ "Scanned 1 database",
+ "Scanned all databases",
+
+ "Using index for group-by", // Special?
+
+ "USING MRR: DONT PRINT ME", // Special!
+
+ "Distinct",
+ "LooseScan",
+ "Start temporary",
+ "End temporary",
+ "FirstMatch", //TODO: also handle special variant!
+
+ "Using join buffer", // Special!,
+
+ "const row not found",
+ "unique row not found",
+ "Impossible ON condition"
+};
+
+
+void QPF_table_access::append_tag_name(String *str, enum Extra_tag tag)
+{
+ switch (tag) {
+ case ET_USING:
+ {
+ // quick select
+ str->append(STRING_WITH_LEN("Using "));
+ str->append(quick_info);
+ break;
+ }
+ case ET_RANGE_CHECKED_FOR_EACH_RECORD:
+ {
+ /* 4 bits per 1 hex digit + terminating '\0' */
+ char buf[MAX_KEY / 4 + 1];
+ str->append(STRING_WITH_LEN("Range checked for each "
+ "record (index map: 0x"));
+ str->append(range_checked_map.print(buf));
+ str->append(')');
+ break;
+ }
+ case ET_USING_MRR:
+ {
+ str->append(mrr_type);
+ break;
+ }
+ case ET_USING_JOIN_BUFFER:
+ {
+ str->append(extra_tag_text[tag]);
+
+ str->append(STRING_WITH_LEN(" ("));
+ const char *buffer_type= bka_type.incremental ? "incremental" : "flat";
+ str->append(buffer_type);
+ str->append(STRING_WITH_LEN(", "));
+ str->append(bka_type.join_alg);
+ str->append(STRING_WITH_LEN(" join"));
+ str->append(STRING_WITH_LEN(")"));
+ if (bka_type.mrr_type.length())
+ str->append(bka_type.mrr_type);
+
+ break;
+ }
+ case ET_FIRST_MATCH:
+ {
+ if (firstmatch_table_name.length())
+ {
+ str->append("FirstMatch(");
+ str->append(firstmatch_table_name);
+ str->append(")");
+ }
+ else
+ str->append(extra_tag_text[tag]);
+ break;
+ }
+ case ET_USING_INDEX_FOR_GROUP_BY:
+ {
+ str->append(extra_tag_text[tag]);
+ if (loose_scan_is_scanning)
+ str->append(" (scanning)");
+ break;
+ }
+ default:
+ str->append(extra_tag_text[tag]);
+ }
+}
+
+
+int QPF_delete::print_explain(QPF_query *query, select_result_sink *output,
+ uint8 explain_flags)
+{
+ if (deleting_all_rows)
+ {
+ const char *msg= "Deleting all rows";
+ int res= print_explain_message_line(output, explain_flags,
+ 1 /*select number*/,
+ select_type, msg);
+ return res;
+
+ }
+ else
+ {
+ return QPF_update::print_explain(query, output, explain_flags);
+ }
+}
+
+
+int QPF_update::print_explain(QPF_query *query, select_result_sink *output,
+ uint8 explain_flags)
+{
+ StringBuffer<64> extra_str;
+ if (impossible_where)
+ {
+ const char *msg= "Impossible where";
+ int res= print_explain_message_line(output, explain_flags,
+ 1 /*select number*/,
+ select_type, msg);
+ return res;
+ }
+
+ if (using_where)
+ extra_str.append(STRING_WITH_LEN("Using where"));
+
+ if (mrr_type.length() != 0)
+ {
+ if (extra_str.length() !=0)
+ extra_str.append(STRING_WITH_LEN("; "));
+ extra_str.append(mrr_type);
+ }
+
+ if (using_filesort)
+ {
+ if (extra_str.length() !=0)
+ extra_str.append(STRING_WITH_LEN("; "));
+ extra_str.append(STRING_WITH_LEN("Using filesort"));
+ }
+
+ /*
+ Single-table DELETE commands do not do "Using temporary".
+ "Using index condition" is also not possible (which is an unjustified limitation)
+ */
+
+ print_explain_row(output, explain_flags,
+ 1, /* id */
+ select_type,
+ table_name.c_ptr(),
+ // partitions,
+ jtype,
+ possible_keys_line.length()? possible_keys_line.c_ptr(): NULL,
+ key_str.length()? key_str.c_ptr() : NULL,
+ key_len_str.length() ? key_len_str.c_ptr() : NULL,
+ NULL, /* 'ref' is always NULL in single-table EXPLAIN DELETE */
+ rows,
+ extra_str.c_ptr());
+
+ return print_explain_for_children(query, output, explain_flags);
+}
+
+
+void delete_qpf_query(LEX *lex)
+{
+ delete lex->query_plan_footprint;
+ lex->query_plan_footprint= NULL;
+}
+
+
+void create_qpf_query(LEX *lex, MEM_ROOT *mem_root)
+{
+ DBUG_ASSERT(!lex->query_plan_footprint);
+ lex->query_plan_footprint= new QPF_query;
+ DBUG_ASSERT(mem_root == current_thd->mem_root);
+ lex->query_plan_footprint->mem_root= mem_root;
+}
+
=== added file 'sql/opt_qpf.h'
--- sql/opt_qpf.h 1970-01-01 00:00:00 +0000
+++ sql/opt_qpf.h 2013-06-27 18:36:59 +0000
@@ -0,0 +1,418 @@
+/**************************************************************************************
+
+ Query Plan Footprint (QPF) structures
+
+ These structures
+ - Can be produced in-expensively from query plan.
+ - Store sufficient information to produce either a tabular or a json EXPLAIN
+ output
+ - Have methods that produce a tabular output.
+
+*************************************************************************************/
+
+class QPF_query;
+
+/*
+ A node can be either a SELECT, or a UNION.
+*/
+class QPF_node : public Sql_alloc
+{
+public:
+ enum qpf_node_type {QPF_UNION, QPF_SELECT, QPF_UPDATE, QPF_DELETE };
+ virtual enum qpf_node_type get_type()= 0;
+
+
+ virtual int get_select_id()= 0;
+
+ /*
+ A node may have children nodes. When a node's QPF (Query Plan Footprint) is
+ created, children nodes may not yet have QPFs. This is why we store ids.
+ */
+ Dynamic_array<int> children;
+ void add_child(int select_no)
+ {
+ children.append(select_no);
+ }
+
+ virtual int print_explain(QPF_query *query, select_result_sink *output,
+ uint8 explain_flags)=0;
+
+ int print_explain_for_children(QPF_query *query, select_result_sink *output,
+ uint8 explain_flags);
+ virtual ~QPF_node(){}
+};
+
+
+class QPF_table_access;
+
+
+/*
+ Query Plan Footprint of a SELECT.
+
+ A select can be:
+ 1. A degenerate case. In this case, message!=NULL, and it contains a
+ description of what kind of degenerate case it is (e.g. "Impossible
+ WHERE").
+ 2. a non-degenrate join. In this case, join_tabs describes the join.
+
+ In the non-degenerate case, a SELECT may have a GROUP BY/ORDER BY operation.
+
+ In both cases, the select may have children nodes. class QPF_node provides
+ a way get node's children.
+*/
+
+class QPF_select : public QPF_node
+{
+public:
+ enum qpf_node_type get_type() { return QPF_SELECT; }
+
+ QPF_select() :
+ message(NULL), join_tabs(NULL),
+ using_temporary(false), using_filesort(false)
+ {}
+
+ ~QPF_select();
+
+ bool add_table(QPF_table_access *tab)
+ {
+ if (!join_tabs)
+ {
+ join_tabs= (QPF_table_access**) my_malloc(sizeof(QPF_table_access*) *
+ MAX_TABLES, MYF(0));
+ n_join_tabs= 0;
+ }
+ join_tabs[n_join_tabs++]= tab;
+ return false;
+ }
+
+public:
+ int select_id;
+ const char *select_type;
+
+ int get_select_id() { return select_id; }
+
+ /*
+ If message != NULL, this is a degenerate join plan, and all subsequent
+ members have no info
+ */
+ const char *message;
+
+ /*
+ A flat array of Query Plan Footprints. The order is "just like EXPLAIN
+ would print them".
+ */
+ QPF_table_access** join_tabs;
+ uint n_join_tabs;
+
+ /* Global join attributes. In tabular form, they are printed on the first row */
+ bool using_temporary;
+ bool using_filesort;
+
+ int print_explain(QPF_query *query, select_result_sink *output,
+ uint8 explain_flags);
+};
+
+
+/*
+ Query Plan Footprint of a UNION.
+
+ A UNION may or may not have "Using filesort".
+*/
+
+class QPF_union : public QPF_node
+{
+public:
+ enum qpf_node_type get_type() { return QPF_UNION; }
+
+ int get_select_id()
+ {
+ DBUG_ASSERT(union_members.elements() > 0);
+ return union_members.at(0);
+ }
+ /*
+ Members of the UNION. Note: these are different from UNION's "children".
+ Example:
+
+ (select * from t1) union
+ (select * from t2) order by (select col1 from t3 ...)
+
+ here
+ - select-from-t1 and select-from-t2 are "union members",
+ - select-from-t3 is the only "child".
+ */
+ Dynamic_array<int> union_members;
+
+ void add_select(int select_no)
+ {
+ union_members.append(select_no);
+ }
+ void push_table_name(List<Item> *item_list);
+ int print_explain(QPF_query *query, select_result_sink *output,
+ uint8 explain_flags);
+
+ const char *fake_select_type;
+ bool using_filesort;
+};
+
+class QPF_delete;
+
+
+/*
+ Query Plan Footprint (QPF) for a query (i.e. a statement).
+
+ This should be able to survive when the query plan was deleted. Currently,
+ we do not intend for it survive until after query's MEM_ROOT is freed. It
+ does surivive freeing of query's items.
+
+ For reference, the process of post-query cleanup is as follows:
+
+ >dispatch_command
+ | >mysql_parse
+ | | ...
+ | | lex_end()
+ | | ...
+ | | >THD::cleanup_after_query
+ | | | ...
+ | | | free_items()
+ | | | ...
+ | | <THD::cleanup_after_query
+ | |
+ | <mysql_parse
+ |
+ | log_slow_statement()
+ |
+ | free_root()
+ |
+ >dispatch_command
+
+ That is, the order of actions is:
+ - free query's Items
+ - write to slow query log
+ - free query's MEM_ROOT
+
+*/
+
+class QPF_query : public Sql_alloc
+{
+public:
+ QPF_query();
+ ~QPF_query();
+ /* Add a new node */
+ void add_node(QPF_node *node);
+
+ /* This will return a select, or a union */
+ QPF_node *get_node(uint select_id);
+
+ /* This will return a select (even if there is a union with this id) */
+ QPF_select *get_select(uint select_id);
+
+ QPF_union *get_union(uint select_id);
+
+ /* QPF_delete inherits from QPF_update */
+ QPF_update *upd_del_plan;
+
+ /* Produce a tabular EXPLAIN output */
+ int print_explain(select_result_sink *output, uint8 explain_flags);
+
+ /* If true, at least part of EXPLAIN can be printed */
+ bool have_query_plan() { return upd_del_plan!= NULL || get_node(1) != NULL; }
+ MEM_ROOT *mem_root;
+private:
+ Dynamic_array<QPF_union*> unions;
+ Dynamic_array<QPF_select*> selects;
+
+ /*
+ Debugging aid: count how many times add_node() was called. Ideally, it
+ should be one, we currently allow O(1) query plan saves for each
+ select or union. The goal is not to have O(#rows_in_some_table), which
+ is unacceptable.
+ */
+ longlong operations;
+};
+
+
+enum Extra_tag
+{
+ ET_none= 0, /* not-a-tag */
+ ET_USING_INDEX_CONDITION,
+ ET_USING_INDEX_CONDITION_BKA,
+ ET_USING, /* For quick selects of various kinds */
+ ET_RANGE_CHECKED_FOR_EACH_RECORD,
+ ET_USING_WHERE_WITH_PUSHED_CONDITION,
+ ET_USING_WHERE,
+ ET_NOT_EXISTS,
+
+ ET_USING_INDEX,
+ ET_FULL_SCAN_ON_NULL_KEY,
+ ET_SKIP_OPEN_TABLE,
+ ET_OPEN_FRM_ONLY,
+ ET_OPEN_FULL_TABLE,
+
+ ET_SCANNED_0_DATABASES,
+ ET_SCANNED_1_DATABASE,
+ ET_SCANNED_ALL_DATABASES,
+
+ ET_USING_INDEX_FOR_GROUP_BY,
+
+ ET_USING_MRR, // does not print "Using mrr".
+
+ ET_DISTINCT,
+ ET_LOOSESCAN,
+ ET_START_TEMPORARY,
+ ET_END_TEMPORARY,
+ ET_FIRST_MATCH,
+
+ ET_USING_JOIN_BUFFER,
+
+ ET_CONST_ROW_NOT_FOUND,
+ ET_UNIQUE_ROW_NOT_FOUND,
+ ET_IMPOSSIBLE_ON_CONDITION,
+
+ ET_total
+};
+
+
+typedef struct st_qpf_bka_type
+{
+ bool incremental;
+ const char *join_alg;
+ StringBuffer<64> mrr_type;
+
+} QPF_BKA_TYPE;
+
+
+/*
+ Query Plan Footprint for a JOIN_TAB.
+*/
+class QPF_table_access : public Sql_alloc
+{
+public:
+ void push_extra(enum Extra_tag extra_tag);
+
+ /* Internals */
+public:
+ /*
+ 0 means this tab is not inside SJM nest and should use QPF_select's id
+ other value means the tab is inside an SJM nest.
+ */
+ int sjm_nest_select_id;
+
+ /* id and 'select_type' are cared-of by the parent QPF_select */
+ TABLE *table;
+ StringBuffer<64> table_name;
+
+ enum join_type type;
+
+ StringBuffer<64> used_partitions;
+ bool used_partitions_set;
+
+ key_map possible_keys;
+ StringBuffer<64> possible_keys_str;
+
+ /* Not used? */
+ uint key_no;
+ uint key_length;
+
+ bool key_set; /* not set means 'NULL' should be printed */
+ StringBuffer<64> key;
+
+ bool key_len_set; /* not set means 'NULL' should be printed */
+ StringBuffer<64> key_len;
+
+ bool ref_set; /* not set means 'NULL' should be printed */
+ StringBuffer<64> ref;
+
+ bool rows_set; /* not set means 'NULL' should be printed */
+ ha_rows rows;
+
+ bool filtered_set; /* not set means 'NULL' should be printed */
+ double filtered;
+
+ /*
+ Contents of the 'Extra' column. Some are converted into strings, some have
+ parameters, values for which are stored below.
+ */
+ Dynamic_array<enum Extra_tag> extra_tags;
+
+ // Valid if ET_USING tag is present
+ StringBuffer<64> quick_info;
+
+ // Valid if ET_USING_INDEX_FOR_GROUP_BY is present
+ bool loose_scan_is_scanning;
+
+ // valid with ET_RANGE_CHECKED_FOR_EACH_RECORD
+ key_map range_checked_map;
+
+ // valid with ET_USING_MRR
+ StringBuffer<64> mrr_type;
+
+ // valid with ET_USING_JOIN_BUFFER
+ //StringBuffer<64> join_buffer_type;
+ QPF_BKA_TYPE bka_type;
+
+ //TABLE *firstmatch_table;
+ StringBuffer<64> firstmatch_table_name;
+
+ int print_explain(select_result_sink *output, uint8 explain_flags,
+ uint select_id, const char *select_type,
+ bool using_temporary, bool using_filesort);
+private:
+ void append_tag_name(String *str, enum Extra_tag tag);
+};
+
+
+/*
+ Query Plan Footprint for single-table UPDATE.
+
+ This is similar to QPF_table_access, except that it is more restrictive.
+ Also, it can have UPDATE operation options, but currently there aren't any.
+*/
+
+class QPF_update : public QPF_node
+{
+public:
+ virtual enum qpf_node_type get_type() { return QPF_UPDATE; }
+ virtual int get_select_id() { return 1; /* always root */ }
+
+ const char *select_type;
+
+ bool impossible_where;
+ StringBuffer<64> table_name;
+
+ enum join_type jtype;
+ StringBuffer<128> possible_keys_line;
+ StringBuffer<128> key_str;
+ StringBuffer<128> key_len_str;
+ StringBuffer<64> mrr_type;
+
+ bool using_where;
+ ha_rows rows;
+
+ bool using_filesort;
+
+ virtual int print_explain(QPF_query *query, select_result_sink *output,
+ uint8 explain_flags);
+};
+
+
+/*
+ Query Plan Footprint for a single-table DELETE.
+*/
+
+class QPF_delete: public QPF_update
+{
+public:
+ /*
+ TRUE means we're going to call handler->delete_all_rows() and not read any
+ rows.
+ */
+ bool deleting_all_rows;
+
+ virtual enum qpf_node_type get_type() { return QPF_DELETE; }
+ virtual int get_select_id() { return 1; /* always root */ }
+
+ virtual int print_explain(QPF_query *query, select_result_sink *output,
+ uint8 explain_flags);
+};
+
+
=== modified file 'sql/opt_range.h'
--- sql/opt_range.h 2013-06-15 17:09:40 +0000
+++ sql/opt_range.h 2013-06-27 18:36:59 +0000
@@ -944,11 +944,7 @@ class QUICK_GROUP_MIN_MAX_SELECT : publi
void dbug_dump(int indent, bool verbose);
#endif
bool is_agg_distinct() { return have_agg_distinct; }
- virtual void append_loose_scan_type(String *str)
- {
- if (is_index_scan)
- str->append(STRING_WITH_LEN(" (scanning)"));
- }
+ bool loose_scan_is_scanning() { return is_index_scan; }
};
=== modified file 'sql/sp_head.cc'
--- sql/sp_head.cc 2013-06-07 08:10:45 +0000
+++ sql/sp_head.cc 2013-06-27 18:36:59 +0000
@@ -2977,6 +2977,7 @@ sp_lex_keeper::reset_lex_and_exec_core(T
}
reinit_stmt_before_use(thd, m_lex);
+ // not here, but inside every instr: create_qpf_query(m_lex);
if (open_tables)
res= instr->exec_open_and_lock_tables(thd, m_lex->query_tables);
@@ -3010,6 +3011,8 @@ sp_lex_keeper::reset_lex_and_exec_core(T
else if (! thd->in_sub_stmt)
thd->mdl_context.release_statement_locks();
}
+
+ delete_qpf_query(m_lex);
if (m_lex->query_tables_own_last)
{
@@ -3217,6 +3220,7 @@ sp_instr_set::execute(THD *thd, uint *ne
int
sp_instr_set::exec_core(THD *thd, uint *nextp)
{
+ create_qpf_query(thd->lex, thd->mem_root);
int res= thd->spcont->set_variable(thd, m_offset, &m_value);
if (res)
@@ -3229,6 +3233,7 @@ sp_instr_set::exec_core(THD *thd, uint *
my_error(ER_OUT_OF_RESOURCES, MYF(ME_FATALERROR));
}
}
+ delete_qpf_query(thd->lex);
*nextp = m_ip+1;
return res;
=== modified file 'sql/sql_array.h'
--- sql/sql_array.h 2013-06-06 15:51:28 +0000
+++ sql/sql_array.h 2013-06-21 18:08:10 +0000
@@ -106,6 +106,7 @@ template <class Elem> class Dynamic_arra
Elem& at(size_t idx)
{
+ DBUG_ASSERT(idx < array.elements);
return *(((Elem*)array.buffer) + idx);
}
@@ -139,6 +140,23 @@ template <class Elem> class Dynamic_arra
array.elements= n;
}
+ bool resize(size_t new_size, Elem default_val)
+ {
+ size_t old_size= elements();
+ if (allocate_dynamic(&array, new_size))
+ return true;
+
+ if (new_size > old_size)
+ {
+ set_dynamic(&array, (uchar*)&default_val, new_size - 1);
+ /*for (size_t i= old_size; i != new_size; i++)
+ {
+ at(i)= default_val;
+ }*/
+ }
+ return false;
+ }
+
~Dynamic_array()
{
delete_dynamic(&array);
=== modified file 'sql/sql_class.h'
--- sql/sql_class.h 2013-06-06 15:51:28 +0000
+++ sql/sql_class.h 2013-06-21 13:12:55 +0000
@@ -3401,6 +3401,11 @@ class select_result :public select_resul
void begin_dataset() {}
#endif
virtual void update_used_tables() {}
+
+ void reset_offset_limit()
+ {
+ unit->offset_limit_cnt= 0;
+ }
};
=== modified file 'sql/sql_delete.cc'
--- sql/sql_delete.cc 2013-06-06 15:51:28 +0000
+++ sql/sql_delete.cc 2013-06-27 18:36:59 +0000
@@ -40,6 +40,132 @@
#include "records.h" // init_read_record,
#include "sql_derived.h" // mysql_handle_list_of_derived
// end_read_record
+
+
+/*
+ @brief
+ Print query plan of a single-table DELETE command
+
+ @detail
+ This function is used by EXPLAIN DELETE and by SHOW EXPLAIN when it is
+ invoked on a running DELETE statement.
+*/
+
+void Delete_plan::save_query_plan_footprint(QPF_query *query)
+{
+ QPF_delete* qpf= new QPF_delete;
+
+ if (deleting_all_rows)
+ {
+ qpf->deleting_all_rows= true;
+ qpf->select_type= "SIMPLE";
+ }
+ else
+ {
+ qpf->deleting_all_rows= false;
+ Update_plan::save_query_plan_footprint_intern(query, qpf);
+ }
+
+ query->upd_del_plan= qpf;
+}
+
+
+void Update_plan::save_query_plan_footprint(QPF_query *query)
+{
+ QPF_update* qpf= new QPF_update;
+ save_query_plan_footprint_intern(query, qpf);
+ query->upd_del_plan= qpf;
+}
+
+
+void Update_plan::save_query_plan_footprint_intern(QPF_query *query, QPF_update *qpf)
+{
+ qpf->select_type= "SIMPLE";
+ qpf->table_name.append(table->pos_in_table_list->alias);
+ if (impossible_where)
+ {
+ qpf->impossible_where= true;
+ return;
+ }
+
+ qpf->impossible_where= false;
+
+ select_lex->set_explain_type(TRUE);
+ qpf->select_type= select_lex->type;
+
+ /* Set jtype */
+ if (select && select->quick)
+ {
+ int quick_type= select->quick->get_type();
+ if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) ||
+ (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) ||
+ (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
+ (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION))
+ qpf->jtype= JT_INDEX_MERGE;
+ else
+ qpf->jtype= JT_RANGE;
+ }
+ else
+ {
+ if (index == MAX_KEY)
+ qpf->jtype= JT_ALL;
+ else
+ qpf->jtype= JT_NEXT;
+ }
+
+ qpf->using_where= test(select && select->cond);
+ qpf->using_filesort= using_filesort;
+
+ //using_filesort is already set
+ make_possible_keys_line(table, possible_keys, &qpf->possible_keys_line);
+
+ /* Calculate key_len */
+ if (select && select->quick)
+ {
+ select->quick->add_keys_and_lengths(&qpf->key_str, &qpf->key_len_str);
+ }
+ else
+ {
+ if (index != MAX_KEY)
+ {
+ qpf->key_str.append(table->key_info[index].name);
+ }
+ // key_len stays NULL
+ }
+ qpf->rows= select ? select->records : table_rows;
+
+ if (select && select->quick &&
+ select->quick->get_type() == QUICK_SELECT_I::QS_TYPE_RANGE)
+ {
+ explain_append_mrr_info((QUICK_RANGE_SELECT*)select->quick, &qpf->mrr_type);
+ }
+
+ bool skip= updating_a_view;
+ /* Save subquery children */
+ for (SELECT_LEX_UNIT *unit= select_lex->first_inner_unit();
+ unit;
+ unit= unit->next_unit())
+ {
+ if (skip)
+ {
+ skip= false;
+ continue;
+ }
+ /*
+ Display subqueries only if they are not parts of eliminated WHERE/ON
+ clauses.
+ */
+ if (!(unit->item && unit->item->eliminated))
+ qpf->add_child(unit->first_select()->select_number);
+
+ //TODO: temporary?:
+ // A: yes. optimizing children subqueries has caused them to save QPFs,
+ // automatically.
+ //unit->save_qpf(query);
+ }
+}
+
+
/**
Implement DELETE SQL word.
@@ -61,12 +187,16 @@ bool mysql_delete(THD *thd, TABLE_LIST *
bool const_cond_result;
ha_rows deleted= 0;
bool reverse= FALSE;
+ bool err= true;
ORDER *order= (ORDER *) ((order_list && order_list->elements) ?
order_list->first : NULL);
- uint usable_index= MAX_KEY;
SELECT_LEX *select_lex= &thd->lex->select_lex;
killed_state killed_status= NOT_KILLED;
THD::enum_binlog_query_type query_type= THD::ROW_QUERY_TYPE;
+
+ Delete_plan query_plan;
+ query_plan.index= MAX_KEY;
+ query_plan.using_filesort= FALSE;
DBUG_ENTER("mysql_delete");
if (open_and_lock_tables(thd, table_list, TRUE, 0))
@@ -90,6 +220,11 @@ bool mysql_delete(THD *thd, TABLE_LIST *
}
thd_proc_info(thd, "init");
table->map=1;
+ query_plan.select_lex= &thd->lex->select_lex;
+ query_plan.table= table;
+
+ //psergey-todo: Ugly, discuss with Sanja
+ query_plan.updating_a_view= test(table_list->view);
if (mysql_prepare_delete(thd, table_list, &conds))
DBUG_RETURN(TRUE);
@@ -163,6 +298,11 @@ bool mysql_delete(THD *thd, TABLE_LIST *
table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
ha_rows const maybe_deleted= table->file->stats.records;
DBUG_PRINT("debug", ("Trying to use delete_all_rows()"));
+
+ query_plan.set_delete_all_rows(maybe_deleted);
+ if (thd->lex->describe)
+ goto exit_without_my_ok;
+
if (!(error=table->file->ha_delete_all_rows()))
{
/*
@@ -187,7 +327,12 @@ bool mysql_delete(THD *thd, TABLE_LIST *
Item::cond_result result;
conds= remove_eq_conds(thd, conds, &result);
if (result == Item::COND_FALSE) // Impossible where
+ {
limit= 0;
+ query_plan.set_impossible_where();
+ if (thd->lex->describe)
+ goto exit_without_my_ok;
+ }
}
#ifdef WITH_PARTITION_STORAGE_ENGINE
@@ -195,6 +340,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
{
free_underlaid_joins(thd, select_lex);
// No matching record
+ //psergey-explain-todo: No-partitions used EXPLAIN here..
my_ok(thd, 0);
DBUG_RETURN(0);
}
@@ -211,6 +357,10 @@ bool mysql_delete(THD *thd, TABLE_LIST *
DBUG_RETURN(TRUE);
if ((select && select->check_quick(thd, safe_update, limit)) || !limit)
{
+ query_plan.set_impossible_where();
+ if (thd->lex->describe)
+ goto exit_without_my_ok;
+
delete select;
free_underlaid_joins(thd, select_lex);
/*
@@ -243,26 +393,47 @@ bool mysql_delete(THD *thd, TABLE_LIST *
if (order)
{
- uint length= 0;
- SORT_FIELD *sortorder;
- ha_rows examined_rows;
- ha_rows found_rows;
-
table->update_const_key_parts(conds);
order= simple_remove_const(order, conds);
- bool need_sort;
if (select && select->quick && select->quick->unique_key_range())
{ // Single row select (always "ordered")
- need_sort= FALSE;
- usable_index= MAX_KEY;
+ query_plan.using_filesort= FALSE;
+ query_plan.index= MAX_KEY;
}
else
- usable_index= get_index_for_order(order, table, select, limit,
- &need_sort, &reverse);
- if (need_sort)
+ query_plan.index= get_index_for_order(order, table, select, limit,
+ &query_plan.using_filesort,
+ &reverse);
+ }
+
+ query_plan.select= select;
+ query_plan.possible_keys= table->quick_keys;
+ query_plan.table_rows= table->file->stats.records;
+
+ /*
+ Ok, we have generated a query plan for the DELETE.
+ - if we're running EXPLAIN DELETE, goto produce explain output
+ - otherwise, execute the query plan
+ */
+ if (thd->lex->describe)
+ goto exit_without_my_ok;
+
+ query_plan.save_query_plan_footprint(thd->lex->query_plan_footprint);
+ thd->apc_target.enable();
+
+ DBUG_EXECUTE_IF("show_explain_probe_delete_exec_start",
+ dbug_serve_apcs(thd, 1););
+
+ if (query_plan.using_filesort)
+ {
+ ha_rows examined_rows;
+ ha_rows found_rows;
+ uint length= 0;
+ SORT_FIELD *sortorder;
+
{
- DBUG_ASSERT(usable_index == MAX_KEY);
+ DBUG_ASSERT(query_plan.index == MAX_KEY);
table->sort.io_cache= (IO_CACHE *) my_malloc(sizeof(IO_CACHE),
MYF(MY_FAE | MY_ZEROFILL |
MY_THREAD_SPECIFIC));
@@ -276,6 +447,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
{
delete select;
free_underlaid_joins(thd, &thd->lex->select_lex);
+ thd->apc_target.disable();
DBUG_RETURN(TRUE);
}
thd->examined_row_count+= examined_rows;
@@ -294,19 +466,21 @@ bool mysql_delete(THD *thd, TABLE_LIST *
{
delete select;
free_underlaid_joins(thd, select_lex);
+ thd->apc_target.disable();
DBUG_RETURN(TRUE);
}
- if (usable_index == MAX_KEY || (select && select->quick))
+ if (query_plan.index == MAX_KEY || (select && select->quick))
{
if (init_read_record(&info, thd, table, select, 1, 1, FALSE))
{
delete select;
free_underlaid_joins(thd, select_lex);
+ thd->apc_target.disable();
DBUG_RETURN(TRUE);
}
}
else
- init_read_record_idx(&info, thd, table, 1, usable_index, reverse);
+ init_read_record_idx(&info, thd, table, 1, query_plan.index, reverse);
init_ftfuncs(thd, select_lex, 1);
thd_proc_info(thd, "updating");
@@ -398,6 +572,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
if (options & OPTION_QUICK)
(void) table->file->extra(HA_EXTRA_NORMAL);
+ thd->apc_target.disable();
cleanup:
/*
Invalidate the table in the query cache if something changed. This must
@@ -458,6 +633,28 @@ bool mysql_delete(THD *thd, TABLE_LIST *
DBUG_PRINT("info",("%ld records deleted",(long) deleted));
}
DBUG_RETURN(error >= 0 || thd->is_error());
+
+ /* Special exits */
+exit_without_my_ok:
+ query_plan.save_query_plan_footprint(thd->lex->query_plan_footprint);
+
+ select_send *result;
+ if (!(result= new select_send()))
+ return 1; /* purecov: inspected */
+ List<Item> dummy; /* note: looked in 5.6 and they too use a dummy list like this */
+ result->prepare(dummy, &thd->lex->unit);
+ thd->send_explain_fields(result);
+ int err2= thd->lex->query_plan_footprint->print_explain(result, 0 /* explain flags*/);
+
+ if (err2)
+ result->abort_result_set();
+ else
+ result->send_eof();
+
+ delete select;
+ free_underlaid_joins(thd, select_lex);
+ //table->set_keyread(false);
+ DBUG_RETURN((err || thd->is_error() || thd->killed) ? 1 : 0);
}
=== modified file 'sql/sql_join_cache.cc'
--- sql/sql_join_cache.cc 2013-05-05 18:39:31 +0000
+++ sql/sql_join_cache.cc 2013-06-27 18:36:59 +0000
@@ -2568,34 +2568,26 @@ enum_nested_loop_state JOIN_CACHE::join_
none
*/
-void JOIN_CACHE::print_explain_comment(String *str)
+void JOIN_CACHE::save_qpf(struct st_qpf_bka_type *qpf)
{
- str->append(STRING_WITH_LEN(" ("));
- const char *buffer_type= prev_cache ? "incremental" : "flat";
- str->append(buffer_type);
- str->append(STRING_WITH_LEN(", "));
-
- const char *join_alg="";
+ qpf->incremental= test(prev_cache);
+
switch (get_join_alg()) {
case BNL_JOIN_ALG:
- join_alg= "BNL";
+ qpf->join_alg= "BNL";
break;
case BNLH_JOIN_ALG:
- join_alg= "BNLH";
+ qpf->join_alg= "BNLH";
break;
case BKA_JOIN_ALG:
- join_alg= "BKA";
+ qpf->join_alg= "BKA";
break;
case BKAH_JOIN_ALG:
- join_alg= "BKAH";
+ qpf->join_alg= "BKAH";
break;
default:
DBUG_ASSERT(0);
}
-
- str->append(join_alg);
- str->append(STRING_WITH_LEN(" join"));
- str->append(STRING_WITH_LEN(")"));
}
/**
@@ -2621,18 +2613,17 @@ static void add_mrr_explain_info(String
}
}
-
-void JOIN_CACHE_BKA::print_explain_comment(String *str)
+void JOIN_CACHE_BKA::save_qpf(struct st_qpf_bka_type *qpf)
{
- JOIN_CACHE::print_explain_comment(str);
- add_mrr_explain_info(str, mrr_mode, join_tab->table->file);
+ JOIN_CACHE::save_qpf(qpf);
+ add_mrr_explain_info(&qpf->mrr_type, mrr_mode, join_tab->table->file);
}
-void JOIN_CACHE_BKAH::print_explain_comment(String *str)
+void JOIN_CACHE_BKAH::save_qpf(struct st_qpf_bka_type *qpf)
{
- JOIN_CACHE::print_explain_comment(str);
- add_mrr_explain_info(str, mrr_mode, join_tab->table->file);
+ JOIN_CACHE::save_qpf(qpf);
+ add_mrr_explain_info(&qpf->mrr_type, mrr_mode, join_tab->table->file);
}
=== modified file 'sql/sql_join_cache.h'
--- sql/sql_join_cache.h 2012-02-21 19:51:56 +0000
+++ sql/sql_join_cache.h 2013-06-27 18:36:59 +0000
@@ -63,6 +63,7 @@ typedef struct st_cache_field {
class JOIN_TAB_SCAN;
+struct st_qpf_bka_type;
/*
JOIN_CACHE is the base class to support the implementations of
@@ -657,7 +658,7 @@ class JOIN_CACHE :public Sql_alloc
enum_nested_loop_state join_records(bool skip_last);
/* Add a comment on the join algorithm employed by the join cache */
- virtual void print_explain_comment(String *str);
+ virtual void save_qpf(struct st_qpf_bka_type *qpf);
THD *thd();
@@ -1335,7 +1336,7 @@ class JOIN_CACHE_BKA :public JOIN_CACHE
/* Check index condition of the joined table for a record from BKA cache */
bool skip_index_tuple(range_id_t range_info);
- void print_explain_comment(String *str);
+ void save_qpf(struct st_qpf_bka_type *qpf);
};
@@ -1426,5 +1427,5 @@ class JOIN_CACHE_BKAH :public JOIN_CACHE
/* Check index condition of the joined table for a record from BKAH cache */
bool skip_index_tuple(range_id_t range_info);
- void print_explain_comment(String *str);
+ void save_qpf(struct st_qpf_bka_type *qpf);
};
=== modified file 'sql/sql_lex.cc'
--- sql/sql_lex.cc 2013-06-06 15:51:28 +0000
+++ sql/sql_lex.cc 2013-06-27 18:36:59 +0000
@@ -447,6 +447,8 @@ void lex_start(THD *thd)
DBUG_ENTER("lex_start");
lex->thd= lex->unit.thd= thd;
+
+ lex->query_plan_footprint= NULL;
lex->context_stack.empty();
lex->unit.init_query();
@@ -2551,7 +2553,8 @@ void Query_tables_list::destroy_query_ta
*/
LEX::LEX()
- :result(0), option_type(OPT_DEFAULT), is_lex_started(0),
+ : query_plan_footprint(NULL),
+ result(0), option_type(OPT_DEFAULT), is_lex_started(0),
limit_rows_examined_cnt(ULONGLONG_MAX)
{
@@ -3493,6 +3496,18 @@ bool st_select_lex::optimize_unflattened
is_correlated_unit|= sl->is_correlated;
inner_join->select_options= save_options;
un->thd->lex->current_select= save_select;
+ /// psergey:
+ QPF_query *qpf;
+ if ((qpf= inner_join->thd->lex->query_plan_footprint))
+ {
+ QPF_select *qp_sel;
+ if ((qp_sel= qpf->get_select(inner_join->select_lex->select_number)))
+ {
+ sl->set_explain_type(TRUE);
+ qp_sel->select_type= sl->type;
+ }
+ }
+ ///
if (empty_union_result)
{
/*
@@ -4171,89 +4186,55 @@ bool st_select_lex::is_merged_child_of(s
return all_merged;
}
+/*
+ This is used by SHOW EXPLAIN. It assuses query plan has been already
+ collected into QPF structures and we only need to print it out.
+*/
-int print_explain_message_line(select_result_sink *result,
- SELECT_LEX *select_lex,
- bool on_the_fly,
- uint8 options,
- const char *message);
-
-
-int st_select_lex::print_explain(select_result_sink *output,
- uint8 explain_flags,
- bool *printed_anything)
+int LEX::print_explain(select_result_sink *output, uint8 explain_flags,
+ bool *printed_anything)
{
int res;
- if (join && join->have_query_plan == JOIN::QEP_AVAILABLE)
+ if (query_plan_footprint && query_plan_footprint->have_query_plan())
{
- /*
- There is a number of reasons join can be marked as degenerate, so all
- three conditions below can happen simultaneously, or individually:
- */
- *printed_anything= TRUE;
- if (!join->table_count || !join->tables_list || join->zero_result_cause)
- {
- /* It's a degenerate join */
- const char *cause= join->zero_result_cause ? join-> zero_result_cause :
- "No tables used";
- res= join->print_explain(output, explain_flags, TRUE, FALSE, FALSE,
- FALSE, cause);
- }
- else
- {
- res= join->print_explain(output, explain_flags, TRUE,
- join->need_tmp, // need_tmp_table
- !join->skip_sort_order && !join->no_order &&
- (join->order || join->group_list), // bool need_order
- join->select_distinct, // bool distinct
- NULL); //const char *message
- }
- if (res)
- goto err;
-
- for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit();
- unit;
- unit= unit->next_unit())
- {
- /*
- Display subqueries only if they are not parts of eliminated WHERE/ON
- clauses.
- */
- if (!(unit->item && unit->item->eliminated))
- {
- if ((res= unit->print_explain(output, explain_flags, printed_anything)))
- goto err;
- }
- }
+ res= query_plan_footprint->print_explain(output, explain_flags);
+ *printed_anything= true;
}
else
{
- const char *msg;
- if (!join)
- DBUG_ASSERT(0); /* Seems not to be possible */
-
- /* Not printing anything useful, don't touch *printed_anything here */
- if (join->have_query_plan == JOIN::QEP_NOT_PRESENT_YET)
- msg= "Not yet optimized";
- else
- {
- DBUG_ASSERT(join->have_query_plan == JOIN::QEP_DELETED);
- msg= "Query plan already deleted";
- }
- res= print_explain_message_line(output, this, TRUE /* on_the_fly */,
- 0, msg);
+ res= 0;
+ *printed_anything= false;
}
-err:
return res;
}
-int st_select_lex_unit::print_explain(select_result_sink *output,
- uint8 explain_flags, bool *printed_anything)
+/*
+ Save query plan of a UNION. The only variable member is whether the union has
+ "Using filesort".
+
+ There is also save_union_qpf_part2() function, which is called before we read
+ UNION's output.
+
+ The reason for it is examples like this:
+
+ SELECT col1 FROM t1 UNION SELECT col2 FROM t2 ORDER BY (select ... from t3 ...)
+
+ Here, the (select ... from t3 ...) subquery must be a child of UNION's
+ st_select_lex. However, it is not connected as child until a very late
+ stage in execution.
+*/
+
+int st_select_lex_unit::save_union_qpf(QPF_query *output)
{
- int res= 0;
SELECT_LEX *first= first_select();
-
+ QPF_union *qpfu= new (output->mem_root) QPF_union;
+
+ /*
+ TODO: The following code should be eliminated. If we have a capability to
+ save Query Plan Footprints, we should just save them, and never need to
+ print "query plan already deleted".
+ */
if (first && !first->next_select() && !first->join)
{
/*
@@ -4261,24 +4242,44 @@ int st_select_lex_unit::print_explain(se
EXPLAIN state" error.
*/
const char *msg="Query plan already deleted";
- res= print_explain_message_line(output, first, TRUE /* on_the_fly */,
- 0, msg);
+ first->set_explain_type(TRUE/* on_the_fly */);
+
+ QPF_select *qp_sel= new (output->mem_root)QPF_select;
+ qp_sel->select_id= first->select_number;
+ qp_sel->select_type= first->type;
+ qp_sel->message= msg;
+ output->add_node(qp_sel);
+ qpfu->add_select(qp_sel->select_id);
return 0;
}
for (SELECT_LEX *sl= first; sl; sl= sl->next_select())
- {
- if ((res= sl->print_explain(output, explain_flags, printed_anything)))
- break;
- }
+ qpfu->add_select(sl->select_number);
+
+ // Save the UNION node
+ output->add_node(qpfu);
- /* Note: fake_select_lex->join may be NULL or non-NULL at this point */
+ qpfu->fake_select_type= "UNION RESULT";
+ qpfu->using_filesort= test(global_parameters->order_list.first);
+ return 0;
+}
+
+
+int st_select_lex_unit::save_union_qpf_part2(QPF_query *output)
+{
+ QPF_union *qpfu= output->get_union(first_select()->select_number);
if (fake_select_lex)
{
- res= print_fake_select_lex_join(output, TRUE /* on the fly */,
- fake_select_lex, explain_flags);
+ for (SELECT_LEX_UNIT *unit= fake_select_lex->first_inner_unit();
+ unit; unit= unit->next_unit())
+ {
+ if (!(unit->item && unit->item->eliminated))
+ {
+ qpfu->add_child(unit->first_select()->select_number);
+ }
+ }
}
- return res;
+ return 0;
}
=== modified file 'sql/sql_lex.h'
--- sql/sql_lex.h 2013-06-24 18:56:49 +0000
+++ sql/sql_lex.h 2013-06-27 18:36:59 +0000
@@ -617,7 +617,10 @@ class select_result;
class JOIN;
class select_union;
class Procedure;
+class QPF_query;
+void delete_qpf_query(LEX *lex);
+void create_qpf_query(LEX *lex, MEM_ROOT *mem_root);
class st_select_lex_unit: public st_select_lex_node {
protected:
@@ -728,8 +731,9 @@ class st_select_lex_unit: public st_sele
friend int subselect_union_engine::exec();
List<Item> *get_unit_column_types();
- int print_explain(select_result_sink *output, uint8 explain_flags,
- bool *printed_anything);
+
+ int save_union_qpf(QPF_query *output);
+ int save_union_qpf_part2(QPF_query *output);
};
typedef class st_select_lex_unit SELECT_LEX_UNIT;
@@ -1051,8 +1055,7 @@ class st_select_lex: public st_select_le
bool save_prep_leaf_tables(THD *thd);
bool is_merged_child_of(st_select_lex *ancestor);
- int print_explain(select_result_sink *output, uint8 explain_flags,
- bool *printed_anything);
+
/*
For MODE_ONLY_FULL_GROUP_BY we need to maintain two flags:
- Non-aggregated fields are used in this select.
@@ -2357,6 +2360,69 @@ class Sql_statement : public Sql_alloc
LEX *m_lex;
};
+
+class Delete_plan;
+class SQL_SELECT;
+
+class QPF_query;
+class QPF_update;
+
+/*
+ Query plan of a single-table UPDATE.
+ (This is actually a plan for single-table DELETE also)
+*/
+
+class Update_plan
+{
+protected:
+ bool impossible_where;
+public:
+ bool updating_a_view;
+ TABLE *table;
+ SQL_SELECT *select;
+ uint index;
+ ha_rows table_rows; /* Use if select==NULL */
+ /*
+ Top-level select_lex. Most of its fields are not used, we need it only to
+ get to the subqueries.
+ */
+ SELECT_LEX *select_lex;
+
+ key_map possible_keys;
+ bool using_filesort;
+
+ /* Set this plan to be a plan to do nothing because of impossible WHRE*/
+ void set_impossible_where() { impossible_where= true; }
+
+ void save_query_plan_footprint(QPF_query *query);
+ void save_query_plan_footprint_intern(QPF_query *query, QPF_update *qpf);
+ virtual ~Update_plan() {}
+
+ Update_plan() : impossible_where(false), using_filesort(false) {}
+};
+
+
+/* Query plan of a single-table DELETE */
+class Delete_plan : public Update_plan
+{
+ bool deleting_all_rows;
+public:
+
+ /* Construction functions */
+ Delete_plan() :
+ deleting_all_rows(false) {}
+
+ /* Set this query plan to be a plan to make a call to h->delete_all_rows() */
+ void set_delete_all_rows(ha_rows rows_arg)
+ {
+ deleting_all_rows= true;
+ table_rows= rows_arg;
+ }
+
+ void save_query_plan_footprint(QPF_query *query);
+};
+
+
/* The state of the lex parsing. This is saved in the THD struct */
struct LEX: public Query_tables_list
@@ -2367,6 +2433,9 @@ struct LEX: public Query_tables_list
SELECT_LEX *current_select;
/* list of all SELECT_LEX */
SELECT_LEX *all_selects_list;
+
+ /* Query Plan Footprint of a currently running select */
+ QPF_query *query_plan_footprint;
char *length,*dec,*change;
LEX_STRING name;
@@ -2783,6 +2852,9 @@ struct LEX: public Query_tables_list
}
return FALSE;
}
+
+ int print_explain(select_result_sink *output, uint8 explain_flags,
+ bool *printed_anything);
};
=== modified file 'sql/sql_parse.cc'
--- sql/sql_parse.cc 2013-06-24 18:56:49 +0000
+++ sql/sql_parse.cc 2013-06-27 18:36:59 +0000
@@ -598,6 +598,7 @@ static void handle_bootstrap_impl(THD *t
#if defined(ENABLED_PROFILING)
thd->profiling.finish_current_query();
#endif
+ delete_qpf_query(thd->lex);
if (bootstrap_error)
break;
@@ -807,7 +808,9 @@ bool do_command(THD *thd)
my_net_set_read_timeout(net, thd->variables.net_read_timeout);
DBUG_ASSERT(packet_length);
+ DBUG_ASSERT(!thd->apc_target.is_enabled());
return_value= dispatch_command(command, thd, packet+1, (uint) (packet_length-1));
+ DBUG_ASSERT(!thd->apc_target.is_enabled());
out:
DBUG_RETURN(return_value);
@@ -1109,6 +1112,7 @@ bool dispatch_command(enum enum_server_c
ulong length= (ulong)(packet_end - beginning_of_next_stmt);
log_slow_statement(thd);
+ DBUG_ASSERT(!thd->apc_target.is_enabled());
/* Remove garbage at start of query */
while (length > 0 && my_isspace(thd->charset(), *beginning_of_next_stmt))
@@ -1481,6 +1485,7 @@ bool dispatch_command(enum enum_server_c
thd->update_all_stats();
log_slow_statement(thd);
+ /* psergey-todo: this is the place we could print EXPLAIN to slow query log */
thd_proc_info(thd, "cleaning up");
thd->reset_query();
@@ -1515,6 +1520,8 @@ void log_slow_statement(THD *thd)
{
DBUG_ENTER("log_slow_statement");
+ delete_qpf_query(thd->lex);
+
/*
The following should never be true with our current code base,
but better to keep this here so we don't accidently try to log a
@@ -1523,6 +1530,7 @@ void log_slow_statement(THD *thd)
if (unlikely(thd->in_sub_stmt))
DBUG_VOID_RETURN; // Don't set time for sub stmt
+
/* Follow the slow log filter configuration. */
if (!thd->enable_slow_log ||
(thd->variables.log_slow_filter
@@ -2176,6 +2184,8 @@ mysql_execute_command(THD *thd)
/* Release metadata locks acquired in this transaction. */
thd->mdl_context.release_transactional_locks();
}
+
+ create_qpf_query(thd->lex, thd->mem_root);
#ifndef DBUG_OFF
if (lex->sql_command != SQLCOM_SET_OPTION)
@@ -3251,7 +3261,8 @@ mysql_execute_command(THD *thd)
{
DBUG_ASSERT(first_table == all_tables && first_table != 0);
TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first;
- multi_delete *del_result;
+ bool explain= test(lex->describe);
+ select_result *result;
if ((res= multi_delete_precheck(thd, all_tables)))
break;
@@ -3266,37 +3277,80 @@ mysql_execute_command(THD *thd)
if ((res= open_and_lock_tables(thd, all_tables, TRUE, 0)))
break;
- MYSQL_MULTI_DELETE_START(thd->query());
+ if (!explain)
+ {
+ MYSQL_MULTI_DELETE_START(thd->query());
+ }
+
if ((res= mysql_multi_delete_prepare(thd)))
{
- MYSQL_MULTI_DELETE_DONE(1, 0);
+ if (!explain)
+ {
+ MYSQL_MULTI_DELETE_DONE(1, 0);
+ }
goto error;
}
- if (!thd->is_fatal_error &&
- (del_result= new multi_delete(aux_tables, lex->table_count)))
+ if (!thd->is_fatal_error)
{
- res= mysql_select(thd, &select_lex->ref_pointer_array,
- select_lex->get_table_list(),
- select_lex->with_wild,
- select_lex->item_list,
- select_lex->where,
- 0, (ORDER *)NULL, (ORDER *)NULL, (Item *)NULL,
- (ORDER *)NULL,
- (select_lex->options | thd->variables.option_bits |
- SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
- OPTION_SETUP_TABLES_DONE) & ~OPTION_BUFFER_RESULT,
- del_result, unit, select_lex);
- res|= thd->is_error();
- MYSQL_MULTI_DELETE_DONE(res, del_result->num_deleted());
- if (res)
- del_result->abort_result_set();
- delete del_result;
+ if (explain)
+ {
+ result= new select_send();
+ if (thd->send_explain_fields(result))
+ {
+ delete result;
+ result= NULL;
+ }
+ select_lex->set_explain_type(FALSE);
+ //thd->lex->query_plan_footprint= new QPF_query;
+ }
+ else
+ result= new multi_delete(aux_tables, lex->table_count);
+
+ if (result)
+ {
+ res= mysql_select(thd, &select_lex->ref_pointer_array,
+ select_lex->get_table_list(),
+ select_lex->with_wild,
+ select_lex->item_list,
+ select_lex->where,
+ 0, (ORDER *)NULL, (ORDER *)NULL, (Item *)NULL,
+ (ORDER *)NULL,
+ (select_lex->options | thd->variables.option_bits |
+ SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
+ OPTION_SETUP_TABLES_DONE) & ~OPTION_BUFFER_RESULT,
+ result, unit, select_lex);
+ res|= thd->is_error();
+
+ if (!explain)
+ {
+ MYSQL_MULTI_DELETE_DONE(res, del_result->num_deleted());
+ }
+ else
+ {
+ result->reset_offset_limit();
+ thd->lex->query_plan_footprint->print_explain(result, thd->lex->describe);
+ //delete thd->lex->query_plan_footprint;
+ //thd->lex->query_plan_footprint= NULL;
+ }
+
+ if (res)
+ result->abort_result_set(); /* for both DELETE and EXPLAIN DELETE */
+ else
+ {
+ if (explain)
+ result->send_eof();
+ }
+ delete result;
+ }
}
else
{
res= TRUE; // Error
- MYSQL_MULTI_DELETE_DONE(1, 0);
+ if (!explain)
+ {
+ MYSQL_MULTI_DELETE_DONE(1, 0);
+ }
}
break;
}
@@ -4729,8 +4783,14 @@ mysql_execute_command(THD *thd)
ha_maria::implicit_commit(thd, FALSE);
#endif
}
-
lex->unit.cleanup();
+ //psergey-todo: print EXPLAIN here? After the above JOIN::cleanup calls?
+ // how do we print EXPLAIN extended, then?
+ if (lex->describe)
+ {
+ DBUG_ASSERT(lex->query_plan_footprint);
+ ///..
+ }
/* Free tables */
thd_proc_info(thd, "closing tables");
close_thread_tables(thd);
@@ -4805,7 +4865,25 @@ static bool execute_sqlcom_select(THD *t
if (!(result= new select_send()))
return 1; /* purecov: inspected */
thd->send_explain_fields(result);
+ //thd->lex->query_plan_footprint= new QPF_query;
res= mysql_explain_union(thd, &thd->lex->unit, result);
+
+ if (!res)
+ {
+ /*
+ Do like the original select_describe did: remove OFFSET from the
+ top-level LIMIT
+ */
+ result->reset_offset_limit();
+ thd->lex->query_plan_footprint->print_explain(result, thd->lex->describe);
+ }
+ //delete thd->lex->query_plan_footprint;
+ //thd->lex->query_plan_footprint= NULL;
+
+ //psergey-todo: here, produce the EXPLAIN output.
+ // mysql_explain_union() itself is only responsible for calling
+ // optimize() for all parts of the query.
+
/*
The code which prints the extended description is not robust
against malformed queries, so skip it if we have an error.
=== modified file 'sql/sql_prepare.cc'
--- sql/sql_prepare.cc 2013-06-24 18:56:49 +0000
+++ sql/sql_prepare.cc 2013-06-27 18:36:59 +0000
@@ -2488,6 +2488,7 @@ void reinit_stmt_before_use(THD *thd, LE
object and because of this can be used in different threads.
*/
lex->thd= thd;
+ DBUG_ASSERT(!lex->query_plan_footprint);
if (lex->empty_field_list_on_rset)
{
@@ -3927,6 +3928,8 @@ bool Prepared_statement::execute(String
if (! cursor)
cleanup_stmt();
+ //psergey: TODO the "EXECUTE problem" is here
+ delete_qpf_query(thd->lex);
thd->set_statement(&stmt_backup);
thd->stmt_arena= old_stmt_arena;
=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc 2013-06-06 15:51:28 +0000
+++ sql/sql_select.cc 2013-06-27 18:36:59 +0000
@@ -1002,20 +1002,33 @@ bool JOIN::prepare_stage2()
DBUG_RETURN(res); /* purecov: inspected */
}
+void join_save_qpf(JOIN *join);
int JOIN::optimize()
{
+ bool was_optimized= optimized;
int res= optimize_inner();
/*
If we're inside a non-correlated subquery, this function may be
called for the second time after the subquery has been executed
and deleted. The second call will not produce a valid query plan, it will
short-circuit because optimized==TRUE.
+
+ "was_optimized != optimized" is here to handle this case:
+ - first optimization starts, gets an error (from a const. cheap
+ subquery), returns 1
+ - another JOIN::optimize() call made, and now join->optimize() will
+ return 0, even though we never had a query plan.
*/
- if (!res && have_query_plan != QEP_DELETED)
+ if (was_optimized != optimized && !res && have_query_plan != QEP_DELETED)
+ {
have_query_plan= QEP_AVAILABLE;
+ join_save_qpf(this);
+ }
return res;
}
+
+
/**
global select optimisation.
@@ -1602,8 +1615,10 @@ TODO: make view to decide if it is possi
JOIN_TAB *tab= &join_tab[const_tables];
bool all_order_fields_used;
if (order)
+ {
skip_sort_order= test_if_skip_sort_order(tab, order, select_limit, 1,
&tab->table->keys_in_use_for_order_by);
+ }
if ((group_list=create_distinct_group(thd, select_lex->ref_pointer_array,
order, fields_list, all_fields,
&all_order_fields_used)))
@@ -2280,11 +2295,48 @@ JOIN::save_join_tab()
}
+void join_save_qpf(JOIN *join)
+{
+ THD *thd= join->thd;
+//TODO: why not call st_select_lex::save_qpf here?
+
+ if (join->select_lex->select_number != UINT_MAX &&
+ join->select_lex->select_number != INT_MAX /* this is not a UNION's "fake select */ &&
+ join->have_query_plan != JOIN::QEP_NOT_PRESENT_YET &&
+ join->have_query_plan != JOIN::QEP_DELETED && // this happens when there was no QEP ever, but then
+ //cleanup() is called multiple times
+
+ thd->lex->query_plan_footprint && // for "SET" command in SPs.
+ !thd->lex->query_plan_footprint->get_select(join->select_lex->select_number))
+ {
+ const char *message= NULL;
+
+ if (!join->table_count || !join->tables_list || join->zero_result_cause)
+ {
+ /* It's a degenerate join */
+ message= join->zero_result_cause ? join->zero_result_cause : "No tables used";
+ }
+
+ join->save_qpf(thd->lex->query_plan_footprint,
+ join->need_tmp, // need_tmp_table
+ !join->skip_sort_order && !join->no_order &&
+ (join->order || join->group_list), // bool need_order
+ join->select_distinct, // bool distinct
+ message); // message
+ }
+}
+
+
void JOIN::exec()
{
/*
Enable SHOW EXPLAIN only if we're in the top-level query.
*/
+
+ /*
+ psergey: we can produce SHOW explain at this point. This means, we're ready
+ to save the query plan.
+ */
thd->apc_target.enable();
DBUG_EXECUTE_IF("show_explain_probe_join_exec_start",
if (dbug_user_var_equals_int(thd,
@@ -2295,13 +2347,42 @@ void JOIN::exec()
exec_inner();
+ if (!exec_qpf_saved)
+ {
+ if (select_lex->select_number != UINT_MAX &&
+ select_lex->select_number != INT_MAX /* this is not a UNION's "fake select */ &&
+ have_query_plan != QEP_NOT_PRESENT_YET &&
+ have_query_plan != QEP_DELETED && // this happens when there was no QEP ever, but then
+ //cleanup() is called multiple times
+
+ thd->lex->query_plan_footprint //&& // for "SET" command in SPs.
+ /*!thd->lex->query_plan_footprint->get_select(select_lex->select_number)*/)
+ {
+ const char *message= NULL;
+
+ if (!table_count || !tables_list || zero_result_cause)
+ {
+ /* It's a degenerate join */
+ message= zero_result_cause ? zero_result_cause : "No tables used";
+ }
+
+ save_qpf(thd->lex->query_plan_footprint,
+ need_tmp, // need_tmp_table
+ // !skip_sort_order && !no_order &&
+ // (order || group_list), // bool need_order
+ order != 0 && !skip_sort_order,
+ select_distinct, // bool distinct
+ message); // message
+ }
+ exec_qpf_saved= true;
+ }
+
DBUG_EXECUTE_IF("show_explain_probe_join_exec_end",
if (dbug_user_var_equals_int(thd,
"show_explain_probe_select_id",
select_lex->select_number))
dbug_serve_apcs(thd, 1);
);
-
thd->apc_target.disable();
}
@@ -3855,7 +3936,7 @@ make_join_statistics(JOIN *join, List<TA
if (*s->on_expr_ref)
{
/* Generate empty row */
- s->info= "Impossible ON condition";
+ s->info= ET_IMPOSSIBLE_ON_CONDITION;
found_const_table_map|= s->table->map;
s->type= JT_CONST;
mark_as_null_row(s->table); // All fields are NULL
@@ -11097,7 +11178,37 @@ void JOIN::cleanup(bool full)
DBUG_ENTER("JOIN::cleanup");
DBUG_PRINT("enter", ("full %u", (uint) full));
- have_query_plan= QEP_DELETED;
+ if (full)
+ {
+ /* Save it again */
+#if 0
+ if (select_lex->select_number != UINT_MAX &&
+ select_lex->select_number != INT_MAX /* this is not a UNION's "fake select */ &&
+ have_query_plan != QEP_NOT_PRESENT_YET &&
+ have_query_plan != QEP_DELETED && // this happens when there was no QEP ever, but then
+ //cleanup() is called multiple times
+
+ thd->lex->query_plan_footprint //&& // for "SET" command in SPs.
+ /*!thd->lex->query_plan_footprint->get_select(select_lex->select_number)*/)
+ {
+ const char *message= NULL;
+
+ if (!table_count || !tables_list || zero_result_cause)
+ {
+ /* It's a degenerate join */
+ message= zero_result_cause ? zero_result_cause : "No tables used";
+ }
+
+ save_qpf(thd->lex->query_plan_footprint,
+ need_tmp, // need_tmp_table
+ !skip_sort_order && !no_order &&
+ (order || group_list), // bool need_order
+ select_distinct, // bool distinct
+ message); // message
+ }
+#endif
+ have_query_plan= QEP_DELETED; //psergey: this is a problem!
+ }
if (table)
{
@@ -17243,7 +17354,7 @@ join_read_const_table(JOIN_TAB *tab, POS
{
if ((error=join_read_system(tab)))
{ // Info for DESCRIBE
- tab->info="const row not found";
+ tab->info= ET_CONST_ROW_NOT_FOUND;
/* Mark for EXPLAIN that the row was not found */
pos->records_read=0.0;
pos->ref_depend_map= 0;
@@ -17269,7 +17380,7 @@ join_read_const_table(JOIN_TAB *tab, POS
table->disable_keyread();
if (error)
{
- tab->info="unique row not found";
+ tab->info= ET_UNIQUE_ROW_NOT_FOUND;
/* Mark for EXPLAIN that the row was not found */
pos->records_read=0.0;
pos->ref_depend_map= 0;
@@ -22114,23 +22225,20 @@ void JOIN::clear()
/*
Print an EXPLAIN line with all NULLs and given message in the 'Extra' column
*/
+
int print_explain_message_line(select_result_sink *result,
- SELECT_LEX *select_lex,
- bool on_the_fly,
uint8 options,
+ uint select_number,
+ const char *select_type,
const char *message)
{
const CHARSET_INFO *cs= system_charset_info;
Item *item_null= new Item_null();
List<Item> item_list;
- if (on_the_fly)
- select_lex->set_explain_type(on_the_fly);
-
- item_list.push_back(new Item_int((int32)
- select_lex->select_number));
- item_list.push_back(new Item_string(select_lex->type,
- strlen(select_lex->type), cs));
+ item_list.push_back(new Item_int((int32) select_number));
+ item_list.push_back(new Item_string(select_type,
+ strlen(select_type), cs));
for (uint i=0 ; i < 7; i++)
item_list.push_back(item_null);
if (options & DESCRIBE_PARTITIONS)
@@ -22146,6 +22254,104 @@ int print_explain_message_line(select_re
}
+/*
+ Make a comma-separated list of possible_keys names and add it into the string
+*/
+
+void make_possible_keys_line(TABLE *table, key_map possible_keys, String *line)
+{
+ if (!possible_keys.is_clear_all())
+ {
+ uint j;
+ for (j=0 ; j < table->s->keys ; j++)
+ {
+ if (possible_keys.is_set(j))
+ {
+ if (line->length())
+ line->append(',');
+ line->append(table->key_info[j].name,
+ strlen(table->key_info[j].name),
+ system_charset_info);
+ }
+ }
+ }
+}
+
+/*
+ Print an EXPLAIN output row, based on information provided in the parameters
+
+ @note
+ Parameters that may have NULL value in EXPLAIN output, should be passed
+ (char*)NULL.
+
+ @return
+ 0 - OK
+ 1 - OOM Error
+*/
+
+int print_explain_row(select_result_sink *result,
+ uint8 options,
+ uint select_number,
+ const char *select_type,
+ const char *table_name,
+ //const char *partitions, (todo)
+ enum join_type jtype,
+ const char *possible_keys,
+ const char *index,
+ const char *key_len,
+ const char *ref,
+ ha_rows rows,
+ const char *extra)
+{
+ const CHARSET_INFO *cs= system_charset_info;
+ Item *item_null= new Item_null();
+ List<Item> item_list;
+ Item *item;
+
+ item_list.push_back(new Item_int((int32) select_number));
+ item_list.push_back(new Item_string(select_type,
+ strlen(select_type), cs));
+ item_list.push_back(new Item_string(table_name,
+ strlen(table_name), cs));
+ if (options & DESCRIBE_PARTITIONS)
+ item_list.push_back(item_null); // psergey-todo: produce proper value
+
+ const char *jtype_str= join_type_str[jtype];
+ item_list.push_back(new Item_string(jtype_str,
+ strlen(jtype_str), cs));
+
+ item= possible_keys? new Item_string(possible_keys, strlen(possible_keys),
+ cs) : item_null;
+ item_list.push_back(item);
+
+ /* 'index */
+ item= index ? new Item_string(index, strlen(index), cs) : item_null;
+ item_list.push_back(item);
+
+ /* 'key_len */
+ item= key_len ? new Item_string(key_len, strlen(key_len), cs) : item_null;
+ item_list.push_back(item);
+
+ /* 'ref' */
+ item= ref ? new Item_string(ref, strlen(ref), cs) : item_null;
+ item_list.push_back(item);
+
+ /* 'rows' */
+ item_list.push_back(new Item_int(rows,
+ MY_INT64_NUM_DECIMAL_DIGITS));
+ /* 'filtered' */
+ if (options & DESCRIBE_EXTENDED)
+ item_list.push_back(item_null);
+
+ /* 'Extra' */
+ item_list.push_back(new Item_string(extra, strlen(extra), cs));
+
+ if (result->send_data(item_list))
+ return 1;
+ return 0;
+}
+
+
int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly,
SELECT_LEX *select_lex, uint8 explain_flags)
{
@@ -22225,69 +22431,122 @@ int print_fake_select_lex_join(select_re
}
-/**
- EXPLAIN handling.
+/*
+ Append MRR information from quick select to the given string
+*/
+
+void explain_append_mrr_info(QUICK_RANGE_SELECT *quick, String *res)
+{
+ char mrr_str_buf[128];
+ mrr_str_buf[0]=0;
+ int len;
+ handler *h= quick->head->file;
+ len= h->multi_range_read_explain_info(quick->mrr_flags, mrr_str_buf,
+ sizeof(mrr_str_buf));
+ if (len > 0)
+ {
+ //res->append(STRING_WITH_LEN("; "));
+ res->append(mrr_str_buf, len);
+ }
+}
+
+
+/////////////////////////////////////////////////////////////////////////////////////////////////
+void QPF_table_access::push_extra(enum Extra_tag extra_tag)
+{
+ extra_tags.append(extra_tag);
+}
+
+void append_possible_keys(String *str, TABLE *table, key_map possible_keys)
+{
+ uint j;
+ for (j=0 ; j < table->s->keys ; j++)
+ {
+ if (possible_keys.is_set(j))
+ {
+ if (str->length())
+ str->append(',');
+ str->append(table->key_info[j].name,
+ strlen(table->key_info[j].name),
+ system_charset_info);
+ }
+ }
+}
- Produce lines explaining execution of *this* select (not including children
- selects)
- @param on_the_fly TRUE <=> we're being executed on-the-fly, so don't make
- modifications to any select's data structures
+/*
+ Save Query Plan Footprint
+
+ @note
+ Currently, this function may be called multiple times
*/
-int JOIN::print_explain(select_result_sink *result, uint8 explain_flags,
- bool on_the_fly,
- bool need_tmp_table, bool need_order,
- bool distinct, const char *message)
+int JOIN::save_qpf(QPF_query *output, bool need_tmp_table, bool need_order,
+ bool distinct, const char *message)
{
- List<Item> field_list;
- List<Item> item_list;
+ QPF_node *qp_node;
+ const bool on_the_fly= true;
+
JOIN *join= this; /* Legacy: this code used to be a non-member function */
THD *thd=join->thd;
- Item *item_null= new Item_null();
- CHARSET_INFO *cs= system_charset_info;
+ const CHARSET_INFO *cs= system_charset_info;
int quick_type;
int error= 0;
- DBUG_ENTER("JOIN::print_explain");
+ DBUG_ENTER("JOIN::save_qpf");
DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s",
(ulong)join->select_lex, join->select_lex->type,
message ? message : "NULL"));
- DBUG_ASSERT(on_the_fly? have_query_plan == QEP_AVAILABLE: TRUE);
+ DBUG_ASSERT(have_query_plan == QEP_AVAILABLE);
/* Don't log this into the slow query log */
- if (!on_the_fly)
- {
- thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED);
- join->unit->offset_limit_cnt= 0;
- }
-
- /*
- NOTE: the number/types of items pushed into item_list must be in sync with
- EXPLAIN column types as they're "defined" in THD::send_explain_fields()
- */
if (message)
{
- if (print_explain_message_line(result, join->select_lex, on_the_fly,
- explain_flags, message))
- error= 1;
-
+ QPF_select *qp_sel;
+ qp_node= qp_sel= new (output->mem_root) QPF_select;
+ join->select_lex->set_explain_type(on_the_fly);
+
+ qp_sel->select_id= join->select_lex->select_number;
+ qp_sel->select_type= join->select_lex->type;
+ qp_sel->message= message;
+ /* Setting qp_sel->message means that all other members are invalid */
+ output->add_node(qp_sel);
}
else if (join->select_lex == join->unit->fake_select_lex)
{
- if (print_fake_select_lex_join(result, on_the_fly,
- join->select_lex,
- explain_flags))
- error= 1;
+#if 0
+ select_lex->set_explain_type(on_the_fly);
+ QPF_union *qp_union= new (output->mem_root) QPF_union;
+ qp_node= qp_union;
+
+ SELECT_LEX *child;
+ for (child= select_lex->master_unit()->first_select(); child;
+ child=child->next_select())
+ {
+ qp_union->add_select(child->select_number);
+ }
+
+ qp_union->fake_select_type= select_lex->type;
+ qp_union->using_filesort=
+ test(select_lex->master_unit()->global_parameters->order_list.first);
+
+ output->add_node(qp_union);
+#endif
}
else if (!join->select_lex->master_unit()->derived ||
join->select_lex->master_unit()->derived->is_materialized_derived())
{
+ QPF_select *qp_sel;
+ qp_node= qp_sel= new (output->mem_root) QPF_select;
table_map used_tables=0;
- //if (!join->select_lex->type)
+
if (on_the_fly)
join->select_lex->set_explain_type(on_the_fly);
- bool printing_materialize_nest= FALSE;
+ //bool printing_materialize_nest= FALSE;
uint select_id= join->select_lex->select_number;
+
+ qp_sel->select_id= join->select_lex->select_number;
+ qp_sel->select_type= join->select_lex->type;
+
JOIN_TAB* const first_top_tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS);
for (JOIN_TAB *tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); tab;
@@ -22297,18 +22556,15 @@ int JOIN::print_explain(select_result_si
{
JOIN_TAB *first_sibling= tab->bush_root_tab->bush_children->start;
select_id= first_sibling->emb_sj_nest->sj_subq_pred->get_identifier();
- printing_materialize_nest= TRUE;
+ //printing_materialize_nest= TRUE;
}
-
+
TABLE *table=tab->table;
TABLE_LIST *table_list= tab->table->pos_in_table_list;
- char buff[512];
- char buff1[512], buff2[512], buff3[512], buff4[512];
+ char buff2[512], buff3[512], buff4[512];
char keylen_str_buf[64];
my_bool key_read;
- String extra(buff, sizeof(buff),cs);
char table_name_buffer[SAFE_NAME_LEN];
- String tmp1(buff1,sizeof(buff1),cs);
String tmp2(buff2,sizeof(buff2),cs);
String tmp3(buff3,sizeof(buff3),cs);
String tmp4(buff4,sizeof(buff4),cs);
@@ -22317,8 +22573,6 @@ int JOIN::print_explain(select_result_si
uint key_len= 0;
bool is_hj= tab->type == JT_HASH || tab->type ==JT_HASH_NEXT;
- extra.length(0);
- tmp1.length(0);
tmp2.length(0);
tmp3.length(0);
tmp4.length(0);
@@ -22340,28 +22594,20 @@ int JOIN::print_explain(select_result_si
tab= pre_sort_join_tab;
}
- item_list.empty();
+ QPF_table_access *qpt= new (output->mem_root) QPF_table_access;
+ qp_sel->add_table(qpt);
+
/* id */
- item_list.push_back(new Item_uint((uint32)select_id));
+ if (tab->bush_root_tab)
+ qpt->sjm_nest_select_id= select_id;
+ else
+ qpt->sjm_nest_select_id= 0;
+
/* select_type */
- const char* stype= printing_materialize_nest? "MATERIALIZED" :
- join->select_lex->type;
- item_list.push_back(new Item_string(stype, strlen(stype), cs));
-
- enum join_type tab_type= tab->type;
- if ((tab->type == JT_ALL || tab->type == JT_HASH) &&
- tab->select && tab->select->quick && tab->use_quick != 2)
- {
- quick= tab->select->quick;
- quick_type= tab->select->quick->get_type();
- if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) ||
- (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) ||
- (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
- (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION))
- tab_type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE;
- else
- tab_type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE;
- }
+ //const char* stype= printing_materialize_nest? "MATERIALIZED" :
+ // join->select_lex->type;
+ //item_list.push_back(new Item_string(stype, strlen(stype), cs));
+ qp_sel->select_type= join->select_lex->type;
/* table */
if (table->derived_select_number)
@@ -22370,7 +22616,7 @@ int JOIN::print_explain(select_result_si
int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1,
"<derived%u>",
table->derived_select_number);
- item_list.push_back(new Item_string(table_name_buffer, len, cs));
+ qpt->table_name.copy(table_name_buffer, len, cs);
}
else if (tab->bush_children)
{
@@ -22380,59 +22626,58 @@ int JOIN::print_explain(select_result_si
sizeof(table_name_buffer)-1,
"<subquery%d>",
ctab->emb_sj_nest->sj_subq_pred->get_identifier());
- item_list.push_back(new Item_string(table_name_buffer, len, cs));
+ qpt->table_name.copy(table_name_buffer, len, cs);
}
else
{
TABLE_LIST *real_table= table->pos_in_table_list;
- item_list.push_back(new Item_string(real_table->alias,
- strlen(real_table->alias), cs));
+ qpt->table_name.copy(real_table->alias, strlen(real_table->alias), cs);
}
+
/* "partitions" column */
- if (explain_flags & DESCRIBE_PARTITIONS)
{
#ifdef WITH_PARTITION_STORAGE_ENGINE
partition_info *part_info;
if (!table->derived_select_number &&
(part_info= table->part_info))
{
- Item_string *item_str= new Item_string(cs);
- make_used_partitions_str(part_info, &item_str->str_value);
- item_list.push_back(item_str);
+ make_used_partitions_str(part_info, &qpt->used_partitions);
+ qpt->used_partitions_set= true;
}
else
- item_list.push_back(item_null);
+ qpt->used_partitions_set= false;
#else
/* just produce empty column if partitioning is not compiled in */
- item_list.push_back(item_null);
+ qpt->used_partitions_set= false;
#endif
}
+
/* "type" column */
- item_list.push_back(new Item_string(join_type_str[tab_type],
- strlen(join_type_str[tab_type]),
- cs));
- /* Build "possible_keys" value and add it to item_list */
- if (!tab->keys.is_clear_all())
+ enum join_type tab_type= tab->type;
+ if ((tab->type == JT_ALL || tab->type == JT_HASH) &&
+ tab->select && tab->select->quick && tab->use_quick != 2)
{
- uint j;
- for (j=0 ; j < table->s->keys ; j++)
- {
- if (tab->keys.is_set(j))
- {
- if (tmp1.length())
- tmp1.append(',');
- tmp1.append(table->key_info[j].name,
- strlen(table->key_info[j].name),
- system_charset_info);
- }
- }
+ quick= tab->select->quick;
+ quick_type= tab->select->quick->get_type();
+ if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) ||
+ (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) ||
+ (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
+ (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION))
+ tab_type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE;
+ else
+ tab_type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE;
}
- if (tmp1.length())
- item_list.push_back(new Item_string(tmp1.ptr(),tmp1.length(),cs));
- else
- item_list.push_back(item_null);
+ qpt->type= tab_type;
- /* Build "key", "key_len", and "ref" values and add them to item_list */
+ /* Build "possible_keys" value */
+ qpt->possible_keys= tab->keys;
+ append_possible_keys(&qpt->possible_keys_str, table, tab->keys);
+
+ /* Build "key", "key_len", and "ref" */
+
+ // tmp2 holds key_name
+ // tmp3 holds key_length
+ // tmp4 holds ref?
if (tab_type == JT_NEXT)
{
key_info= table->key_info+tab->index;
@@ -22443,6 +22688,7 @@ int JOIN::print_explain(select_result_si
key_info= tab->get_keyinfo_by_key_no(tab->ref.key);
key_len= tab->ref.key_length;
}
+
if (key_info)
{
register uint length;
@@ -22470,11 +22716,13 @@ int JOIN::print_explain(select_result_si
}
}
}
+
if (is_hj && tab_type != JT_HASH)
{
tmp2.append(':');
tmp3.append(':');
}
+
if (tab_type == JT_HASH_NEXT)
{
register uint length;
@@ -22484,23 +22732,36 @@ int JOIN::print_explain(select_result_si
length= (longlong10_to_str(key_len, keylen_str_buf, 10) -
keylen_str_buf);
tmp3.append(keylen_str_buf, length, cs);
- }
+ }
+
if (tab->type != JT_CONST && tab->select && quick)
tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3);
+
if (key_info || (tab->select && quick))
{
if (tmp2.length())
- item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
+ {
+ qpt->key.copy(tmp2);
+ qpt->key_set= true;
+ }
else
- item_list.push_back(item_null);
+ qpt->key_set= false;
+
if (tmp3.length())
- item_list.push_back(new Item_string(tmp3.ptr(),tmp3.length(),cs));
+ {
+ qpt->key_len.copy(tmp3);
+ qpt->key_len_set= true;
+ }
else
- item_list.push_back(item_null);
+ qpt->key_len_set= false;
+
if (key_info && tab_type != JT_NEXT)
- item_list.push_back(new Item_string(tmp4.ptr(),tmp4.length(),cs));
+ {
+ qpt->ref.copy(tmp4);
+ qpt->ref_set= true;
+ }
else
- item_list.push_back(item_null);
+ qpt->ref_set= false;
}
else
{
@@ -22525,51 +22786,52 @@ int JOIN::print_explain(select_result_si
tmp2.append(tmp_buff, strlen(tmp_buff), cs);
}
if (tmp2.length())
- item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
+ {
+ qpt->key.copy(tmp2);
+ qpt->key_set= true;
+ }
else
- item_list.push_back(item_null);
+ qpt->key_set= false;
}
else
- item_list.push_back(item_null);
- item_list.push_back(item_null);
- item_list.push_back(item_null);
+ qpt->key_set= false;
+
+ qpt->key_len_set= false;
+ qpt->ref_set= false;
}
- /* Add "rows" field to item_list. */
+ /* "rows" */
+
if (table_list /* SJM bushes don't have table_list */ &&
table_list->schema_table)
{
- /* in_rows */
- if (explain_flags & DESCRIBE_EXTENDED)
- item_list.push_back(item_null);
- /* rows */
- item_list.push_back(item_null);
+ /* I_S tables have rows=extra=NULL */
+ qpt->rows_set= false;
+ qpt->filtered_set= false;
}
else
{
ha_rows examined_rows= tab->get_examined_rows();
- item_list.push_back(new Item_int((longlong) (ulonglong) examined_rows,
- MY_INT64_NUM_DECIMAL_DIGITS));
+ qpt->rows_set= true;
+ qpt->rows= examined_rows;
- /* Add "filtered" field to item_list. */
- if (explain_flags & DESCRIBE_EXTENDED)
- {
- float f= 0.0;
- if (examined_rows)
- {
- double pushdown_cond_selectivity= tab->cond_selectivity;
- if (pushdown_cond_selectivity == 1.0)
- f= (float) (100.0 * tab->records_read / examined_rows);
- else
- f= (float) (100.0 * pushdown_cond_selectivity);
- }
- set_if_smaller(f, 100.0);
- item_list.push_back(new Item_float(f, 2));
+ /* "filtered" */
+ float f= 0.0;
+ if (examined_rows)
+ {
+ double pushdown_cond_selectivity= tab->cond_selectivity;
+ if (pushdown_cond_selectivity == 1.0)
+ f= (float) (100.0 * tab->records_read / examined_rows);
+ else
+ f= (float) (100.0 * pushdown_cond_selectivity);
}
+ set_if_smaller(f, 100.0);
+ qpt->filtered_set= true;
+ qpt->filtered= f;
}
- /* Build "Extra" field and add it to item_list. */
+ /* Build "Extra" field and save it */
key_read=table->key_read;
if ((tab_type == JT_NEXT || tab_type == JT_CONST) &&
table->covering_keys.is_set(tab->index))
@@ -22579,24 +22841,17 @@ int JOIN::print_explain(select_result_si
key_read=1;
if (tab->info)
- item_list.push_back(new Item_string(tab->info,strlen(tab->info),cs));
+ {
+ qpt->push_extra(tab->info);
+ }
else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
{
if (tab->packed_info & TAB_INFO_USING_INDEX)
- extra.append(STRING_WITH_LEN("; Using index"));
+ qpt->push_extra(ET_USING_INDEX);
if (tab->packed_info & TAB_INFO_USING_WHERE)
- extra.append(STRING_WITH_LEN("; Using where"));
+ qpt->push_extra(ET_USING_WHERE);
if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL)
- extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
- /* Skip initial "; "*/
- const char *str= extra.ptr();
- uint32 len= extra.length();
- if (len)
- {
- str += 2;
- len -= 2;
- }
- item_list.push_back(new Item_string(str, len, cs));
+ qpt->push_extra(ET_FULL_SCAN_ON_NULL_KEY);
}
else
{
@@ -22608,28 +22863,24 @@ int JOIN::print_explain(select_result_si
if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
table->file->pushed_idx_cond)
- extra.append(STRING_WITH_LEN("; Using index condition"));
+ qpt->push_extra(ET_USING_INDEX_CONDITION);
else if (tab->cache_idx_cond)
- extra.append(STRING_WITH_LEN("; Using index condition(BKA)"));
+ qpt->push_extra(ET_USING_INDEX_CONDITION_BKA);
if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT ||
quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)
{
- extra.append(STRING_WITH_LEN("; Using "));
- tab->select->quick->add_info_string(&extra);
+ qpt->push_extra(ET_USING);
+ tab->select->quick->add_info_string(&qpt->quick_info);
}
if (tab->select)
{
if (tab->use_quick == 2)
{
- /* 4 bits per 1 hex digit + terminating '\0' */
- char buf[MAX_KEY / 4 + 1];
- extra.append(STRING_WITH_LEN("; Range checked for each "
- "record (index map: 0x"));
- extra.append(tab->keys.print(buf));
- extra.append(')');
+ qpt->push_extra(ET_RANGE_CHECKED_FOR_EACH_RECORD);
+ qpt->range_checked_map= tab->keys;
}
else if (tab->select->cond)
{
@@ -22641,16 +22892,19 @@ int JOIN::print_explain(select_result_si
HA_MUST_USE_TABLE_CONDITION_PUSHDOWN)) &&
pushed_cond)
{
- extra.append(STRING_WITH_LEN("; Using where with pushed "
- "condition"));
+ qpt->push_extra(ET_USING_WHERE_WITH_PUSHED_CONDITION);
+ /*
+ psergey-todo: what to do? This was useful with NDB only.
+
if (explain_flags & DESCRIBE_EXTENDED)
{
extra.append(STRING_WITH_LEN(": "));
((COND *)pushed_cond)->print(&extra, QT_ORDINARY);
}
+ */
}
else
- extra.append(STRING_WITH_LEN("; Using where"));
+ qpt->push_extra(ET_USING_WHERE);
}
}
if (table_list /* SJM bushes don't have table_list */ &&
@@ -22658,19 +22912,20 @@ int JOIN::print_explain(select_result_si
table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
{
if (!table_list->table_open_method)
- extra.append(STRING_WITH_LEN("; Skip_open_table"));
+ qpt->push_extra(ET_SKIP_OPEN_TABLE);
else if (table_list->table_open_method == OPEN_FRM_ONLY)
- extra.append(STRING_WITH_LEN("; Open_frm_only"));
+ qpt->push_extra(ET_OPEN_FRM_ONLY);
else
- extra.append(STRING_WITH_LEN("; Open_full_table"));
+ qpt->push_extra(ET_OPEN_FULL_TABLE);
+ /* psergey-note: the following has a bug.*/
if (table_list->has_db_lookup_value &&
table_list->has_table_lookup_value)
- extra.append(STRING_WITH_LEN("; Scanned 0 databases"));
+ qpt->push_extra(ET_SCANNED_0_DATABASES);
else if (table_list->has_db_lookup_value ||
table_list->has_table_lookup_value)
- extra.append(STRING_WITH_LEN("; Scanned 1 database"));
+ qpt->push_extra(ET_SCANNED_1_DATABASE);
else
- extra.append(STRING_WITH_LEN("; Scanned all databases"));
+ qpt->push_extra(ET_SCANNED_ALL_DATABASES);
}
if (key_read)
{
@@ -22678,69 +22933,52 @@ int JOIN::print_explain(select_result_si
{
QUICK_GROUP_MIN_MAX_SELECT *qgs=
(QUICK_GROUP_MIN_MAX_SELECT *) tab->select->quick;
- extra.append(STRING_WITH_LEN("; Using index for group-by"));
- qgs->append_loose_scan_type(&extra);
+ qpt->push_extra(ET_USING_INDEX_FOR_GROUP_BY);
+ qpt->loose_scan_is_scanning= qgs->loose_scan_is_scanning();
}
else
- extra.append(STRING_WITH_LEN("; Using index"));
+ qpt->push_extra(ET_USING_INDEX);
}
if (table->reginfo.not_exists_optimize)
- extra.append(STRING_WITH_LEN("; Not exists"));
+ qpt->push_extra(ET_NOT_EXISTS);
- /*
- if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
- !(((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_flags &
- HA_MRR_USE_DEFAULT_IMPL))
- {
- extra.append(STRING_WITH_LEN("; Using MRR"));
- }
- */
if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE)
{
- char mrr_str_buf[128];
- mrr_str_buf[0]=0;
- int len;
- uint mrr_flags=
- ((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_flags;
- len= table->file->multi_range_read_explain_info(mrr_flags,
- mrr_str_buf,
- sizeof(mrr_str_buf));
- if (len > 0)
- {
- extra.append(STRING_WITH_LEN("; "));
- extra.append(mrr_str_buf, len);
- }
+ explain_append_mrr_info((QUICK_RANGE_SELECT*)(tab->select->quick),
+ &qpt->mrr_type);
+ if (qpt->mrr_type.length() > 0)
+ qpt->push_extra(ET_USING_MRR);
}
if (need_tmp_table)
{
need_tmp_table=0;
- extra.append(STRING_WITH_LEN("; Using temporary"));
+ qp_sel->using_temporary= true;
}
if (need_order)
{
need_order=0;
- extra.append(STRING_WITH_LEN("; Using filesort"));
+ qp_sel->using_filesort= true;
}
if (distinct & test_all_bits(used_tables,
join->select_list_used_tables))
- extra.append(STRING_WITH_LEN("; Distinct"));
+ qpt->push_extra(ET_DISTINCT);
if (tab->loosescan_match_tab)
{
- extra.append(STRING_WITH_LEN("; LooseScan"));
+ qpt->push_extra(ET_LOOSESCAN);
}
if (tab->first_weedout_table)
- extra.append(STRING_WITH_LEN("; Start temporary"));
+ qpt->push_extra(ET_START_TEMPORARY);
if (tab->check_weed_out_table)
- extra.append(STRING_WITH_LEN("; End temporary"));
+ qpt->push_extra(ET_END_TEMPORARY);
else if (tab->do_firstmatch)
{
if (tab->do_firstmatch == /*join->join_tab*/ first_top_tab - 1)
- extra.append(STRING_WITH_LEN("; FirstMatch"));
+ qpt->push_extra(ET_FIRST_MATCH);
else
{
- extra.append(STRING_WITH_LEN("; FirstMatch("));
+ qpt->push_extra(ET_FIRST_MATCH);
TABLE *prev_table=tab->do_firstmatch->table;
if (prev_table->derived_select_number)
{
@@ -22749,11 +22987,10 @@ int JOIN::print_explain(select_result_si
int len= my_snprintf(namebuf, sizeof(namebuf)-1,
"<derived%u>",
prev_table->derived_select_number);
- extra.append(namebuf, len);
+ qpt->firstmatch_table_name.append(namebuf, len);
}
else
- extra.append(prev_table->pos_in_table_list->alias);
- extra.append(STRING_WITH_LEN(")"));
+ qpt->firstmatch_table_name.append(prev_table->pos_in_table_list->alias);
}
}
@@ -22761,26 +22998,16 @@ int JOIN::print_explain(select_result_si
{
if (tab->ref.cond_guards[part])
{
- extra.append(STRING_WITH_LEN("; Full scan on NULL key"));
+ qpt->push_extra(ET_FULL_SCAN_ON_NULL_KEY);
break;
}
}
if (tab->cache)
{
- extra.append(STRING_WITH_LEN("; Using join buffer"));
- tab->cache->print_explain_comment(&extra);
- }
-
- /* Skip initial "; "*/
- const char *str= extra.ptr();
- uint32 len= extra.length();
- if (len)
- {
- str += 2;
- len -= 2;
+ qpt->push_extra(ET_USING_JOIN_BUFFER);
+ tab->cache->save_qpf(&qpt->bka_type);
}
- item_list.push_back(new Item_string(str, len, cs));
}
if (saved_join_tab)
@@ -22788,16 +23015,42 @@ int JOIN::print_explain(select_result_si
// For next iteration
used_tables|=table->map;
- if (result->send_data(item_list))
- error= 1;
+ }
+ output->add_node(qp_sel);
+ }
+
+ for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit();
+ unit;
+ unit= unit->next_unit())
+ {
+ /*
+ Display subqueries only if they are not parts of eliminated WHERE/ON
+ clauses.
+ */
+ if (!(unit->item && unit->item->eliminated))
+ {
+ qp_node->add_child(unit->first_select()->select_number);
}
}
+
DBUG_RETURN(error);
}
/*
- See st_select_lex::print_explain() for the SHOW EXPLAIN counterpart
+ This function servers as "shortcut point" for EXPLAIN queries.
+
+ For UNIONs and JOINs, EXPLAIN statement executes just like its SELECT
+ statement would execute, except that JOIN::exec() will call select_describe()
+ instead of actually executing the query.
+
+ The purpose of select_describe() is:
+ - update the query plan with info about last-minute choices made at the start
+ of JOIN::exec
+ - Invoke "pseudo-execution" for the children subqueries.
+
+ Overall, select_describe() is a legacy of old EXPLAIN implementation and
+ should be removed.
*/
static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
@@ -22806,10 +23059,14 @@ static void select_describe(JOIN *join,
THD *thd=join->thd;
select_result *result=join->result;
DBUG_ENTER("select_describe");
- join->error= join->print_explain(result, thd->lex->describe,
- FALSE, /* Not on-the-fly */
- need_tmp_table, need_order, distinct,
- message);
+
+ // Update the QPF:
+ QPF_select *qp;
+ if ((qp= thd->lex->query_plan_footprint->get_select(join->select_lex->select_number)))
+ {
+ qp->using_temporary= need_tmp_table;
+ qp->using_filesort= need_order;
+ }
for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit();
unit;
=== modified file 'sql/sql_select.h'
--- sql/sql_select.h 2013-06-06 15:51:28 +0000
+++ sql/sql_select.h 2013-06-27 08:01:19 +0000
@@ -198,6 +198,12 @@ int rr_sequential(READ_RECORD *info);
int rr_sequential_and_unpack(READ_RECORD *info);
+#include "opt_qpf.h"
+
+/**************************************************************************************
+ * New EXPLAIN structures END
+ *************************************************************************************/
+
class JOIN_CACHE;
class SJ_TMP_TABLE;
class JOIN_TAB_RANGE;
@@ -252,7 +258,9 @@ typedef struct st_join_table {
JOIN_TAB_RANGE *bush_children;
/* Special content for EXPLAIN 'Extra' column or NULL if none */
- const char *info;
+ enum Extra_tag info;
+ //const char *info;
+
/*
Bitmap of TAB_INFO_* bits that encodes special line for EXPLAIN 'Extra'
column, or 0 if there is no info.
@@ -1328,7 +1336,10 @@ class JOIN :public Sql_alloc
pre_sort_join_tab= NULL;
emb_sjm_nest= NULL;
sjm_lookup_tables= 0;
+
+ exec_qpf_saved= false;
}
+ bool exec_qpf_saved;
int prepare(Item ***rref_pointer_array, TABLE_LIST *tables, uint wind_num,
COND *conds, uint og_num, ORDER *order, ORDER *group,
@@ -1454,11 +1465,8 @@ class JOIN :public Sql_alloc
{
return (unit->item && unit->item->is_in_predicate());
}
-
- int print_explain(select_result_sink *result, uint8 explain_flags,
- bool on_the_fly,
- bool need_tmp_table, bool need_order,
- bool distinct,const char *message);
+ int save_qpf(QPF_query *output, bool need_tmp_table, bool need_order,
+ bool distinct, const char *message);
private:
/**
TRUE if the query contains an aggregate function but has no GROUP
@@ -1828,6 +1836,28 @@ void push_index_cond(JOIN_TAB *tab, uint
#define OPT_LINK_EQUAL_FIELDS 1
+/* EXPLAIN-related utility functions */
+int print_explain_message_line(select_result_sink *result,
+ uint8 options,
+ uint select_number,
+ const char *select_type,
+ const char *message);
+void explain_append_mrr_info(QUICK_RANGE_SELECT *quick, String *res);
+int print_explain_row(select_result_sink *result,
+ uint8 options,
+ uint select_number,
+ const char *select_type,
+ const char *table_name,
+ //const char *partitions, (todo)
+ enum join_type jtype,
+ const char *possible_keys,
+ const char *index,
+ const char *key_len,
+ const char *ref,
+ ha_rows rows,
+ const char *extra);
+void make_possible_keys_line(TABLE *table, key_map possible_keys, String *line);
+
/****************************************************************************
Temporary table support for SQL Runtime
***************************************************************************/
=== modified file 'sql/sql_show.cc'
--- sql/sql_show.cc 2013-06-15 17:10:00 +0000
+++ sql/sql_show.cc 2013-06-27 18:36:59 +0000
@@ -2345,8 +2345,8 @@ void Show_explain_request::call_in_targe
DBUG_ASSERT(current_thd == target_thd);
set_current_thd(request_thd);
- if (target_thd->lex->unit.print_explain(explain_buf, 0 /* explain flags*/,
- &printed_anything))
+ if (target_thd->lex->print_explain(explain_buf, 0 /* explain flags*/,
+ &printed_anything))
{
failed_to_produce= TRUE;
}
=== modified file 'sql/sql_string.h'
--- sql/sql_string.h 2013-06-06 15:51:28 +0000
+++ sql/sql_string.h 2013-06-27 18:36:59 +0000
@@ -510,6 +510,38 @@ class String
}
};
+
+// The following class is a backport from MySQL 5.6:
+/**
+ String class wrapper with a preallocated buffer of size buff_sz
+
+ This class allows to replace sequences of:
+ char buff[12345];
+ String str(buff, sizeof(buff));
+ str.length(0);
+ with a simple equivalent declaration:
+ StringBuffer<12345> str;
+*/
+
+template<size_t buff_sz>
+class StringBuffer : public String
+{
+ char buff[buff_sz];
+
+public:
+ StringBuffer() : String(buff, buff_sz, &my_charset_bin) { length(0); }
+ explicit StringBuffer(const CHARSET_INFO *cs) : String(buff, buff_sz, cs)
+ {
+ length(0);
+ }
+ StringBuffer(const char *str, size_t length, const CHARSET_INFO *cs)
+ : String(buff, buff_sz, cs)
+ {
+ set(str, length, cs);
+ }
+};
+
+
static inline bool check_if_only_end_space(CHARSET_INFO *cs,
const char *str,
const char *end)
=== modified file 'sql/sql_union.cc'
--- sql/sql_union.cc 2013-01-29 14:10:47 +0000
+++ sql/sql_union.cc 2013-06-27 10:19:01 +0000
@@ -617,6 +617,7 @@ bool st_select_lex_unit::exec()
ulonglong add_rows=0;
ha_rows examined_rows= 0;
DBUG_ENTER("st_select_lex_unit::exec");
+ bool was_executed= executed;
if (executed && !uncacheable && !describe)
DBUG_RETURN(FALSE);
@@ -626,6 +627,9 @@ bool st_select_lex_unit::exec()
saved_error= optimize();
+ if (!was_executed && thd->lex->query_plan_footprint)
+ save_union_qpf(thd->lex->query_plan_footprint);
+
if (uncacheable || !item || !item->assigned() || describe)
{
for (SELECT_LEX *sl= select_cursor; sl; sl= sl->next_select())
@@ -772,6 +776,9 @@ bool st_select_lex_unit::exec()
*/
if (!fake_select_lex->ref_pointer_array)
fake_select_lex->n_child_sum_items+= global_parameters->n_sum_items;
+
+ if (!was_executed && thd->lex->query_plan_footprint)
+ save_union_qpf_part2(thd->lex->query_plan_footprint);
saved_error= mysql_select(thd, &fake_select_lex->ref_pointer_array,
&result_table_list,
=== modified file 'sql/sql_update.cc'
--- sql/sql_update.cc 2013-03-27 22:41:02 +0000
+++ sql/sql_update.cc 2013-06-27 16:28:16 +0000
@@ -260,7 +260,7 @@ int mysql_update(THD *thd,
bool can_compare_record;
int res;
int error, loc_error;
- uint used_index, dup_key_found;
+ uint dup_key_found;
bool need_sort= TRUE;
bool reverse= FALSE;
#ifndef NO_EMBEDDED_ACCESS_CHECKS
@@ -270,12 +270,16 @@ int mysql_update(THD *thd,
ha_rows updated, found;
key_map old_covering_keys;
TABLE *table;
- SQL_SELECT *select;
+ SQL_SELECT *select= NULL;
READ_RECORD info;
SELECT_LEX *select_lex= &thd->lex->select_lex;
ulonglong id;
List<Item> all_fields;
killed_state killed_status= NOT_KILLED;
+ Update_plan query_plan;
+ query_plan.index= MAX_KEY;
+ query_plan.using_filesort= FALSE;
+ bool apc_target_enabled= false; // means was enabled *by code this function*
DBUG_ENTER("mysql_update");
if (open_tables(thd, &table_list, &table_count, 0))
@@ -310,10 +314,16 @@ int mysql_update(THD *thd,
my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE");
DBUG_RETURN(1);
}
+
+ //psergey-todo: Ugly, discuss with Sanja
+ query_plan.updating_a_view= test(table_list->view);
+
/* Calculate "table->covering_keys" based on the WHERE */
table->covering_keys= table->s->keys_in_use;
table->quick_keys.clear_all();
+ query_plan.select_lex= &thd->lex->select_lex;
+ query_plan.table= table;
#ifndef NO_EMBEDDED_ACCESS_CHECKS
/* Force privilege re-checking for views after they have been opened. */
want_privilege= (table_list->view ? UPDATE_ACL :
@@ -370,7 +380,12 @@ int mysql_update(THD *thd,
Item::cond_result cond_value;
conds= remove_eq_conds(thd, conds, &cond_value);
if (cond_value == Item::COND_FALSE)
+ {
limit= 0; // Impossible WHERE
+ query_plan.set_impossible_where();
+ if (thd->lex->describe)
+ goto exit_without_my_ok;
+ }
}
/*
@@ -400,6 +415,10 @@ int mysql_update(THD *thd,
if (error || !limit || thd->is_error() ||
(select && select->check_quick(thd, safe_update, limit)))
{
+ query_plan.set_impossible_where();
+ if (thd->lex->describe)
+ goto exit_without_my_ok;
+
delete select;
free_underlaid_joins(thd, select_lex);
/*
@@ -438,16 +457,16 @@ int mysql_update(THD *thd,
if (select && select->quick && select->quick->unique_key_range())
{ // Single row select (always "ordered"): Ok to use with key field UPDATE
need_sort= FALSE;
- used_index= MAX_KEY;
+ query_plan.index= MAX_KEY;
used_key_is_modified= FALSE;
}
else
{
- used_index= get_index_for_order(order, table, select, limit,
- &need_sort, &reverse);
+ query_plan.index= get_index_for_order(order, table, select, limit,
+ &need_sort, &reverse);
if (select && select->quick)
{
- DBUG_ASSERT(need_sort || used_index == select->quick->index);
+ DBUG_ASSERT(need_sort || query_plan.index == select->quick->index);
used_key_is_modified= (!select->quick->unique_key_range() &&
select->quick->is_keys_used(table->write_set));
}
@@ -455,14 +474,38 @@ int mysql_update(THD *thd,
{
if (need_sort)
{ // Assign table scan index to check below for modified key fields:
- used_index= table->file->key_used_on_scan;
+ query_plan.index= table->file->key_used_on_scan;
}
- if (used_index != MAX_KEY)
+ if (query_plan.index != MAX_KEY)
{ // Check if we are modifying a key that we are used to search with:
- used_key_is_modified= is_key_used(table, used_index, table->write_set);
+ used_key_is_modified= is_key_used(table, query_plan.index, table->write_set);
}
}
}
+
+ /*
+ Query optimization is finished at this point.
+ - Save the decisions in the query plan
+ - if we're running EXPLAIN UPDATE, get out
+ */
+ query_plan.select= select;
+ query_plan.possible_keys= table->quick_keys;
+ query_plan.table_rows= table->file->stats.records;
+
+ /*
+ Ok, we have generated a query plan for the UPDATE.
+ - if we're running EXPLAIN UPDATE, goto produce explain output
+ - otherwise, execute the query plan
+ */
+ if (thd->lex->describe)
+ goto exit_without_my_ok;
+
+ query_plan.save_query_plan_footprint(thd->lex->query_plan_footprint);
+ thd->apc_target.enable();
+ apc_target_enabled= true;
+ DBUG_EXECUTE_IF("show_explain_probe_update_exec_start",
+ dbug_serve_apcs(thd, 1););
+
if (used_key_is_modified || order ||
partition_key_modified(table, table->write_set))
@@ -476,8 +519,8 @@ int mysql_update(THD *thd,
DBUG_ASSERT(table->read_set == &table->def_read_set);
DBUG_ASSERT(table->write_set == &table->def_write_set);
- if (used_index < MAX_KEY && old_covering_keys.is_set(used_index))
- table->add_read_columns_used_by_index(used_index);
+ if (query_plan.index < MAX_KEY && old_covering_keys.is_set(query_plan.index))
+ table->add_read_columns_used_by_index(query_plan.index);
else
table->use_all_columns();
@@ -534,22 +577,22 @@ int mysql_update(THD *thd,
/*
When we get here, we have one of the following options:
- A. used_index == MAX_KEY
+ A. query_plan.index == MAX_KEY
This means we should use full table scan, and start it with
init_read_record call
- B. used_index != MAX_KEY
+ B. query_plan.index != MAX_KEY
B.1 quick select is used, start the scan with init_read_record
B.2 quick select is not used, this is full index scan (with LIMIT)
Full index scan must be started with init_read_record_idx
*/
- if (used_index == MAX_KEY || (select && select->quick))
+ if (query_plan.index == MAX_KEY || (select && select->quick))
{
if (init_read_record(&info, thd, table, select, 0, 1, FALSE))
goto err;
}
else
- init_read_record_idx(&info, thd, table, 1, used_index, reverse);
+ init_read_record_idx(&info, thd, table, 1, query_plan.index, reverse);
thd_proc_info(thd, "Searching rows for update");
ha_rows tmp_limit= limit;
@@ -610,6 +653,7 @@ int mysql_update(THD *thd,
select= new SQL_SELECT;
select->head=table;
}
+ //psergey-todo: disable SHOW EXPLAIN because the plan was deleted?
if (reinit_io_cache(&tempfile,READ_CACHE,0L,0,0))
error=1; /* purecov: inspected */
select->file=tempfile; // Read row ptrs from this file
@@ -884,6 +928,8 @@ int mysql_update(THD *thd,
if (!transactional_table && updated > 0)
thd->transaction.stmt.modified_non_trans_table= TRUE;
+ thd->apc_target.disable(); //psergey-todo.
+ apc_target_enabled= false;
end_read_record(&info);
delete select;
thd_proc_info(thd, "end");
@@ -957,11 +1003,36 @@ int mysql_update(THD *thd,
DBUG_RETURN((error >= 0 || thd->is_error()) ? 1 : 0);
err:
+ if (apc_target_enabled)
+ thd->apc_target.disable();
+
delete select;
free_underlaid_joins(thd, select_lex);
table->disable_keyread();
thd->abort_on_warning= 0;
DBUG_RETURN(1);
+
+exit_without_my_ok:
+ DBUG_ASSERT(!apc_target_enabled);
+ query_plan.save_query_plan_footprint(thd->lex->query_plan_footprint);
+
+ select_send *result;
+ //bool printed_anything;
+ if (!(result= new select_send()))
+ return 1; /* purecov: inspected */
+ List<Item> dummy; /* note: looked in 5.6 and they too use a dummy list like this */
+ result->prepare(dummy, &thd->lex->unit);
+ thd->send_explain_fields(result);
+ int err2= thd->lex->query_plan_footprint->print_explain(result, 0 /* explain flags*/);
+
+ if (err2)
+ result->abort_result_set();
+ else
+ result->send_eof();
+
+ delete select;
+ free_underlaid_joins(thd, select_lex);
+ DBUG_RETURN((error >= 0 || thd->is_error()) ? 1 : 0);
}
/*
@@ -1381,20 +1452,37 @@ bool mysql_multi_update(THD *thd,
multi_update **result)
{
bool res;
+ select_result *output;
+ bool explain= test(thd->lex->describe);
DBUG_ENTER("mysql_multi_update");
-
- if (!(*result= new multi_update(table_list,
- &thd->lex->select_lex.leaf_tables,
- fields, values,
- handle_duplicates, ignore)))
+
+ if (explain)
{
- DBUG_RETURN(TRUE);
+ /* Handle EXPLAIN UPDATE */
+ if (!(output= new select_send()) ||
+ thd->send_explain_fields(output))
+ {
+ delete output;
+ DBUG_RETURN(TRUE);
+ }
+ select_lex->set_explain_type(FALSE);
+ *result= NULL; /* no multi_update object */
+ }
+ else
+ {
+ if (!(*result= new multi_update(table_list,
+ &thd->lex->select_lex.leaf_tables,
+ fields, values,
+ handle_duplicates, ignore)))
+ {
+ DBUG_RETURN(TRUE);
+ }
+ output= *result;
}
thd->abort_on_warning= test(thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
MODE_STRICT_ALL_TABLES));
-
List<Item> total_list;
res= mysql_select(thd, &select_lex->ref_pointer_array,
@@ -1404,12 +1492,21 @@ bool mysql_multi_update(THD *thd,
(ORDER *)NULL,
options | SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK |
OPTION_SETUP_TABLES_DONE,
- *result, unit, select_lex);
+ output, unit, select_lex);
DBUG_PRINT("info",("res: %d report_error: %d", res, (int) thd->is_error()));
res|= thd->is_error();
if (unlikely(res))
(*result)->abort_result_set();
+ else
+ {
+ if (explain)
+ {
+ thd->lex->query_plan_footprint->print_explain(output, 0);
+ output->send_eof();
+ delete output;
+ }
+ }
thd->abort_on_warning= 0;
DBUG_RETURN(res);
}
=== modified file 'sql/sql_yacc.yy'
--- sql/sql_yacc.yy 2013-06-24 18:56:49 +0000
+++ sql/sql_yacc.yy 2013-06-27 18:36:59 +0000
@@ -11966,13 +11966,21 @@ select_var_ident:
opt_describe_column {}
| describe_command opt_extended_describe
{ Lex->describe|= DESCRIBE_NORMAL; }
- select
+ explanable_command
{
LEX *lex=Lex;
lex->select_lex.options|= SELECT_DESCRIBE;
}
;
+explanable_command:
+ select
+ | insert
+ | replace
+ | update
+ | delete
+ ;
+
describe_command:
DESC
| DESCRIBE
=== modified file 'support-files/build-tags'
--- support-files/build-tags 2010-03-15 19:52:58 +0000
+++ support-files/build-tags 2013-06-21 13:12:55 +0000
@@ -6,7 +6,7 @@ filter='\.cc$\|\.c$\|\.h$\|\.yy$'
list="find . -type f"
bzr root >/dev/null 2>/dev/null && list="bzr ls --from-root -R --kind=file --versioned"
-$list |grep $filter |while read f;
+$list |grep $filter | grep -v gen-cpp |while read f;
do
etags -o TAGS --append $f
done
