maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #04791
Review request: SHOW EXPLAIN, attempt 2
Hi Sergei,
Hi Sergei,
Please find attached the updated combined SHOW EXPLAIN patch. I believe all
of feedback from the previous review has been addressed, except for:
- Request to use of dbug sync facility for testing: this is waiting for you to
come up with idea about how this can be done,
- Error messages: you've indicated you were not happy with the current ones but
I don't have any clue what set of errors would be better.
BR
Sergei
--
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
=== modified file '.bzrignore'
--- .bzrignore 2012-05-22 09:04:32 +0000
+++ .bzrignore 2012-06-14 20:50:17 +0000
@@ -1137,6 +1137,7 @@ plugin/handler_socket/perl-Net-HandlerSo
libmysqld/libmysqld_exports_file.cc
libmysqld/gcalc_slicescan.cc
libmysqld/gcalc_tools.cc
+libmysqld/my_apc.cc
sql/share/errmsg.sys
sql/share/mysql
install_manifest.txt
=== modified file 'CMakeLists.txt'
--- CMakeLists.txt 2012-05-22 09:04:32 +0000
+++ CMakeLists.txt 2012-06-28 12:46:24 +0000
@@ -279,6 +279,7 @@ IF(WITH_UNIT_TESTS)
ADD_SUBDIRECTORY(unittest/strings)
ADD_SUBDIRECTORY(unittest/examples)
ADD_SUBDIRECTORY(unittest/mysys)
+ ADD_SUBDIRECTORY(unittest/sql)
ENDIF()
IF(NOT WITHOUT_SERVER)
=== modified file 'client/mysqltest.cc'
--- client/mysqltest.cc 2012-05-21 18:54:41 +0000
+++ client/mysqltest.cc 2012-06-14 20:50:17 +0000
@@ -84,6 +84,8 @@ static my_bool non_blocking_api_enabled=
#define QUERY_SEND_FLAG 1
#define QUERY_REAP_FLAG 2
+#define QUERY_PRINT_ORIGINAL_FLAG 4
+
#ifndef HAVE_SETENV
static int setenv(const char *name, const char *value, int overwrite);
#endif
@@ -342,7 +344,8 @@ enum enum_commands {
Q_ERROR,
Q_SEND, Q_REAP,
Q_DIRTY_CLOSE, Q_REPLACE, Q_REPLACE_COLUMN,
- Q_PING, Q_EVAL,
+ Q_PING, Q_EVAL,
+ Q_EVALP,
Q_EVAL_RESULT,
Q_ENABLE_QUERY_LOG, Q_DISABLE_QUERY_LOG,
Q_ENABLE_RESULT_LOG, Q_DISABLE_RESULT_LOG,
@@ -408,6 +411,7 @@ const char *command_names[]=
"replace_column",
"ping",
"eval",
+ "evalp",
"eval_result",
/* Enable/disable that the _query_ is logged to result file */
"enable_query_log",
@@ -8269,7 +8273,8 @@ void run_query(struct st_connection *cn,
/*
Evaluate query if this is an eval command
*/
- if (command->type == Q_EVAL || command->type == Q_SEND_EVAL)
+ if (command->type == Q_EVAL || command->type == Q_SEND_EVAL ||
+ command->type == Q_EVALP)
{
init_dynamic_string(&eval_query, "", command->query_len+256, 1024);
do_eval(&eval_query, command->query, command->end, FALSE);
@@ -8301,10 +8306,20 @@ void run_query(struct st_connection *cn,
*/
if (!disable_query_log && (flags & QUERY_SEND_FLAG))
{
- replace_dynstr_append_mem(ds, query, query_len);
+ char *print_query= query;
+ int print_len= query_len;
+ if (flags & QUERY_PRINT_ORIGINAL_FLAG)
+ {
+ print_query= command->query;
+ print_len= command->end - command->query;
+ }
+ replace_dynstr_append_mem(ds, print_query, print_len);
dynstr_append_mem(ds, delimiter, delimiter_length);
dynstr_append_mem(ds, "\n", 1);
}
+
+ /* We're done with this flag */
+ flags &= ~QUERY_PRINT_ORIGINAL_FLAG;
/*
Write the command to the result file before we execute the query
@@ -9162,6 +9177,7 @@ int main(int argc, char **argv)
case Q_EVAL_RESULT:
die("'eval_result' command is deprecated");
case Q_EVAL:
+ case Q_EVALP:
case Q_QUERY_VERTICAL:
case Q_QUERY_HORIZONTAL:
if (command->query == command->query_buf)
@@ -9189,6 +9205,9 @@ int main(int argc, char **argv)
flags= QUERY_REAP_FLAG;
}
+ if (command->type == Q_EVALP)
+ flags |= QUERY_PRINT_ORIGINAL_FLAG;
+
/* Check for special property for this query */
display_result_vertically|= (command->type == Q_QUERY_VERTICAL);
=== modified file 'libmysqld/CMakeLists.txt'
--- libmysqld/CMakeLists.txt 2012-05-22 09:04:32 +0000
+++ libmysqld/CMakeLists.txt 2012-06-14 20:50:17 +0000
@@ -95,6 +95,7 @@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc l
../sql/create_options.cc ../sql/rpl_utility.cc
../sql/rpl_reporting.cc
../sql/sql_expression_cache.cc
+ ../sql/my_apc.cc ../sql/my_apc.h
${GEN_SOURCES}
${MYSYS_LIBWRAP_SOURCE}
)
=== modified file 'mysql-test/include/index_merge2.inc'
--- mysql-test/include/index_merge2.inc 2012-01-16 19:16:35 +0000
+++ mysql-test/include/index_merge2.inc 2012-06-07 16:03:36 +0000
@@ -343,6 +343,7 @@ alter table t1 add index i3(key3);
update t1 set key2=key1,key3=key1;
# to test the bug, the following must use "sort_union":
+--replace_column 9 ROWS
explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
drop table t1;
=== modified file 'mysql-test/r/index_merge_innodb.result'
--- mysql-test/r/index_merge_innodb.result 2012-06-04 15:26:11 +0000
+++ mysql-test/r/index_merge_innodb.result 2012-06-14 20:50:17 +0000
@@ -313,7 +313,7 @@ alter table t1 add index i3(key3);
update t1 set key2=key1,key3=key1;
explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 9 Using sort_union(i3,i2); Using where
+1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL ROWS Using sort_union(i3,i2); Using where
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
key1 key2 key3
31 31 31
=== modified file 'mysql-test/r/index_merge_myisam.result'
--- mysql-test/r/index_merge_myisam.result 2012-02-15 17:08:08 +0000
+++ mysql-test/r/index_merge_myisam.result 2012-06-07 16:03:36 +0000
@@ -1146,7 +1146,7 @@ alter table t1 add index i3(key3);
update t1 set key2=key1,key3=key1;
explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 11 Using sort_union(i3,i2); Using where
+1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL ROWS Using sort_union(i3,i2); Using where
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
key1 key2 key3
31 31 31
=== added file 'mysql-test/r/show_explain.result'
--- mysql-test/r/show_explain.result 1970-01-01 00:00:00 +0000
+++ mysql-test/r/show_explain.result 2012-07-10 17:23:00 +0000
@@ -0,0 +1,823 @@
+drop table if exists t0, t1, t2, t3, t4;
+drop view if exists v1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int);
+insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C;
+alter table t1 add b int, add c int, add filler char(32);
+update t1 set b=a, c=a, filler='fooo';
+alter table t1 add key(a), add key(b);
+show explain for 2*1000*1000*1000;
+ERROR HY000: Unknown thread id: 2000000000
+show explain for (select max(a) from t0);
+ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+show explain for $thr1;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select count(*) from t1 where a < 100000;
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index a a 5 NULL 1000 Using where; Using index
+Warnings:
+Note 1003 select count(*) from t1 where a < 100000
+count(*)
+1000
+select max(c) from t1 where a < 10;
+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 10 Using index condition
+Warnings:
+Note 1003 select max(c) from t1 where a < 10
+max(c)
+9
+# We can catch EXPLAIN, too.
+set @show_expl_tmp= @@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on';
+explain select max(c) from t1 where a < 10;
+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 10 Using index condition; Rowid-ordered scan
+Warnings:
+Note 1003 explain select max(c) from t1 where a < 10
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan
+set optimizer_switch= @show_expl_tmp;
+# UNION, first branch
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+explain select a from t0 A union select a+1 from t0 B;
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY A ALL NULL NULL NULL NULL 10
+2 UNION B ALL NULL NULL NULL NULL 10
+NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 explain select a from t0 A union select a+1 from t0 B
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY A ALL NULL NULL NULL NULL 10
+2 UNION B ALL NULL NULL NULL NULL 10
+NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
+# UNION, second branch
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+explain select a from t0 A union select a+1 from t0 B;
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY A ALL NULL NULL NULL NULL 10
+2 UNION B ALL NULL NULL NULL NULL 10
+NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 explain select a from t0 A union select a+1 from t0 B
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY A ALL NULL NULL NULL NULL 10
+2 UNION B ALL NULL NULL NULL NULL 10
+NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
+# Uncorrelated subquery, select
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 B) from t0 A where a<1;
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
+2 SUBQUERY B ALL NULL NULL NULL NULL 10
+Warnings:
+Note 1003 select a, (select max(a) from t0 B) from t0 A where a<1
+a (select max(a) from t0 B)
+0 9
+# Uncorrelated subquery, explain
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+explain select a, (select max(a) from t0 B) from t0 A where a<1;
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
+2 SUBQUERY B ALL NULL NULL NULL NULL 10
+Warnings:
+Note 1003 explain select a, (select max(a) from t0 B) from t0 A where a<1
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
+2 SUBQUERY B ALL NULL NULL NULL NULL 10
+# correlated subquery, select
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+show explain for $thr2;
+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
+# correlated subquery, explain
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+show explain for $thr2;
+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
+# correlated subquery, select, while inside the subquery
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+show explain for $thr2;
+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
+# correlated subquery, explain, while inside the subquery
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+show explain for $thr2;
+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
+# correlated subquery, explain, while inside the subquery
+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: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+a (select max(a) from t0 b where b.a+a.a<10)
+0 9
+# Try to do SHOW EXPLAIN for a query that runs a SET command:
+# I've found experimentally that select_id==2 here...
+#
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+set @foo= (select max(a) from t0 where sin(a) >0);
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+#
+# Attempt SHOW EXPLAIN for an UPDATE
+#
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+drop table t2;
+#
+# Attempt SHOW EXPLAIN for a DELETE
+#
+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: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+drop table t2;
+#
+# Multiple SHOW EXPLAIN calls for one select
+#
+create table t2 as select a as a, a as dummy from t0 limit 3;
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+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 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
+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
+Warnings:
+Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
+a SUBQ
+0 0
+1 0
+2 0
+drop table t2;
+#
+# SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort"
+#
+explain select * from t0 order by a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using filesort
+set debug_dbug='d,show_explain_probe_join_exec_start';
+set @show_explain_probe_select_id=1;
+select * from t0 order by a;
+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 10 Using filesort
+Warnings:
+Note 1003 select * from t0 order by a
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+#
+# SHOW EXPLAIN for SELECT ... with "Using temporary"
+#
+explain select distinct a from t0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary
+set debug_dbug='d,show_explain_probe_join_exec_start';
+set @show_explain_probe_select_id=1;
+select distinct a from t0;
+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 10 Using temporary
+Warnings:
+Note 1003 select distinct a from t0
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+#
+# SHOW EXPLAIN for SELECT ... with "Using temporary; Using filesort"
+#
+explain select distinct a from t0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary
+set debug_dbug='d,show_explain_probe_join_exec_start';
+set @show_explain_probe_select_id=1;
+select distinct a from t0;
+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 10 Using temporary
+Warnings:
+Note 1003 select distinct a from t0
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+set debug_dbug='';
+#
+# MDEV-238: SHOW EXPLAIN: Server crashes in JOIN::print_explain with FROM subquery and GROUP BY
+#
+CREATE TABLE t2 ( a INT );
+INSERT INTO t2 VALUES (1),(2),(1),(4),(2);
+explain SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort
+1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
+set debug_dbug='d,show_explain_in_find_all_keys';
+SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a;
+# 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: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+a
+1
+2
+4
+set debug_dbug='';
+DROP TABLE t2;
+#
+# MDEV-239: Assertion `field_types == 0 ... ' failed in Protocol_text::store(double, uint32, String*) with
+# SHOW EXPLAIN over EXPLAIN EXTENDED
+#
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 VALUES (1),(2),(1),(4),(2);
+EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort
+1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a`
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ;
+show explain for $thr2;
+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 EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort
+1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a`
+set debug_dbug='';
+DROP TABLE t2;
+#
+# MDEV-240: SHOW EXPLAIN: Assertion `this->optimized == 2' failed in
+# JOIN::print_explain on query with a JOIN, TEMPTABLE view,
+#
+CREATE TABLE t3 (a INT);
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t3;
+INSERT INTO t3 VALUES (8);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (4),(5),(6),(7),(8),(9);
+explain SELECT * FROM v1, t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> system NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6
+2 DERIVED t3 system NULL NULL NULL NULL 1
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+SELECT * FROM v1, t2;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+a b
+8 4
+8 5
+8 6
+8 7
+8 8
+8 9
+set debug_dbug='';
+DROP VIEW v1;
+DROP TABLE t2, t3;
+#
+# MDEV-267: SHOW EXPLAIN: Server crashes in JOIN::print_explain on most of queries
+#
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+select sleep(1);
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select sleep(1)
+sleep(1)
+0
+set debug_dbug='';
+#
+# Same as above, but try another reason for JOIN to be degenerate
+#
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+select * from t0 where 1>10;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+a
+set debug_dbug='';
+#
+# Same as above, but try another reason for JOIN to be degenerate (2)
+#
+create table t3(a int primary key);
+insert into t3 select a from t0;
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+select * from t0,t3 where t3.a=112233;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+a a
+set debug_dbug='';
+drop table t3;
+#
+# MDEV-270: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with
+# select tables optimized away
+#
+CREATE TABLE t2 (pk INT PRIMARY KEY, a INT ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(1,4),(2,62),(3,7),(4,1),(5,0),(6,7),(7,7),(8,1),(9,7),(10,1),
+(11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5) ;
+explain SELECT * FROM t2 WHERE a =
+(SELECT MAX(a) FROM t2
+WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using where
+2 SUBQUERY t2 const PRIMARY PRIMARY 4 const 1 Using where
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_do_select';
+SELECT * FROM t2 WHERE a =
+(SELECT MAX(a) FROM t2
+WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+);
+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 20 Using where
+2 SUBQUERY t2 const PRIMARY PRIMARY 4 const 1 Using where
+3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+Warnings:
+Note 1003 SELECT * FROM t2 WHERE a =
+(SELECT MAX(a) FROM t2
+WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+)
+pk a
+3 7
+6 7
+7 7
+9 7
+set debug_dbug='';
+drop table t2;
+#
+# MDEV-273: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with impossible WHERE
+#
+CREATE TABLE t2 (a1 INT, KEY(a1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(4),(6),(7),(1),(0),(7),(7),(1),(7),(1),
+(5),(2),(0),(1),(8),(1),(1),(9),(1),(5);
+CREATE TABLE t3 (b1 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES
+(4),(5),(8),(4),(8),(2),(9),(6),(4),(8),
+(3),(5),(9),(6),(8),(3),(2),(6),(3),(1),
+(4),(3),(1),(7),(0),(0),(9),(5),(9),(0),
+(2),(2),(5),(9),(1),(4),(8),(6),(5),(5),
+(1),(7),(2),(8),(9),(3),(2),(6),(6),(5),
+(4),(3),(2),(7),(4),(6),(0),(8),(5),(8),
+(2),(9),(7),(5),(7),(0),(4),(3),(1),(0),
+(6),(2),(8),(3),(7),(3),(5),(5),(1),(2),
+(1),(7),(1),(9),(9),(8),(3);
+CREATE TABLE t4 (c1 INT) ENGINE=MyISAM;
+EXPLAIN
+SELECT count(*) FROM t2, t3
+WHERE a1 < ALL (
+SELECT a1 FROM t2
+WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 index NULL a1 5 NULL 20 Using where; Using index
+1 PRIMARY t3 ALL NULL NULL NULL NULL 87 Using join buffer (flat, BNL join)
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_do_select';
+SELECT count(*) FROM t2, t3
+WHERE a1 < ALL (
+SELECT a1 FROM t2
+WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+);
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 index NULL a1 5 NULL 20 Using where; Using index
+1 PRIMARY t3 ALL NULL NULL NULL NULL 87 Using join buffer (flat, BNL join)
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table
+Warnings:
+Note 1003 SELECT count(*) FROM t2, t3
+WHERE a1 < ALL (
+SELECT a1 FROM t2
+WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+)
+count(*)
+1740
+set debug_dbug='';
+drop table t2, t3, t4;
+#
+# MDEV-275: SHOW EXPLAIN: server crashes in JOIN::print_explain with IN subquery and aggregate function
+#
+CREATE TABLE t2 ( `pk` INT NOT NULL PRIMARY KEY, `a1` INT NOT NULL, KEY(`a1`)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(1,5),(2,4),(3,6),(4,9),(5,2),(6,8),(7,4),(8,8),(9,0),(10,43),
+(11,23),(12,3),(13,45),(14,16),(15,2),(16,33),(17,2),(18,5),(19,9),(20,2);
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+SELECT * FROM t2 WHERE (5, 78) IN (SELECT `a1`, MAX(`a1`) FROM t2 GROUP BY `a1`);
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+pk a1
+set debug_dbug='';
+DROP TABLE t2;
+DROP TABLE t1;
+#
+# MDEV-305: SHOW EXPLAIN: ref returned by SHOW EXPLAIN is different from the normal EXPLAIN ('const' vs empty string)
+#
+CREATE TABLE t1(a INT, KEY(a));
+INSERT INTO t1 VALUES (3),(1),(5),(1);
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+SELECT 'test' FROM t1 WHERE a=1;
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref a a 5 const 1 Using index
+Warnings:
+Note 1003 SELECT 'test' FROM t1 WHERE a=1
+test
+test
+test
+set debug_dbug='';
+DROP TABLE t1;
+#
+# MDEV-299: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN changes back and forth during query execution
+#
+create table t1 (key1 int, col1 int, col2 int, filler char(100), key(key1));
+insert into t1 select A.a+ 10 * B.a, 10, 10, 'filler-data' from t0 A, t0 B;
+update t1 set col1=3, col2=10 where key1=1;
+update t1 set col1=3, col2=1000 where key1=2;
+update t1 set col1=3, col2=10 where key1=3;
+update t1 set col1=3, col2=1000 where key1=4;
+set @tmp_mdev299_jcl= @@join_cache_level;
+set join_cache_level=0;
+explain select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where
+1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1)
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_test_if_quick_select';
+select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100;
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where
+1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where
+1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where
+1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where
+1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1)
+Warnings:
+Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100
+count(*)
+212
+set debug_dbug='';
+drop table t1;
+#
+# MDEV-297: SHOW EXPLAIN: Server gets stuck until timeout occurs while
+# executing SHOW INDEX and SHOW EXPLAIN in parallel
+#
+CREATE TABLE t1(a INT, b INT, c INT, KEY(a), KEY(b), KEY(c));
+INSERT INTO t1 (a) VALUES (3),(1),(5),(1);
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+SHOW INDEX FROM t1;
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE STATISTICS ALL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases
+Warnings:
+Note 1003 SHOW INDEX FROM t1
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
+t1 1 a 1 a A NULL NULL NULL YES BTREE
+t1 1 b 1 b A NULL NULL NULL YES BTREE
+t1 1 c 1 c A NULL NULL NULL YES BTREE
+set debug_dbug='';
+DROP TABLE t1;
+#
+# MDEV-324: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN for a query with TEMPTABLE view
+# loses 'DERIVED' line on the way without saying that the plan was already deleted
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN SELECT a + 1 FROM v1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
+2 DERIVED t1 ALL NULL NULL NULL NULL 2
+set debug_dbug='d,show_explain_probe_join_tab_preread';
+set @show_explain_probe_select_id=1;
+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
+Warnings:
+Note 1003 SELECT a + 1 FROM v1
+a + 1
+2
+3
+set debug_dbug='';
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# MDEV-323: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN loses
+# 'UNION RESULT' line on the way without saying that the plan was already deleted
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (4),(6);
+EXPLAIN
+SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+set debug_dbug='d,show_explain_probe_union_read';
+SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 );
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 )
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
+3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL
+Warnings:
+Note 1003 SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 )
+a
+set debug_dbug='';
+DROP TABLE t1;
+#
+# MDEV-327: SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN
+# and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY'
+#
+CREATE TABLE t1 (a INT) ENGINE=Aria;
+INSERT INTO t1 VALUES
+(4),(6),(3),(5),(3),(246),(2),(9),(3),(8),
+(1),(8),(8),(5),(7),(5),(1),(6),(2),(9);
+CREATE TABLE t2 (b INT) ENGINE=Aria;
+INSERT INTO t2 VALUES
+(1),(7),(4),(7),(0),(2),(9),(4),(0),(9),
+(1),(3),(8),(8),(18),(84),(6),(3),(6),(6);
+EXPLAIN
+SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 20
+3 SUBQUERY t1 ALL NULL NULL NULL NULL 20
+3 SUBQUERY t2 ALL NULL NULL NULL NULL 20 Using where
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 20
+3 SUBQUERY t1 ALL NULL NULL NULL NULL 20
+3 SUBQUERY t2 ALL NULL NULL NULL NULL 20 Using where
+Warnings:
+Note 1003 SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b )
+a b
+set debug_dbug='';
+DROP TABLE t1, t2;
+#
+# Test that SHOW EXPLAIN will print 'Distinct'.
+#
+CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned);
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1);
+CREATE TABLE t3 (a int(10) unsigned, key(A), b text);
+INSERT INTO t3 VALUES (1,'1'),(2,'2');
+create temporary table t4 select * from t3;
+insert into t3 select * from t4;
+insert into t4 select * from t3;
+insert into t3 select * from t4;
+insert into t4 select * from t3;
+insert into t3 select * from t4;
+insert into t4 select * from t3;
+insert into t3 select * from t4;
+explain select distinct t1.a from t1,t3 where t1.a=t3.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary
+1 SIMPLE t3 ref a a 5 test.t1.a 7 Using index; Distinct
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select distinct t1.a from t1,t3 where t1.a=t3.a;
+show explain for $thr2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary
+1 SIMPLE t3 ref a a 5 test.t1.a 7 Using index; Distinct
+Warnings:
+Note 1003 select distinct t1.a from t1,t3 where t1.a=t3.a
+a
+1
+2
+set debug_dbug='';
+drop table t1,t3,t4;
+#
+# ---------- SHOW EXPLAIN and permissions -----------------
+#
+grant ALL on test.* to test2@localhost;
+#
+# First, make sure that user 'test2' cannot do SHOW EXPLAIN on us
+#
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select * from t0 where a < 3;
+show explain for $thr2;
+ERROR 42000: Access denied; you need (at least one of) the PROCESSLIST privilege(s) for this operation
+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 10 Using where
+Warnings:
+Note 1003 select * from t0 where a < 3
+a
+0
+1
+2
+set debug_dbug='';
+#
+# Unfortunately, our test setup doesn't allow to check that test2
+# can do SHOW EXPLAIN on his own queries. This is because SET debug_dbug
+# requires SUPER privilege. Giving SUPER to test2 will make the test
+# meaningless
+#
+#
+# Now, grant test2 a PROCESSLIST permission, and see that he's able to observe us
+#
+grant process on *.* to test2@localhost;
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select * from t0 where a < 3;
+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 10 Using where
+Warnings:
+Note 1003 select * from t0 where a < 3
+a
+0
+1
+2
+set debug_dbug='';
+revoke all privileges on test.* from test2@localhost;
+drop user test2@localhost;
+#
+# Test that it is possible to KILL a SHOW EXPLAIN command that's waiting
+# on its target thread
+#
+create table t1 (pk int primary key, data char(64)) engine=innodb;
+insert into t1 select A.a + 10 * B.a + 100 * C.a, 'data1' from t0 A, t0 B, t0 C;
+# Lock two threads
+set autocommit=0;
+select * from t1 where pk between 10 and 20 for update;
+pk data
+10 data1
+11 data1
+12 data1
+13 data1
+14 data1
+15 data1
+16 data1
+17 data1
+18 data1
+19 data1
+20 data1
+set autocommit=0;
+select * from t1 where pk between 10 and 20 for update;
+# do: send_eval show explain for 3;
+kill query $thr_default;
+ERROR 70100: Query execution was interrupted
+rollback;
+pk data
+10 data1
+11 data1
+12 data1
+13 data1
+14 data1
+15 data1
+16 data1
+17 data1
+18 data1
+19 data1
+20 data1
+drop table t1;
+drop table t0;
+#
+# Check that the I_S table is invisible
+#
+select table_name from information_schema.tables where table_schema='information_schema' and table_name like '%explain%';
+table_name
=== added file 'mysql-test/r/show_explain_ps.result'
--- mysql-test/r/show_explain_ps.result 1970-01-01 00:00:00 +0000
+++ mysql-test/r/show_explain_ps.result 2012-07-07 04:47:41 +0000
@@ -0,0 +1,27 @@
+drop table if exists t0, t1;
+select * from performance_schema.setup_instruments where name like '%show_explain%';
+NAME ENABLED TIMED
+wait/synch/cond/sql/show_explain YES YES
+# We've got no instances
+select * from performance_schema.cond_instances where name like '%show_explain%';
+NAME OBJECT_INSTANCE_BEGIN
+# Check out if our cond is hit.
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select count(*) from t0 where a < 100000;
+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 10 Using where
+Warnings:
+Note 1003 select count(*) from t0 where a < 100000
+count(*)
+10
+set debug_dbug='';
+select event_name
+from performance_schema.events_waits_history_long
+where event_name='wait/synch/cond/sql/show_explain';
+event_name
+wait/synch/cond/sql/show_explain
+drop table t0;
=== added file 'mysql-test/t/show_explain.test'
--- mysql-test/t/show_explain.test 1970-01-01 00:00:00 +0000
+++ mysql-test/t/show_explain.test 2012-07-10 17:23:00 +0000
@@ -0,0 +1,855 @@
+#
+# Tests for SHOW EXPLAIN FOR functionality
+#
+--source include/have_debug.inc
+--source include/have_innodb.inc
+
+--disable_warnings
+drop table if exists t0, t1, t2, t3, t4;
+drop view if exists v1;
+--enable_warnings
+
+#
+# Testcases in this file do not work with embedded server. The reason for this
+# is that we use the following commands for synchronization:
+#
+# set @show_explain_probe_select_id=1;
+# set debug_dbug='d,show_explain_probe_join_exec_start';
+# send select count(*) from t1 where a < 100000;
+#
+# When ran with mysqltest_embedded, this translates into:
+#
+# Thread1> DBUG_PUSH("d,show_explain_probe_join_exec_start");
+# Thread1> create another thread for doing "send ... reap"
+# Thread2> mysql_parse("select count(*) from t1 where a < 100000");
+#
+# That is, "select count(*) ..." is ran in a thread for which DBUG_PUSH(...)
+# has not been called. As a result, show_explain_probe_join_exec_start does not fire, and
+# "select count(*) ..." does not wait till its SHOW EXPLAIN command, and the
+# test fails.
+#
+-- source include/not_embedded.inc
+
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int);
+insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C;
+alter table t1 add b int, add c int, add filler char(32);
+update t1 set b=a, c=a, filler='fooo';
+alter table t1 add key(a), add key(b);
+
+#
+# Try killing a non-existent thread
+#
+--error ER_NO_SUCH_THREAD
+show explain for 2*1000*1000*1000;
+
+--error ER_NOT_SUPPORTED_YET
+show explain for (select max(a) from t0);
+
+#
+# Setup two threads and their ids
+#
+let $thr1=`select connection_id()`;
+connect (con1, localhost, root,,);
+connection con1;
+let $thr2=`select connection_id()`;
+connection default;
+
+# SHOW EXPLAIN FOR <idle thread>
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+
+# SHOW EXPLAIN FOR <ourselves>
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr1;
+
+let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2;
+
+#
+# Test SHOW EXPLAIN for simple queries
+#
+connection con1;
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send select count(*) from t1 where a < 100000;
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+send select max(c) from t1 where a < 10;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+--echo # We can catch EXPLAIN, too.
+set @show_expl_tmp= @@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on';
+send explain select max(c) from t1 where a < 10;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set optimizer_switch= @show_expl_tmp;
+
+
+--echo # UNION, first branch
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send explain select a from t0 A union select a+1 from t0 B;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+--echo # UNION, second branch
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send explain select a from t0 A union select a+1 from t0 B;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+--echo # Uncorrelated subquery, select
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send select a, (select max(a) from t0 B) from t0 A where a<1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+--echo # Uncorrelated subquery, explain
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send explain select a, (select max(a) from t0 B) from t0 A where a<1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo # correlated subquery, select
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+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
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo # correlated subquery, explain
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+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
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo # correlated subquery, select, while inside the subquery
+set @show_explain_probe_select_id=2; # <---
+set debug_dbug='d,show_explain_probe_join_exec_start';
+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
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo # correlated subquery, explain, while inside the subquery
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+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
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+--echo # correlated subquery, explain, while inside the subquery
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+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_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+# TODO: explain in the parent subuqery when the un-correlated child has been
+# run (and have done irreversible cleanups)
+# ^^ Is this at all possible after 5.3?
+# Maybe, for 5.3 try this:
+# - run before/after the parent has invoked child's optimization
+# - run after materialization
+
+--echo # Try to do SHOW EXPLAIN for a query that runs a SET command:
+--echo # I've found experimentally that select_id==2 here...
+--echo #
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send set @foo= (select max(a) from t0 where sin(a) >0);
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo #
+--echo # Attempt SHOW EXPLAIN for an UPDATE
+--echo #
+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';
+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_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+drop table t2;
+
+--echo #
+--echo # Attempt SHOW EXPLAIN for a DELETE
+--echo #
+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';
+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_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+drop table t2;
+
+
+--echo #
+--echo # Multiple SHOW EXPLAIN calls for one select
+--echo #
+create table t2 as select a as a, a as dummy from t0 limit 3;
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+evalp show explain for $thr2;
+evalp show explain for $thr2;
+connection con1;
+reap;
+drop table t2;
+
+--echo #
+--echo # SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort"
+--echo #
+explain select * from t0 order by a;
+
+set debug_dbug='d,show_explain_probe_join_exec_start';
+set @show_explain_probe_select_id=1;
+send select * from t0 order by a;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo #
+--echo # SHOW EXPLAIN for SELECT ... with "Using temporary"
+--echo #
+connection default;
+explain select distinct a from t0;
+connection con1;
+
+set debug_dbug='d,show_explain_probe_join_exec_start';
+set @show_explain_probe_select_id=1;
+send select distinct a from t0;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo #
+--echo # SHOW EXPLAIN for SELECT ... with "Using temporary; Using filesort"
+--echo #
+connection default;
+explain select distinct a from t0;
+connection con1;
+
+set debug_dbug='d,show_explain_probe_join_exec_start';
+set @show_explain_probe_select_id=1;
+send select distinct a from t0;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+--echo #
+--echo # MDEV-238: SHOW EXPLAIN: Server crashes in JOIN::print_explain with FROM subquery and GROUP BY
+--echo #
+CREATE TABLE t2 ( a INT );
+INSERT INTO t2 VALUES (1),(2),(1),(4),(2);
+explain SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a;
+
+set debug_dbug='d,show_explain_in_find_all_keys';
+send SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a;
+
+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_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+DROP TABLE t2;
+
+
+--echo #
+--echo # MDEV-239: Assertion `field_types == 0 ... ' failed in Protocol_text::store(double, uint32, String*) with
+--echo # SHOW EXPLAIN over EXPLAIN EXTENDED
+--echo #
+
+
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 VALUES (1),(2),(1),(4),(2);
+
+EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ;
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+send EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ;
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+DROP TABLE t2;
+
+
+--echo #
+--echo # MDEV-240: SHOW EXPLAIN: Assertion `this->optimized == 2' failed in
+--echo # JOIN::print_explain on query with a JOIN, TEMPTABLE view,
+--echo #
+CREATE TABLE t3 (a INT);
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t3;
+INSERT INTO t3 VALUES (8);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (4),(5),(6),(7),(8),(9);
+explain SELECT * FROM v1, t2;
+
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+send SELECT * FROM v1, t2;
+
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+DROP VIEW v1;
+DROP TABLE t2, t3;
+
+--echo #
+--echo # MDEV-267: SHOW EXPLAIN: Server crashes in JOIN::print_explain on most of queries
+--echo #
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+send select sleep(1);
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+
+--echo #
+--echo # Same as above, but try another reason for JOIN to be degenerate
+--echo #
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+send select * from t0 where 1>10;
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+--echo #
+--echo # Same as above, but try another reason for JOIN to be degenerate (2)
+--echo #
+create table t3(a int primary key);
+insert into t3 select a from t0;
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+send select * from t0,t3 where t3.a=112233;
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+drop table t3;
+
+--echo #
+--echo # MDEV-270: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with
+--echo # select tables optimized away
+--echo #
+
+CREATE TABLE t2 (pk INT PRIMARY KEY, a INT ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+ (1,4),(2,62),(3,7),(4,1),(5,0),(6,7),(7,7),(8,1),(9,7),(10,1),
+ (11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5) ;
+
+explain SELECT * FROM t2 WHERE a =
+ (SELECT MAX(a) FROM t2
+ WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+ );
+
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_do_select';
+send SELECT * FROM t2 WHERE a =
+ (SELECT MAX(a) FROM t2
+ WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+ );
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+drop table t2;
+
+
+--echo #
+--echo # MDEV-273: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with impossible WHERE
+--echo #
+CREATE TABLE t2 (a1 INT, KEY(a1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+ (4),(6),(7),(1),(0),(7),(7),(1),(7),(1),
+ (5),(2),(0),(1),(8),(1),(1),(9),(1),(5);
+
+CREATE TABLE t3 (b1 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES
+ (4),(5),(8),(4),(8),(2),(9),(6),(4),(8),
+ (3),(5),(9),(6),(8),(3),(2),(6),(3),(1),
+ (4),(3),(1),(7),(0),(0),(9),(5),(9),(0),
+ (2),(2),(5),(9),(1),(4),(8),(6),(5),(5),
+ (1),(7),(2),(8),(9),(3),(2),(6),(6),(5),
+ (4),(3),(2),(7),(4),(6),(0),(8),(5),(8),
+ (2),(9),(7),(5),(7),(0),(4),(3),(1),(0),
+ (6),(2),(8),(3),(7),(3),(5),(5),(1),(2),
+ (1),(7),(1),(9),(9),(8),(3);
+CREATE TABLE t4 (c1 INT) ENGINE=MyISAM;
+
+EXPLAIN
+SELECT count(*) FROM t2, t3
+WHERE a1 < ALL (
+ SELECT a1 FROM t2
+ WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+);
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_do_select';
+send
+SELECT count(*) FROM t2, t3
+WHERE a1 < ALL (
+ SELECT a1 FROM t2
+ WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+);
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+drop table t2, t3, t4;
+
+--echo #
+--echo # MDEV-275: SHOW EXPLAIN: server crashes in JOIN::print_explain with IN subquery and aggregate function
+--echo #
+CREATE TABLE t2 ( `pk` INT NOT NULL PRIMARY KEY, `a1` INT NOT NULL, KEY(`a1`)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+ (1,5),(2,4),(3,6),(4,9),(5,2),(6,8),(7,4),(8,8),(9,0),(10,43),
+ (11,23),(12,3),(13,45),(14,16),(15,2),(16,33),(17,2),(18,5),(19,9),(20,2);
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+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_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+DROP TABLE t2;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-305: SHOW EXPLAIN: ref returned by SHOW EXPLAIN is different from the normal EXPLAIN ('const' vs empty string)
+--echo #
+CREATE TABLE t1(a INT, KEY(a));
+INSERT INTO t1 VALUES (3),(1),(5),(1);
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+
+send SELECT 'test' FROM t1 WHERE a=1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-299: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN changes back and forth during query execution
+--echo #
+
+create table t1 (key1 int, col1 int, col2 int, filler char(100), key(key1));
+insert into t1 select A.a+ 10 * B.a, 10, 10, 'filler-data' from t0 A, t0 B;
+
+# Make matches 3 records
+update t1 set col1=3, col2=10 where key1=1; # small range
+update t1 set col1=3, col2=1000 where key1=2; # big range
+update t1 set col1=3, col2=10 where key1=3; # small range again
+update t1 set col1=3, col2=1000 where key1=4; # big range
+
+set @tmp_mdev299_jcl= @@join_cache_level;
+set join_cache_level=0;
+
+explain select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100;
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_test_if_quick_select';
+
+send
+select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100;
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+set debug_dbug='';
+drop table t1;
+
+--echo #
+--echo # MDEV-297: SHOW EXPLAIN: Server gets stuck until timeout occurs while
+--echo # executing SHOW INDEX and SHOW EXPLAIN in parallel
+--echo #
+CREATE TABLE t1(a INT, b INT, c INT, KEY(a), KEY(b), KEY(c));
+INSERT INTO t1 (a) VALUES (3),(1),(5),(1);
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+
+send SHOW INDEX FROM t1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-324: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN for a query with TEMPTABLE view
+--echo # loses 'DERIVED' line on the way without saying that the plan was already deleted
+--echo #
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+
+EXPLAIN SELECT a + 1 FROM v1;
+
+set debug_dbug='d,show_explain_probe_join_tab_preread';
+set @show_explain_probe_select_id=1;
+
+send
+ SELECT a + 1 FROM v1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-323: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN loses
+--echo # 'UNION RESULT' line on the way without saying that the plan was already deleted
+--echo #
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (4),(6);
+
+EXPLAIN
+SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 );
+
+set debug_dbug='d,show_explain_probe_union_read';
+send
+SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 );
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+
+connection con1;
+reap;
+
+set debug_dbug='';
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-327: SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN
+--echo # and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY'
+--echo #
+CREATE TABLE t1 (a INT) ENGINE=Aria;
+INSERT INTO t1 VALUES
+(4),(6),(3),(5),(3),(246),(2),(9),(3),(8),
+(1),(8),(8),(5),(7),(5),(1),(6),(2),(9);
+
+CREATE TABLE t2 (b INT) ENGINE=Aria;
+INSERT INTO t2 VALUES
+(1),(7),(4),(7),(0),(2),(9),(4),(0),(9),
+(1),(3),(8),(8),(18),(84),(6),(3),(6),(6);
+
+EXPLAIN
+SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+--send
+SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+
+connection con1;
+reap;
+set debug_dbug='';
+DROP TABLE t1, t2;
+
+--echo #
+--echo # Test that SHOW EXPLAIN will print 'Distinct'.
+--echo #
+CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned);
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1);
+
+CREATE TABLE t3 (a int(10) unsigned, key(A), b text);
+INSERT INTO t3 VALUES (1,'1'),(2,'2');
+
+create temporary table t4 select * from t3;
+insert into t3 select * from t4;
+insert into t4 select * from t3;
+insert into t3 select * from t4;
+insert into t4 select * from t3;
+insert into t3 select * from t4;
+insert into t4 select * from t3;
+insert into t3 select * from t4;
+explain select distinct t1.a from t1,t3 where t1.a=t3.a;
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+--send
+select distinct t1.a from t1,t3 where t1.a=t3.a;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+
+connection con1;
+reap;
+set debug_dbug='';
+
+drop table t1,t3,t4;
+
+--echo #
+--echo # ---------- SHOW EXPLAIN and permissions -----------------
+--echo #
+grant ALL on test.* to test2@localhost;
+
+connect (con2, localhost, test2,,);
+connection con1;
+
+--echo #
+--echo # First, make sure that user 'test2' cannot do SHOW EXPLAIN on us
+--echo #
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send
+select * from t0 where a < 3;
+
+connection default;
+--source include/wait_condition.inc
+
+connection con2;
+--error ER_SPECIFIC_ACCESS_DENIED_ERROR
+evalp show explain for $thr2;
+
+connection default;
+evalp show explain for $thr2;
+
+connection con1;
+reap;
+set debug_dbug='';
+
+--echo #
+--echo # Unfortunately, our test setup doesn't allow to check that test2
+--echo # can do SHOW EXPLAIN on his own queries. This is because SET debug_dbug
+--echo # requires SUPER privilege. Giving SUPER to test2 will make the test
+--echo # meaningless
+--echo #
+
+--echo #
+--echo # Now, grant test2 a PROCESSLIST permission, and see that he's able to observe us
+--echo #
+disconnect con2;
+grant process on *.* to test2@localhost;
+connect (con2, localhost, test2,,);
+connection con1;
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send
+select * from t0 where a < 3;
+
+connection default;
+--source include/wait_condition.inc
+
+connection con2;
+evalp show explain for $thr2;
+
+connection con1;
+reap;
+set debug_dbug='';
+
+revoke all privileges on test.* from test2@localhost;
+drop user test2@localhost;
+
+disconnect con2;
+--echo #
+--echo # Test that it is possible to KILL a SHOW EXPLAIN command that's waiting
+--echo # on its target thread
+--echo #
+connect (con2, localhost, root,,);
+connect (con3, localhost, root,,);
+connection con2;
+create table t1 (pk int primary key, data char(64)) engine=innodb;
+insert into t1 select A.a + 10 * B.a + 100 * C.a, 'data1' from t0 A, t0 B, t0 C;
+
+--echo # Lock two threads
+set autocommit=0;
+select * from t1 where pk between 10 and 20 for update;
+
+connection con1;
+set autocommit=0;
+# This will freeze
+send
+select * from t1 where pk between 10 and 20 for update;
+
+# run SHOW EXPLAIN on a frozen thread
+connection default;
+let $wait_condition= select State='Sending data' from information_schema.processlist where id=$thr2;
+let $thr_default=`select connection_id()`;
+--source include/wait_condition.inc
+--echo # do: send_eval show explain for $thr2;
+--disable_query_log
+send_eval show explain for $thr2;
+--enable_query_log
+
+# kill the SHOW EXPLAIN command
+connection con3;
+let $wait_condition= select State='show_explain' from information_schema.processlist where id=$thr_default;
+--source include/wait_condition.inc
+evalp kill query $thr_default;
+
+connection default;
+--error ER_QUERY_INTERRUPTED
+reap;
+
+connection con2;
+rollback;
+
+connection con1;
+reap;
+
+drop table t1;
+disconnect con3;
+disconnect con2;
+
+## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
+## thread and served together.
+
+drop table t0;
+
+--echo #
+--echo # Check that the I_S table is invisible
+--echo #
+select table_name from information_schema.tables where table_schema='information_schema' and table_name like '%explain%';
+
=== added file 'mysql-test/t/show_explain_ps.test'
--- mysql-test/t/show_explain_ps.test 1970-01-01 00:00:00 +0000
+++ mysql-test/t/show_explain_ps.test 2012-07-07 04:47:41 +0000
@@ -0,0 +1,48 @@
+#
+# Test how SHOW EXPLAIN is represented in performance schema
+#
+--source include/have_perfschema.inc
+
+--disable_warnings
+drop table if exists t0, t1;
+--enable_warnings
+
+select * from performance_schema.setup_instruments where name like '%show_explain%';
+
+--echo # We've got no instances
+select * from performance_schema.cond_instances where name like '%show_explain%';
+
+--echo # Check out if our cond is hit.
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+let $thr1=`select connection_id()`;
+connect (con1, localhost, root,,);
+connection con1;
+let $thr2=`select connection_id()`;
+connection default;
+
+let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2;
+
+#
+# Test SHOW EXPLAIN for simple queries
+#
+connection con1;
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send select count(*) from t0 where a < 100000;
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+set debug_dbug='';
+
+select event_name
+from performance_schema.events_waits_history_long
+where event_name='wait/synch/cond/sql/show_explain';
+
+drop table t0;
=== modified file 'sql/CMakeLists.txt'
--- sql/CMakeLists.txt 2012-05-22 09:04:32 +0000
+++ sql/CMakeLists.txt 2012-06-14 20:50:17 +0000
@@ -85,6 +85,7 @@ SET (SQL_SOURCE
gcalc_slicescan.cc gcalc_tools.cc
threadpool_common.cc
../sql-common/mysql_async.c
+ my_apc.cc my_apc.h
${GEN_SOURCES}
${MYSYS_LIBWRAP_SOURCE}
)
=== modified file 'sql/filesort.cc'
--- sql/filesort.cc 2012-05-21 13:30:25 +0000
+++ sql/filesort.cc 2012-06-25 14:39:26 +0000
@@ -502,7 +502,6 @@ static ha_rows find_all_keys(SORTPARAM *
my_off_t record;
TABLE *sort_form;
THD *thd= current_thd;
- volatile killed_state *killed= &thd->killed;
handler *file;
MY_BITMAP *save_read_set, *save_write_set, *save_vcol_set;
uchar *next_sort_key= sort_keys_buf;
@@ -523,6 +522,11 @@ static ha_rows find_all_keys(SORTPARAM *
if (flag)
ref_pos= &file->ref[0];
next_pos=ref_pos;
+
+ DBUG_EXECUTE_IF("show_explain_in_find_all_keys",
+ dbug_serve_apcs(thd, 1);
+ );
+
if (!quick_select)
{
next_pos=(uchar*) 0; /* Find records in sequence */
@@ -586,7 +590,7 @@ static ha_rows find_all_keys(SORTPARAM *
break;
}
- if (*killed)
+ if (thd->check_killed())
{
DBUG_PRINT("info",("Sort killed by user"));
if (!quick_select)
@@ -1231,18 +1235,13 @@ int merge_buffers(SORTPARAM *param, IO_C
void *first_cmp_arg;
element_count dupl_count= 0;
uchar *src;
- killed_state not_killable;
uchar *unique_buff= param->unique_buff;
- volatile killed_state *killed= ¤t_thd->killed;
+ const bool killable= !param->not_killable;
+ THD* const thd=current_thd;
DBUG_ENTER("merge_buffers");
- status_var_increment(current_thd->status_var.filesort_merge_passes);
- current_thd->query_plan_fsort_passes++;
- if (param->not_killable)
- {
- killed= ¬_killable;
- not_killable= NOT_KILLED;
- }
+ status_var_increment(thd->status_var.filesort_merge_passes);
+ thd->query_plan_fsort_passes++;
error=0;
rec_length= param->rec_length;
@@ -1320,7 +1319,7 @@ int merge_buffers(SORTPARAM *param, IO_C
while (queue.elements > 1)
{
- if (*killed)
+ if (killable && thd->check_killed())
{
error= 1; goto err; /* purecov: inspected */
}
=== modified file 'sql/handler.h'
--- sql/handler.h 2012-05-21 13:30:25 +0000
+++ sql/handler.h 2012-07-10 17:23:00 +0000
@@ -600,6 +600,7 @@ enum enum_schema_tables
SCH_COLUMN_PRIVILEGES,
SCH_ENGINES,
SCH_EVENTS,
+ SCH_EXPLAIN,
SCH_FILES,
SCH_GLOBAL_STATUS,
SCH_GLOBAL_VARIABLES,
=== modified file 'sql/item_func.cc'
--- sql/item_func.cc 2012-03-26 10:33:49 +0000
+++ sql/item_func.cc 2012-05-16 20:59:03 +0000
@@ -4298,7 +4298,7 @@ longlong Item_func_sleep::val_int()
#define extra_size sizeof(double)
-static user_var_entry *get_variable(HASH *hash, LEX_STRING &name,
+user_var_entry *get_variable(HASH *hash, LEX_STRING &name,
bool create_if_not_exists)
{
user_var_entry *entry;
=== modified file 'sql/item_subselect.cc'
--- sql/item_subselect.cc 2012-06-04 15:26:11 +0000
+++ sql/item_subselect.cc 2012-06-19 09:53:16 +0000
@@ -1813,7 +1813,7 @@ bool Item_allany_subselect::is_maxmin_ap
WHERE condition.
*/
return (abort_on_null || (upper_item && upper_item->is_top_level_item())) &&
- !join->select_lex->master_unit()->uncacheable && !func->eqne_op();
+ !(join->select_lex->master_unit()->uncacheable & ~UNCACHEABLE_EXPLAIN) && !func->eqne_op();
}
=== added file 'sql/my_apc.cc'
--- sql/my_apc.cc 1970-01-01 00:00:00 +0000
+++ sql/my_apc.cc 2012-07-10 17:23:00 +0000
@@ -0,0 +1,302 @@
+/*
+ Copyright (c) 2011 - 2012, Monty Program Ab
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
+
+
+#ifndef MY_APC_STANDALONE
+
+#include "sql_priv.h"
+#include "sql_class.h"
+
+#endif
+
+/* For standalone testing of APC system, see unittest/sql/my_apc-t.cc */
+
+#ifndef MY_APC_STANDALONE
+
+ST_FIELD_INFO show_explain_fields_info[]=
+{
+ /* field_name, length, type, value, field_flags, old_name*/
+ {"id", 3, MYSQL_TYPE_LONGLONG, 0 /*value*/, MY_I_S_MAYBE_NULL, "id",
+ SKIP_OPEN_TABLE},
+ {"select_type", 19, MYSQL_TYPE_STRING, 0 /*value*/, 0, "select_type",
+ SKIP_OPEN_TABLE},
+ {"table", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0 /*value*/, MY_I_S_MAYBE_NULL,
+ "table", SKIP_OPEN_TABLE},
+ {"type", 10, MYSQL_TYPE_STRING, 0, MY_I_S_MAYBE_NULL, "type", SKIP_OPEN_TABLE},
+ {"possible_keys", NAME_CHAR_LEN*MAX_KEY, MYSQL_TYPE_STRING, 0/*value*/,
+ MY_I_S_MAYBE_NULL, "possible_keys", SKIP_OPEN_TABLE},
+ {"key", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0/*value*/, MY_I_S_MAYBE_NULL,
+ "key", SKIP_OPEN_TABLE},
+ {"key_len", NAME_CHAR_LEN*MAX_KEY, MYSQL_TYPE_STRING, 0/*value*/,
+ MY_I_S_MAYBE_NULL, "key_len", SKIP_OPEN_TABLE},
+ {"ref", NAME_CHAR_LEN*MAX_REF_PARTS, MYSQL_TYPE_STRING, 0/*value*/,
+ MY_I_S_MAYBE_NULL, "ref", SKIP_OPEN_TABLE},
+ {"rows", 10, MYSQL_TYPE_LONGLONG, 0/*value*/, MY_I_S_MAYBE_NULL, "rows",
+ SKIP_OPEN_TABLE},
+ {"Extra", 255, MYSQL_TYPE_STRING, 0/*value*/, 0 /*flags*/, "Extra",
+ SKIP_OPEN_TABLE},
+ {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
+};
+
+
+#endif
+/*
+ Initialize the target.
+
+ @note
+ Initialization must be done prior to enabling/disabling the target, or making
+ any call requests to it.
+ Initial state after initialization is 'disabled'.
+*/
+void Apc_target::init(mysql_mutex_t *target_mutex)
+{
+ DBUG_ASSERT(!enabled);
+ LOCK_thd_data_ptr= target_mutex;
+#ifndef DBUG_OFF
+ n_calls_processed= 0;
+#endif
+}
+
+
+/*
+ Destroy the target. The target must be disabled when this call is made.
+*/
+void Apc_target::destroy()
+{
+ DBUG_ASSERT(!enabled);
+}
+
+
+/*
+ Enter ther state where the target is available for serving APC requests
+*/
+void Apc_target::enable()
+{
+ /* Ok to do without getting/releasing the mutex: */
+ enabled++;
+}
+
+
+/*
+ Make the target unavailable for serving APC requests.
+
+ @note
+ This call will serve all requests that were already enqueued
+*/
+
+void Apc_target::disable()
+{
+ bool process= FALSE;
+ mysql_mutex_lock(LOCK_thd_data_ptr);
+ if (!(--enabled))
+ process= TRUE;
+ mysql_mutex_unlock(LOCK_thd_data_ptr);
+ if (process)
+ process_apc_requests();
+}
+
+
+/* [internal] Put request qe into the request list */
+
+void Apc_target::enqueue_request(Call_request *qe)
+{
+ mysql_mutex_assert_owner(LOCK_thd_data_ptr);
+ if (apc_calls)
+ {
+ Call_request *after= apc_calls->prev;
+ qe->next= apc_calls;
+ apc_calls->prev= qe;
+
+ qe->prev= after;
+ after->next= qe;
+ }
+ else
+ {
+ apc_calls= qe;
+ qe->next= qe->prev= qe;
+ }
+}
+
+
+/*
+ [internal] Remove request qe from the request queue.
+
+ The request is not necessarily first in the queue.
+*/
+
+void Apc_target::dequeue_request(Call_request *qe)
+{
+ mysql_mutex_assert_owner(LOCK_thd_data_ptr);
+ if (apc_calls == qe)
+ {
+ if ((apc_calls= apc_calls->next) == qe)
+ {
+ apc_calls= NULL;
+ }
+ }
+
+ qe->prev->next= qe->next;
+ qe->next->prev= qe->prev;
+}
+
+#ifdef HAVE_PSI_INTERFACE
+
+/* One key for all conds */
+PSI_cond_key key_show_explain_request_COND;
+
+static PSI_cond_info show_explain_psi_conds[]=
+{
+ { &key_show_explain_request_COND, "show_explain", 0 /* not using PSI_FLAG_GLOBAL*/ }
+};
+
+void init_show_explain_psi_keys(void)
+{
+ if (PSI_server == NULL)
+ return;
+
+ PSI_server->register_cond("sql", show_explain_psi_conds,
+ array_elements(show_explain_psi_conds));
+}
+#endif
+
+
+/*
+ Make an APC (Async Procedure Call) to another thread.
+
+ @detail
+ Make an APC call: schedule it for execution and wait until the target
+ thread has executed it.
+
+ - The caller is responsible for making sure he's not posting request
+ to the thread he's calling this function from.
+
+ - The caller must have locked target_mutex. The function will release it.
+
+ @retval FALSE - Ok, the call has been made
+ @retval TRUE - Call wasnt made (either the target is in disabled state or
+ timeout occured)
+*/
+
+bool Apc_target::make_apc_call(THD *caller_thd, Apc_call *call,
+ int timeout_sec, bool *timed_out)
+{
+ bool res= TRUE;
+ *timed_out= FALSE;
+
+ if (enabled)
+ {
+ /* Create and post the request */
+ Call_request apc_request;
+ apc_request.call= call;
+ apc_request.processed= FALSE;
+ mysql_cond_init(key_show_explain_request_COND, &apc_request.COND_request,
+ NULL);
+ enqueue_request(&apc_request);
+ apc_request.what="enqueued by make_apc_call";
+
+ struct timespec abstime;
+ const int timeout= timeout_sec;
+ set_timespec(abstime, timeout);
+
+ int wait_res= 0;
+ const char *old_msg;
+ old_msg= caller_thd->enter_cond(&apc_request.COND_request,
+ LOCK_thd_data_ptr, "show_explain");
+ /* todo: how about processing other errors here? */
+ while (!apc_request.processed && (wait_res != ETIMEDOUT))
+ {
+ /* We own LOCK_thd_data_ptr */
+ wait_res= mysql_cond_timedwait(&apc_request.COND_request,
+ LOCK_thd_data_ptr, &abstime);
+ // &apc_request.LOCK_request, &abstime);
+ if (caller_thd->killed)
+ break;
+ }
+
+ if (!apc_request.processed)
+ {
+ /*
+ The wait has timed out, or this thread was KILLed.
+ Remove the request from the queue (ok to do because we own
+ LOCK_thd_data_ptr)
+ */
+ apc_request.processed= TRUE;
+ dequeue_request(&apc_request);
+ *timed_out= TRUE;
+ res= TRUE;
+ }
+ else
+ {
+ /* Request was successfully executed and dequeued by the target thread */
+ res= FALSE;
+ }
+ /*
+ exit_cond() will call mysql_mutex_unlock(LOCK_thd_data_ptr) for us:
+ */
+ caller_thd->exit_cond(old_msg);
+
+ /* Destroy all APC request data */
+ mysql_cond_destroy(&apc_request.COND_request);
+ }
+ else
+ {
+ mysql_mutex_unlock(LOCK_thd_data_ptr);
+ }
+ return res;
+}
+
+
+/*
+ Process all APC requests.
+ This should be called periodically by the APC target thread.
+*/
+
+void Apc_target::process_apc_requests()
+{
+ if (!get_first_in_queue())
+ return;
+
+ while (1)
+ {
+ Call_request *request;
+
+ mysql_mutex_lock(LOCK_thd_data_ptr);
+ if (!(request= get_first_in_queue()))
+ {
+ /* No requests in the queue */
+ mysql_mutex_unlock(LOCK_thd_data_ptr);
+ break;
+ }
+
+ /*
+ Remove the request from the queue (we're holding queue lock so we can be
+ sure that request owner won't try to remove it)
+ */
+ request->what="dequeued by process_apc_requests";
+ dequeue_request(request);
+ request->processed= TRUE;
+
+ request->call->call_in_target_thread();
+ request->what="func called by process_apc_requests";
+
+#ifndef DBUG_OFF
+ n_calls_processed++;
+#endif
+ mysql_cond_signal(&request->COND_request);
+ mysql_mutex_unlock(LOCK_thd_data_ptr);
+ }
+}
+
=== added file 'sql/my_apc.h'
--- sql/my_apc.h 1970-01-01 00:00:00 +0000
+++ sql/my_apc.h 2012-07-11 09:39:56 +0000
@@ -0,0 +1,134 @@
+/*
+ Copyright (c) 2011 - 2012, Monty Program Ab
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
+
+/*
+ Interface
+ ~~~~~~~~~
+ (
+ - This is an APC request queue
+ - We assume there is a particular owner thread which periodically calls
+ process_apc_requests() to serve the call requests.
+ - Other threads can post call requests, and block until they are exectued.
+ )
+
+ Implementation
+ ~~~~~~~~~~~~~~
+ - The target has a mutex-guarded request queue.
+
+ - After the request has been put into queue, the requestor waits for request
+ to be satisfied. The worker satisifes the request and signals the
+ requestor.
+*/
+
+class THD;
+
+/*
+ Target for asynchronous procedure calls (APCs).
+ - A target is running in some particular thread,
+ - One can make calls to it from other threads.
+*/
+class Apc_target
+{
+ mysql_mutex_t *LOCK_thd_data_ptr;
+public:
+ Apc_target() : enabled(0), apc_calls(NULL) {}
+ ~Apc_target() { DBUG_ASSERT(!enabled && !apc_calls);}
+
+ void init(mysql_mutex_t *target_mutex);
+ void destroy();
+ void enable();
+ void disable();
+
+ void process_apc_requests();
+ /*
+ A lightweight function, intended to be used in frequent checks like this:
+
+ if (apc_target.have_requests()) apc_target.process_apc_requests()
+ */
+ inline bool have_apc_requests()
+ {
+ return test(apc_calls);
+ }
+
+ /* Functor class for calls you can schedule */
+ class Apc_call
+ {
+ public:
+ /* This function will be called in the target thread */
+ virtual void call_in_target_thread()= 0;
+ virtual ~Apc_call() {}
+ };
+
+ /* Make a call in the target thread (see function definition for details) */
+ bool make_apc_call(THD *caller_thd, Apc_call *call, int timeout_sec, bool *timed_out);
+
+#ifndef DBUG_OFF
+ int n_calls_processed; /* Number of calls served by this target */
+#endif
+private:
+ class Call_request;
+
+ /*
+ Non-zero value means we're enabled. It's an int, not bool, because one can
+ call enable() N times (and then needs to call disable() N times before the
+ target is really disabled)
+ */
+ int enabled;
+
+ /*
+ Circular, double-linked list of all enqueued call requests.
+ We use this structure, because we
+ - process requests sequentially: requests are added at the end of the
+ list and removed from the front. With circular list, we can keep one
+ pointer.
+ - a thread that has posted a request may time out (or be KILLed) and
+ cancel the request, which means we need a fast request-removal
+ operation.
+ */
+ Call_request *apc_calls;
+
+ class Call_request
+ {
+ public:
+ Apc_call *call; /* Functor to be called */
+
+ /* The caller will actually wait for "processed==TRUE" */
+ bool processed;
+
+ /* Condition that will be signalled when the request has been served */
+ mysql_cond_t COND_request;
+
+ /* Double linked-list linkage */
+ Call_request *next;
+ Call_request *prev;
+
+ const char *what; /* (debug) state of the request */
+ };
+
+ void enqueue_request(Call_request *qe);
+ void dequeue_request(Call_request *qe);
+
+ /* return the first call request in queue, or NULL if there are none enqueued */
+ Call_request *get_first_in_queue()
+ {
+ return apc_calls;
+ }
+};
+
+#ifdef HAVE_PSI_INTERFACE
+void init_show_explain_psi_keys(void);
+#endif
+
=== modified file 'sql/mysqld.cc'
--- sql/mysqld.cc 2012-06-08 09:18:56 +0000
+++ sql/mysqld.cc 2012-07-07 04:47:41 +0000
@@ -3339,6 +3339,7 @@ SHOW_VAR com_status_vars[]= {
{"show_engine_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_ENGINE_STATUS]), SHOW_LONG_STATUS},
{"show_events", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_EVENTS]), SHOW_LONG_STATUS},
{"show_errors", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_ERRORS]), SHOW_LONG_STATUS},
+ {"show_explain", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_EXPLAIN]), SHOW_LONG_STATUS},
{"show_fields", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_FIELDS]), SHOW_LONG_STATUS},
#ifndef DBUG_OFF
{"show_function_code", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_FUNC_CODE]), SHOW_LONG_STATUS},
@@ -3908,6 +3909,7 @@ static int init_thread_environment()
sp_cache_init();
#ifdef HAVE_EVENT_SCHEDULER
Events::init_mutexes();
+ init_show_explain_psi_keys();
#endif
/* Parameter for threads created for connections */
(void) pthread_attr_init(&connection_attrib);
=== modified file 'sql/opt_subselect.cc'
--- sql/opt_subselect.cc 2012-06-06 13:19:48 +0000
+++ sql/opt_subselect.cc 2012-06-14 20:50:17 +0000
@@ -1657,6 +1657,7 @@ static bool convert_subq_to_sj(JOIN *par
parent_lex->ftfunc_list->push_front(ifm);
}
+ parent_lex->have_merged_subqueries= TRUE;
DBUG_RETURN(FALSE);
}
@@ -1767,6 +1768,8 @@ static bool convert_subq_to_jtbm(JOIN *p
create_subquery_temptable_name(tbl_alias, hash_sj_engine->materialize_join->
select_lex->select_number);
jtbm->alias= tbl_alias;
+
+ parent_lex->have_merged_subqueries= TRUE;
#if 0
/* Inject sj_on_expr into the parent's WHERE or ON */
if (emb_tbl_nest)
=== modified file 'sql/protocol.h'
--- sql/protocol.h 2012-01-13 14:50:02 +0000
+++ sql/protocol.h 2012-07-10 17:23:00 +0000
@@ -35,6 +35,7 @@ class Protocol
protected:
THD *thd;
String *packet;
+ /* Used by net_store_data() for charset conversions */
String *convert;
uint field_pos;
#ifndef DBUG_OFF
@@ -49,6 +50,10 @@ class Protocol
MYSQL_FIELD *next_mysql_field;
MEM_ROOT *alloc;
#endif
+ /*
+ The following two are low-level functions that are invoked from
+ higher-level store_xxx() funcs. The data is stored into this->packet.
+ */
bool net_store_data(const uchar *from, size_t length,
CHARSET_INFO *fromcs, CHARSET_INFO *tocs);
bool store_string_aux(const char *from, size_t length,
=== modified file 'sql/sp_head.cc'
--- sql/sp_head.cc 2012-05-21 18:54:41 +0000
+++ sql/sp_head.cc 2012-06-14 20:50:17 +0000
@@ -218,6 +218,7 @@ sp_get_flags_for_command(LEX *lex)
case SQLCOM_SHOW_CREATE_TRIGGER:
case SQLCOM_SHOW_DATABASES:
case SQLCOM_SHOW_ERRORS:
+ case SQLCOM_SHOW_EXPLAIN:
case SQLCOM_SHOW_FIELDS:
case SQLCOM_SHOW_FUNC_CODE:
case SQLCOM_SHOW_GRANTS:
=== modified file 'sql/sql_class.cc'
--- sql/sql_class.cc 2012-05-21 13:30:25 +0000
+++ sql/sql_class.cc 2012-07-11 09:39:56 +0000
@@ -1196,6 +1196,7 @@ void THD::init(void)
/* Initialize the Debug Sync Facility. See debug_sync.cc. */
debug_sync_init_thread(this);
#endif /* defined(ENABLED_DEBUG_SYNC) */
+ apc_target.init(&LOCK_thd_data);
}
@@ -1361,6 +1362,7 @@ void THD::cleanup(void)
ull= NULL;
}
+ apc_target.destroy();
cleanup_done=1;
DBUG_VOID_RETURN;
}
@@ -2006,6 +2008,20 @@ CHANGED_TABLE_LIST* THD::changed_table_d
int THD::send_explain_fields(select_result *result)
{
List<Item> field_list;
+ make_explain_field_list(field_list);
+ return (result->send_result_set_metadata(field_list,
+ Protocol::SEND_NUM_ROWS |
+ Protocol::SEND_EOF));
+}
+
+
+/*
+ Populate the provided field_list with EXPLAIN output columns.
+ this->lex->describe has the EXPLAIN flags
+*/
+
+void THD::make_explain_field_list(List<Item> &field_list)
+{
Item *item;
CHARSET_INFO *cs= system_charset_info;
field_list.push_back(item= new Item_return_int("id",3, MYSQL_TYPE_LONGLONG));
@@ -2044,10 +2060,9 @@ int THD::send_explain_fields(select_resu
}
item->maybe_null= 1;
field_list.push_back(new Item_empty_string("Extra", 255, cs));
- return (result->send_result_set_metadata(field_list,
- Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF));
}
+
#ifdef SIGNAL_WITH_VIO_CLOSE
void THD::close_active_vio()
{
@@ -2279,6 +2294,16 @@ int select_send::send_data(List<Item> &i
DBUG_RETURN(0);
}
+
+int select_result_explain_buffer::send_data(List<Item> &items)
+{
+ fill_record(thd, dst_table->field, items, TRUE, FALSE);
+ if ((dst_table->file->ha_write_tmp_row(dst_table->record[0])))
+ return 1;
+ return 0;
+}
+
+
bool select_send::send_eof()
{
/*
@@ -3172,6 +3197,10 @@ void THD::end_statement()
}
+/*
+ Start using arena specified by @set. Current arena data will be saved to
+ *backup.
+*/
void THD::set_n_backup_active_arena(Query_arena *set, Query_arena *backup)
{
DBUG_ENTER("THD::set_n_backup_active_arena");
@@ -3186,6 +3215,12 @@ void THD::set_n_backup_active_arena(Quer
}
+/*
+ Stop using the temporary arena, and start again using the arena that is
+ specified in *backup.
+ The temporary arena is returned back into *set.
+*/
+
void THD::restore_active_arena(Query_arena *set, Query_arena *backup)
{
DBUG_ENTER("THD::restore_active_arena");
@@ -3198,6 +3233,43 @@ void THD::restore_active_arena(Query_are
DBUG_VOID_RETURN;
}
+
+/*
+ Produce EXPLAIN data.
+
+ This function is APC-scheduled to be run in the context of the thread that
+ we're producing EXPLAIN for.
+*/
+
+void Show_explain_request::call_in_target_thread()
+{
+ Query_arena backup_arena;
+ bool printed_anything= FALSE;
+
+ /*
+ Change the arena because JOIN::print_explain and co. are going to allocate
+ items. Let them allocate them on our arena.
+ */
+ target_thd->set_n_backup_active_arena((Query_arena*)request_thd,
+ &backup_arena);
+
+ query_str.copy(target_thd->query(),
+ target_thd->query_length(),
+ &my_charset_bin);
+
+ if (target_thd->lex->unit.print_explain(explain_buf, 0 /* explain flags*/,
+ &printed_anything))
+ {
+ failed_to_produce= TRUE;
+ }
+
+ if (!printed_anything)
+ failed_to_produce= TRUE;
+
+ target_thd->restore_active_arena((Query_arena*)request_thd, &backup_arena);
+}
+
+
Statement::~Statement()
{
}
=== modified file 'sql/sql_class.h'
--- sql/sql_class.h 2012-05-21 13:30:25 +0000
+++ sql/sql_class.h 2012-07-11 09:39:56 +0000
@@ -43,7 +43,7 @@
#include "violite.h" /* vio_is_connected */
#include "thr_lock.h" /* thr_lock_type, THR_LOCK_DATA,
THR_LOCK_INFO */
-
+#include "my_apc.h"
class Reprepare_observer;
class Relay_log_info;
@@ -1520,6 +1520,43 @@ class Global_read_lock
extern "C" void my_message_sql(uint error, const char *str, myf MyFlags);
+class select_result_explain_buffer;
+
+
+/*
+ SHOW EXPLAIN request object.
+
+ The thread that runs SHOW EXPLAIN statement creates a Show_explain_request
+ object R, and then schedules APC call of
+ Show_explain_request::call((void*)&R).
+
+*/
+
+class Show_explain_request : public Apc_target::Apc_call
+{
+public:
+ THD *target_thd; /* thd that we're running SHOW EXPLAIN for */
+ THD *request_thd; /* thd that run SHOW EXPLAIN command */
+
+ /* If true, there was some error when producing EXPLAIN output. */
+ bool failed_to_produce;
+
+ /* SHOW EXPLAIN will be stored here */
+ select_result_explain_buffer *explain_buf;
+
+ /* Query that we've got SHOW EXPLAIN for */
+ String query_str;
+
+ /* Overloaded virtual function */
+ void call_in_target_thread();
+};
+
+class THD;
+void mysqld_show_explain(THD *thd, const char *calling_user, ulong thread_id);
+#ifndef DBUG_OFF
+void dbug_serve_apcs(THD *thd, int n_calls);
+#endif
+
/**
@class THD
For each client connection we create a separate thread with THD serving as
@@ -2185,6 +2222,15 @@ class THD :public Statement,
*/
killed_state volatile killed;
+ inline bool check_killed()
+ {
+ if (killed)
+ return TRUE;
+ if (apc_target.have_apc_requests())
+ apc_target.process_apc_requests();
+ return FALSE;
+ }
+
/* scramble - random string sent to client on handshake */
char scramble[SCRAMBLE_LENGTH+1];
@@ -2383,10 +2429,20 @@ class THD :public Statement,
void close_active_vio();
#endif
void awake(killed_state state_to_set);
-
+
/** Disconnect the associated communication endpoint. */
void disconnect();
+
+ /*
+ Allows this thread to serve as a target for others to schedule Async
+ Procedure Calls on.
+
+ It's possible to schedule arbitrary C++ function calls. Currently, only
+ Show_explain_request uses this.
+ */
+ Apc_target apc_target;
+
#ifndef MYSQL_CLIENT
enum enum_binlog_query_type {
/* The query can be logged in row format or in statement format. */
@@ -2580,7 +2636,7 @@ class THD :public Statement,
void add_changed_table(const char *key, long key_length);
CHANGED_TABLE_LIST * changed_table_dup(const char *key, long key_length);
int send_explain_fields(select_result *result);
-
+ void make_explain_field_list(List<Item> &field_list);
/**
Clear the current error, if any.
We do not clear is_fatal_error or is_fatal_sub_stmt_error since we
@@ -3190,10 +3246,42 @@ class sql_exchange :public Sql_alloc
class JOIN;
-class select_result :public Sql_alloc {
+/* Pure interface for sending tabular data */
+class select_result_sink: public Sql_alloc
+{
+public:
+ /*
+ send_data returns 0 on ok, 1 on error and -1 if data was ignored, for
+ example for a duplicate row entry written to a temp table.
+ */
+ virtual int send_data(List<Item> &items)=0;
+ virtual ~select_result_sink() {};
+};
+
+
+/*
+ Interface for sending tabular data, together with some other stuff:
+
+ - Primary purpose seems to be seding typed tabular data:
+ = the DDL is sent with send_fields()
+ = the rows are sent with send_data()
+ Besides that,
+ - there seems to be an assumption that the sent data is a result of
+ SELECT_LEX_UNIT *unit,
+ - nest_level is used by SQL parser
+*/
+
+class select_result :public select_result_sink
+{
protected:
THD *thd;
+ /*
+ All descendant classes have their send_data() skip the first
+ unit->offset_limit_cnt rows sent. Select_materialize
+ also uses unit->get_unit_column_types().
+ */
SELECT_LEX_UNIT *unit;
+ /* Something used only by the parser: */
public:
select_result();
virtual ~select_result() {};
@@ -3211,11 +3299,6 @@ class select_result :public Sql_alloc {
virtual uint field_count(List<Item> &fields) const
{ return fields.elements; }
virtual bool send_result_set_metadata(List<Item> &list, uint flags)=0;
- /*
- send_data returns 0 on ok, 1 on error and -1 if data was ignored, for
- example for a duplicate row entry written to a temp table.
- */
- virtual int send_data(List<Item> &items)=0;
virtual bool initialize_tables (JOIN *join=0) { return 0; }
virtual void send_error(uint errcode,const char *err);
virtual bool send_eof()=0;
@@ -3243,6 +3326,32 @@ class select_result :public Sql_alloc {
/*
+ This is a select_result_sink which simply writes all data into a (temporary)
+ table. Creation/deletion of the table is outside of the scope of the class
+
+ It is aimed at capturing SHOW EXPLAIN output, so:
+ - Unlike select_result class, we don't assume that the sent data is an
+ output of a SELECT_LEX_UNIT (and so we dont apply "LIMIT x,y" from the
+ unit)
+ - We don't try to convert the target table to MyISAM
+*/
+
+class select_result_explain_buffer : public select_result_sink
+{
+public:
+ select_result_explain_buffer(THD *thd_arg, TABLE *table_arg) :
+ thd(thd_arg), dst_table(table_arg) {};
+
+ THD *thd;
+ TABLE *dst_table; /* table to write into */
+
+ /* The following is called in the child thread: */
+ int send_data(List<Item> &items);
+};
+
+
+
+/*
Base class for select_result descendands which intercept and
transform result set rows. As the rows are not sent to the client,
sending of result set metadata should be suppressed as well.
@@ -3812,6 +3921,8 @@ class user_var_entry
DTCollation collation;
};
+user_var_entry *get_variable(HASH *hash, LEX_STRING &name,
+ bool create_if_not_exists);
/*
Unique -- class for unique (removing of duplicates).
=== modified file 'sql/sql_join_cache.cc'
--- sql/sql_join_cache.cc 2012-03-24 17:21:22 +0000
+++ sql/sql_join_cache.cc 2012-05-16 20:59:03 +0000
@@ -2236,7 +2236,7 @@ enum_nested_loop_state JOIN_CACHE::join_
while (!(error= join_tab_scan->next()))
{
- if (join->thd->killed)
+ if (join->thd->check_killed())
{
/* The user has aborted the execution of the query */
join->thd->send_kill_message();
@@ -2506,7 +2506,7 @@ enum_nested_loop_state JOIN_CACHE::join_
for ( ; cnt; cnt--)
{
- if (join->thd->killed)
+ if (join->thd->check_killed())
{
/* The user has aborted the execution of the query */
join->thd->send_kill_message();
@@ -3356,7 +3356,7 @@ int JOIN_TAB_SCAN::next()
update_virtual_fields(thd, table);
while (!err && select && (skip_rc= select->skip_record(thd)) <= 0)
{
- if (thd->killed || skip_rc < 0)
+ if (thd->check_killed() || skip_rc < 0)
return 1;
/*
Move to the next record if the last retrieved record does not
=== modified file 'sql/sql_lex.cc'
--- sql/sql_lex.cc 2012-05-21 13:30:25 +0000
+++ sql/sql_lex.cc 2012-07-05 20:28:30 +0000
@@ -1873,6 +1873,7 @@ void st_select_lex::init_query()
nest_level= 0;
link_next= 0;
is_prep_leaf_list_saved= FALSE;
+ have_merged_subqueries= FALSE;
bzero((char*) expr_cache_may_be_used, sizeof(expr_cache_may_be_used));
m_non_agg_field_used= false;
m_agg_func_used= false;
@@ -3441,7 +3442,7 @@ bool st_select_lex::optimize_unflattened
if (options & SELECT_DESCRIBE)
{
/* Optimize the subquery in the context of EXPLAIN. */
- sl->set_explain_type();
+ sl->set_explain_type(FALSE);
sl->options|= SELECT_DESCRIBE;
inner_join->select_options|= SELECT_DESCRIBE;
}
@@ -3831,9 +3832,12 @@ void SELECT_LEX::update_used_tables()
/**
Set the EXPLAIN type for this subquery.
+
+ @param on_the_fly TRUE<=> We're running a SHOW EXPLAIN command, so we must
+ not change any variables
*/
-void st_select_lex::set_explain_type()
+void st_select_lex::set_explain_type(bool on_the_fly)
{
bool is_primary= FALSE;
if (next_select())
@@ -3855,6 +3859,9 @@ void st_select_lex::set_explain_type()
}
}
+ if (on_the_fly && !is_primary && have_merged_subqueries)
+ is_primary= TRUE;
+
SELECT_LEX *first= master_unit()->first_select();
/* drop UNCACHEABLE_EXPLAIN, because it is for internal usage only */
uint8 is_uncacheable= (uncacheable & ~UNCACHEABLE_EXPLAIN);
@@ -3907,10 +3914,15 @@ void st_select_lex::set_explain_type()
else
{
type= is_uncacheable ? "UNCACHEABLE UNION": "UNION";
+ if (this == master_unit()->fake_select_lex)
+ type= "UNION RESULT";
+
}
}
}
- options|= SELECT_DESCRIBE;
+
+ if (!on_the_fly)
+ options|= SELECT_DESCRIBE;
}
@@ -4057,6 +4069,115 @@ bool st_select_lex::is_merged_child_of(s
}
+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 res;
+ if (join && join->have_query_plan == JOIN::QEP_AVAILABLE)
+ {
+ /*
+ 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->order != 0 && !join->skip_sort_order), // 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;
+ }
+ }
+ }
+ 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);
+ }
+err:
+ return res;
+}
+
+
+int st_select_lex_unit::print_explain(select_result_sink *output,
+ uint8 explain_flags, bool *printed_anything)
+{
+ int res= 0;
+ SELECT_LEX *first= first_select();
+
+ if (first && !first->next_select() && !first->join)
+ {
+ /*
+ If there is only one child, 'first', and it has join==NULL, emit "not in
+ EXPLAIN state" error.
+ */
+ const char *msg="Query plan already deleted";
+ res= print_explain_message_line(output, first, TRUE /* on_the_fly */,
+ 0, msg);
+ return 0;
+ }
+
+ for (SELECT_LEX *sl= first; sl; sl= sl->next_select())
+ {
+ if ((res= sl->print_explain(output, explain_flags, printed_anything)))
+ break;
+ }
+
+ /* Note: fake_select_lex->join may be NULL or non-NULL at this point */
+ if (fake_select_lex)
+ {
+ res= print_fake_select_lex_join(output, TRUE /* on the fly */,
+ fake_select_lex, explain_flags);
+ }
+ return res;
+}
+
+
/**
A routine used by the parser to decide whether we are specifying a full
partitioning or if only partitions to add or to split.
=== modified file 'sql/sql_lex.h'
--- sql/sql_lex.h 2012-05-21 13:30:25 +0000
+++ sql/sql_lex.h 2012-07-10 17:23:00 +0000
@@ -193,6 +193,7 @@ enum enum_sql_command {
SQLCOM_SHOW_RELAYLOG_EVENTS,
SQLCOM_SHOW_USER_STATS, SQLCOM_SHOW_TABLE_STATS, SQLCOM_SHOW_INDEX_STATS,
SQLCOM_SHOW_CLIENT_STATS,
+ SQLCOM_SHOW_EXPLAIN,
/*
When a command is added here, be sure it's also added in mysqld.cc
@@ -355,6 +356,8 @@ typedef uchar index_clause_map;
#define INDEX_HINT_MASK_ALL (INDEX_HINT_MASK_JOIN | INDEX_HINT_MASK_GROUP | \
INDEX_HINT_MASK_ORDER)
+class select_result_sink;
+
/* Single element of an USE/FORCE/IGNORE INDEX list specified as a SQL hint */
class Index_hint : public Sql_alloc
{
@@ -715,6 +718,8 @@ 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);
};
typedef class st_select_lex_unit SELECT_LEX_UNIT;
@@ -775,6 +780,12 @@ class st_select_lex: public st_select_le
those converted to jtbm nests. The list is emptied when conversion is done.
*/
List<Item_in_subselect> sj_subselects;
+
+ /*
+ Needed to correctly generate 'PRIMARY' or 'SIMPLE' for select_type column
+ of EXPLAIN
+ */
+ bool have_merged_subqueries;
List<TABLE_LIST> leaf_tables;
List<TABLE_LIST> leaf_tables_exec;
@@ -1004,7 +1015,7 @@ class st_select_lex: public st_select_le
*/
bool optimize_unflattened_subqueries();
/* Set the EXPLAIN type for this subquery. */
- void set_explain_type();
+ void set_explain_type(bool on_the_fly);
bool handle_derived(LEX *lex, uint phases);
void append_table_to_list(TABLE_LIST *TABLE_LIST::*link, TABLE_LIST *table);
bool get_free_table_map(table_map *map, uint *tablenr);
@@ -1027,8 +1038,10 @@ class st_select_lex: public st_select_le
bool save_leaf_tables(THD *thd);
bool save_prep_leaf_tables(THD *thd);
- bool is_merged_child_of(st_select_lex *ancestor);
+ 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.
@@ -2344,7 +2357,8 @@ struct LEX: public Query_tables_list
char *backup_dir; /* For RESTORE/BACKUP */
char* to_log; /* For PURGE MASTER LOGS TO */
char* x509_subject,*x509_issuer,*ssl_cipher;
- String *wild;
+ String *wild; /* Wildcard in SHOW {something} LIKE 'wild'*/
+ Item *show_explain_for_thread; /* id in SHOW EXPLAIN FOR id */
sql_exchange *exchange;
select_result *result;
Item *default_value, *on_update_value;
=== modified file 'sql/sql_parse.cc'
--- sql/sql_parse.cc 2012-05-21 18:54:41 +0000
+++ sql/sql_parse.cc 2012-07-10 17:23:00 +0000
@@ -335,6 +335,7 @@ void init_update_queries(void)
sql_command_flags[SQLCOM_SHOW_ENGINE_STATUS]= CF_STATUS_COMMAND;
sql_command_flags[SQLCOM_SHOW_ENGINE_MUTEX]= CF_STATUS_COMMAND;
sql_command_flags[SQLCOM_SHOW_ENGINE_LOGS]= CF_STATUS_COMMAND;
+ sql_command_flags[SQLCOM_SHOW_EXPLAIN]= CF_STATUS_COMMAND;
sql_command_flags[SQLCOM_SHOW_PROCESSLIST]= CF_STATUS_COMMAND;
sql_command_flags[SQLCOM_SHOW_GRANTS]= CF_STATUS_COMMAND;
sql_command_flags[SQLCOM_SHOW_CREATE_DB]= CF_STATUS_COMMAND;
@@ -2143,6 +2144,32 @@ mysql_execute_command(THD *thd)
execute_show_status(thd, all_tables);
break;
}
+ case SQLCOM_SHOW_EXPLAIN:
+ {
+ if (!thd->security_ctx->priv_user[0] &&
+ check_global_access(thd,PROCESS_ACL))
+ break;
+
+ /*
+ The select should use only one table, it's the SHOW EXPLAIN pseudo-table
+ */
+ if (lex->sroutines.records || lex->query_tables->next_global)
+ {
+ my_error(ER_NOT_SUPPORTED_YET, MYF(0), "Usage of subqueries or stored "
+ "function calls as part of this statement");
+ break;
+ }
+
+ Item **it= &(lex->show_explain_for_thread);
+ if ((!(*it)->fixed && (*it)->fix_fields(lex->thd, it)) ||
+ (*it)->check_cols(1))
+ {
+ my_message(ER_SET_CONSTANTS_ONLY, ER(ER_SET_CONSTANTS_ONLY),
+ MYF(0));
+ goto error;
+ }
+ /* no break; fall through */
+ }
case SQLCOM_SHOW_DATABASES:
case SQLCOM_SHOW_TABLES:
case SQLCOM_SHOW_TRIGGERS:
=== modified file 'sql/sql_prepare.cc'
--- sql/sql_prepare.cc 2012-05-21 18:54:41 +0000
+++ sql/sql_prepare.cc 2012-06-14 20:50:17 +0000
@@ -2134,6 +2134,7 @@ static bool check_prepared_statement(Pre
Note that we don't need to have cases in this list if they are
marked with CF_STATUS_COMMAND in sql_command_flags
*/
+ case SQLCOM_SHOW_EXPLAIN:
case SQLCOM_DROP_TABLE:
case SQLCOM_RENAME_TABLE:
case SQLCOM_ALTER_TABLE:
=== modified file 'sql/sql_priv.h'
--- sql/sql_priv.h 2012-06-04 15:26:11 +0000
+++ sql/sql_priv.h 2012-06-14 20:50:17 +0000
@@ -331,6 +331,7 @@ enum enum_yes_no_unknown
External variables
*/
+
/* sql_yacc.cc */
#ifndef DBUG_OFF
extern void turn_parser_debug_on();
=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc 2012-06-04 15:26:11 +0000
+++ sql/sql_select.cc 2012-07-05 20:28:30 +0000
@@ -272,6 +272,57 @@ Item_equal *find_item_equal(COND_EQUAL *
JOIN_TAB *first_depth_first_tab(JOIN* join);
JOIN_TAB *next_depth_first_tab(JOIN* join, JOIN_TAB* tab);
+#ifndef DBUG_OFF
+
+/*
+ SHOW EXPLAIN testing: wait for, and serve n_calls APC requests.
+*/
+void dbug_serve_apcs(THD *thd, int n_calls)
+{
+ const char *save_proc_info= thd->proc_info;
+ /* This is so that mysqltest knows we're ready to serve requests: */
+ thd_proc_info(thd, "show_explain_trap");
+
+ /* Busy-wait for n_calls APC requests to arrive and be processed */
+ int n_apcs= thd->apc_target.n_calls_processed + n_calls;
+ while (thd->apc_target.n_calls_processed < n_apcs)
+ {
+ my_sleep(300);
+ if (thd->check_killed())
+ break;
+ }
+ thd_proc_info(thd, save_proc_info);
+}
+
+
+/*
+ Debugging: check if @name=value, comparing as integer
+
+ Intended usage:
+
+ DBUG_EXECUTE_IF("show_explain_probe_2",
+ if (dbug_user_var_equals_int(thd, "select_id", select_id))
+ dbug_serve_apcs(thd, 1);
+ );
+
+*/
+
+bool dbug_user_var_equals_int(THD *thd, const char *name, int value)
+{
+ user_var_entry *var;
+ LEX_STRING varname= {(char*)name, strlen(name)};
+ if ((var= get_variable(&thd->user_vars, varname, FALSE)))
+ {
+ bool null_value;
+ longlong var_value= var->val_int(&null_value);
+ if (!null_value && var_value == value)
+ return TRUE;
+ }
+ return FALSE;
+}
+#endif
+
+
/**
This handles SELECT with and without UNION.
*/
@@ -928,6 +979,13 @@ bool JOIN::prepare_stage2()
}
+int JOIN::optimize()
+{
+ int res= optimize_inner();
+ if (!res)
+ have_query_plan= QEP_AVAILABLE;
+ return res;
+}
/**
global select optimisation.
@@ -941,7 +999,7 @@ bool JOIN::prepare_stage2()
*/
int
-JOIN::optimize()
+JOIN::optimize_inner()
{
ulonglong select_opts_for_readinfo;
uint no_jbuf_after;
@@ -2142,6 +2200,32 @@ JOIN::save_join_tab()
}
+void JOIN::exec()
+{
+ /*
+ Enable SHOW EXPLAIN only if we're in the top-level query.
+ */
+ thd->apc_target.enable();
+ DBUG_EXECUTE_IF("show_explain_probe_join_exec_start",
+ if (dbug_user_var_equals_int(thd,
+ "show_explain_probe_select_id",
+ select_lex->select_number))
+ dbug_serve_apcs(thd, 1);
+ );
+
+ exec_inner();
+
+ 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();
+}
+
+
/**
Exec select.
@@ -2153,8 +2237,8 @@ JOIN::save_join_tab()
@todo
When can we have here thd->net.report_error not zero?
*/
-void
-JOIN::exec()
+
+void JOIN::exec_inner()
{
List<Item> *columns_list= &fields_list;
int tmp_error;
@@ -2458,6 +2542,10 @@ JOIN::exec()
DBUG_PRINT("info",("Creating group table"));
/* Free first data from old join */
+
+ /*
+ psergey-todo: this is the place of pre-mature JOIN::free call.
+ */
curr_join->join_free();
if (curr_join->make_simple_join(this, curr_tmp_table))
DBUG_VOID_RETURN;
@@ -3695,7 +3783,7 @@ make_join_statistics(JOIN *join, List<TA
goto error;
/* Generate an execution plan from the found optimal join order. */
- DBUG_RETURN(join->thd->killed || get_best_combination(join));
+ DBUG_RETURN(join->thd->check_killed() || get_best_combination(join));
error:
/*
@@ -6743,7 +6831,7 @@ best_extension_by_limited_search(JOIN
DBUG_ENTER("best_extension_by_limited_search");
THD *thd= join->thd;
- if (thd->killed) // Abort
+ if (thd->check_killed()) // Abort
DBUG_RETURN(TRUE);
DBUG_EXECUTE("opt", print_plan(join, idx, read_time, record_count, idx,
@@ -6900,7 +6988,7 @@ find_best(JOIN *join,table_map rest_tabl
{
DBUG_ENTER("find_best");
THD *thd= join->thd;
- if (thd->killed)
+ if (thd->check_killed())
DBUG_RETURN(TRUE);
if (!rest_tables)
{
@@ -7214,28 +7302,36 @@ prev_record_reads(POSITION *positions, u
return found;
}
+enum enum_exec_or_opt {WALK_OPTIMIZATION_TABS , WALK_EXECUTION_TABS};
/*
Enumerate join tabs in breadth-first fashion, including const tables.
*/
-JOIN_TAB *first_breadth_first_tab(JOIN *join)
+JOIN_TAB *first_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind)
{
- return join->join_tab; /* There's always one (i.e. first) table */
+ /* There's always one (i.e. first) table */
+ return (tabs_kind == WALK_EXECUTION_TABS)? join->join_tab:
+ join->table_access_tabs;
}
-JOIN_TAB *next_breadth_first_tab(JOIN *join, JOIN_TAB *tab)
+JOIN_TAB *next_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind,
+ JOIN_TAB *tab)
{
+ JOIN_TAB* const first_top_tab= first_breadth_first_tab(join, tabs_kind);
+ const uint n_top_tabs_count= (tabs_kind == WALK_EXECUTION_TABS)?
+ join->top_join_tab_count:
+ join->top_table_access_tabs_count;
if (!tab->bush_root_tab)
{
/* We're at top level. Get the next top-level tab */
tab++;
- if (tab < join->join_tab + join->top_join_tab_count)
+ if (tab < first_top_tab + n_top_tabs_count)
return tab;
/* No more top-level tabs. Switch to enumerating SJM nest children */
- tab= join->join_tab;
+ tab= first_top_tab;
}
else
{
@@ -7259,7 +7355,7 @@ JOIN_TAB *next_breadth_first_tab(JOIN *j
Ok, "tab" points to a top-level table, and we need to find the next SJM
nest and enter it.
*/
- for (; tab < join->join_tab + join->top_join_tab_count; tab++)
+ for (; tab < first_top_tab + n_top_tabs_count; tab++)
{
if (tab->bush_children)
return tab->bush_children->start;
@@ -7283,7 +7379,7 @@ JOIN_TAB *first_top_level_tab(JOIN *join
JOIN_TAB *next_top_level_tab(JOIN *join, JOIN_TAB *tab)
{
- tab= next_breadth_first_tab(join, tab);
+ tab= next_breadth_first_tab(join, WALK_EXECUTION_TABS, tab);
if (tab && tab->bush_root_tab)
tab= NULL;
return tab;
@@ -7583,6 +7679,13 @@ get_best_combination(JOIN *join)
join->top_join_tab_count= join->join_tab_ranges.head()->end -
join->join_tab_ranges.head()->start;
+ /*
+ Save pointers to select join tabs for SHOW EXPLAIN
+ */
+ join->table_access_tabs= join->join_tab;
+ join->top_table_access_tabs_count= join->top_join_tab_count;
+
+
update_depend_map(join);
DBUG_RETURN(0);
}
@@ -7826,6 +7929,7 @@ static bool create_ref_for_key(JOIN *joi
j->ref.null_rejecting= 0;
j->ref.disable_cache= FALSE;
j->ref.null_ref_part= NO_REF_PART;
+ j->ref.const_ref_part_map= 0;
keyuse=org_keyuse;
store_key **ref_key= j->ref.key_copy;
@@ -7861,6 +7965,13 @@ static bool create_ref_for_key(JOIN *joi
if (keyuse->null_rejecting)
j->ref.null_rejecting |= 1 << i;
keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables;
+ /*
+ Todo: we should remove this check for thd->lex->describe on the next
+ line. With SHOW EXPLAIN code, EXPLAIN printout code no longer depends
+ on it. However, removing the check caused change in lots of query
+ plans! Does the optimizer depend on the contents of
+ table_ref->key_copy ? If yes, do we produce incorrect EXPLAINs?
+ */
if (!keyuse->val->used_tables() && !thd->lex->describe)
{ // Compare against constant
store_key_item tmp(thd,
@@ -7873,6 +7984,7 @@ static bool create_ref_for_key(JOIN *joi
if (thd->is_fatal_error)
DBUG_RETURN(TRUE);
tmp.copy();
+ j->ref.const_ref_part_map |= key_part_map(1) << i ;
}
else
*ref_key++= get_store_key(thd,
@@ -8015,6 +8127,7 @@ JOIN::make_simple_join(JOIN *parent, TAB
!(parent->join_tab_reexec= (JOIN_TAB*) thd->alloc(sizeof(JOIN_TAB))))
DBUG_RETURN(TRUE); /* purecov: inspected */
+ // psergey-todo: here, save the pointer for original join_tabs.
join_tab= parent->join_tab_reexec;
table= &parent->table_reexec[0]; parent->table_reexec[0]= temp_table;
table_count= top_join_tab_count= 1;
@@ -10308,9 +10421,18 @@ bool JOIN_TAB::preread_init()
mysql_handle_single_derived(join->thd->lex,
derived, DT_CREATE | DT_FILL))
return TRUE;
+
preread_init_done= TRUE;
if (select && select->quick)
select->quick->replace_handler(table->file);
+
+ DBUG_EXECUTE_IF("show_explain_probe_join_tab_preread",
+ if (dbug_user_var_equals_int(join->thd,
+ "show_explain_probe_select_id",
+ join->select_lex->select_number))
+ dbug_serve_apcs(join->thd, 1);
+ );
+
return FALSE;
}
@@ -10518,6 +10640,8 @@ void JOIN::cleanup(bool full)
{
DBUG_ENTER("JOIN::cleanup");
DBUG_PRINT("enter", ("full %u", (uint) full));
+
+ have_query_plan= QEP_DELETED;
if (table)
{
@@ -15421,7 +15545,7 @@ create_internal_tmp_table_from_heap2(THD
DBUG_EXECUTE_IF("raise_error", write_err= HA_ERR_FOUND_DUPP_KEY ;);
if (write_err)
goto err;
- if (thd->killed)
+ if (thd->check_killed())
{
thd->send_kill_message();
goto err_killed;
@@ -15680,6 +15804,14 @@ do_select(JOIN *join,List<Item> *fields,
else
{
DBUG_ASSERT(join->table_count);
+
+ DBUG_EXECUTE_IF("show_explain_probe_do_select",
+ if (dbug_user_var_equals_int(join->thd,
+ "show_explain_probe_select_id",
+ join->select_lex->select_number))
+ dbug_serve_apcs(join->thd, 1);
+ );
+
if (join->outer_ref_cond && !join->outer_ref_cond->val_int())
error= NESTED_LOOP_NO_MORE_ROWS;
else
@@ -15810,7 +15942,7 @@ sub_select_cache(JOIN *join, JOIN_TAB *j
rc= sub_select(join, join_tab, end_of_records);
DBUG_RETURN(rc);
}
- if (join->thd->killed)
+ if (join->thd->check_killed())
{
/* The user has aborted the execution of the query */
join->thd->send_kill_message();
@@ -16097,7 +16229,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
DBUG_RETURN(NESTED_LOOP_ERROR);
if (error < 0)
DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
- if (join->thd->killed) // Aborted by user
+ if (join->thd->check_killed()) // Aborted by user
{
join->thd->send_kill_message();
DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */
@@ -16841,6 +16973,14 @@ int read_first_record_seq(JOIN_TAB *tab)
static int
test_if_quick_select(JOIN_TAB *tab)
{
+ DBUG_EXECUTE_IF("show_explain_probe_test_if_quick_select",
+ if (dbug_user_var_equals_int(tab->join->thd,
+ "show_explain_probe_select_id",
+ tab->join->select_lex->select_number))
+ dbug_serve_apcs(tab->join->thd, 1);
+ );
+
+
delete tab->select->quick;
tab->select->quick=0;
return tab->select->test_quick_select(tab->join->thd, tab->keys,
@@ -17301,7 +17441,7 @@ end_write(JOIN *join, JOIN_TAB *join_tab
}
}
end:
- if (join->thd->killed)
+ if (join->thd->check_killed())
{
join->thd->send_kill_message();
DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */
@@ -17384,7 +17524,7 @@ end_update(JOIN *join, JOIN_TAB *join_ta
}
join->send_records++;
end:
- if (join->thd->killed)
+ if (join->thd->check_killed())
{
join->thd->send_kill_message();
DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */
@@ -17434,7 +17574,7 @@ end_unique_update(JOIN *join, JOIN_TAB *
DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */
}
}
- if (join->thd->killed)
+ if (join->thd->check_killed())
{
join->thd->send_kill_message();
DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */
@@ -17512,7 +17652,7 @@ end_write_group(JOIN *join, JOIN_TAB *jo
if (join->procedure)
join->procedure->add();
end:
- if (join->thd->killed)
+ if (join->thd->check_killed())
{
join->thd->send_kill_message();
DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */
@@ -18997,7 +19137,7 @@ static int remove_dup_with_compare(THD *
error= file->ha_rnd_next(record);
for (;;)
{
- if (thd->killed)
+ if (thd->check_killed())
{
thd->send_kill_message();
error=0;
@@ -19129,7 +19269,7 @@ static int remove_dup_with_hash_index(TH
for (;;)
{
uchar *org_key_pos;
- if (thd->killed)
+ if (thd->check_killed())
{
thd->send_kill_message();
error=0;
@@ -21148,29 +21288,155 @@ 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,
+ 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));
+ for (uint i=0 ; i < 7; i++)
+ item_list.push_back(item_null);
+ if (options & DESCRIBE_PARTITIONS)
+ item_list.push_back(item_null);
+ if (options & DESCRIBE_EXTENDED)
+ item_list.push_back(item_null);
+
+ item_list.push_back(new Item_string(message,strlen(message),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)
+{
+ 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);
+ /*
+ here we assume that the query will return at least two rows, so we
+ show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
+ and no filesort will be actually done, but executing all selects in
+ the UNION to provide precise EXPLAIN information will hardly be
+ appreciated :)
+ */
+ char table_name_buffer[SAFE_NAME_LEN];
+ item_list.empty();
+ /* id */
+ item_list.push_back(new Item_null);
+ /* select_type */
+ item_list.push_back(new Item_string(select_lex->type,
+ strlen(select_lex->type),
+ cs));
+ /* table */
+ {
+ SELECT_LEX *sl= select_lex->master_unit()->first_select();
+ uint len= 6, lastop= 0;
+ memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
+ for (; sl && len + lastop + 5 < NAME_LEN; sl= sl->next_select())
+ {
+ len+= lastop;
+ lastop= my_snprintf(table_name_buffer + len, NAME_LEN - len,
+ "%u,", sl->select_number);
+ }
+ if (sl || 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 '>'
+ }
+ item_list.push_back(new Item_string(table_name_buffer, len, cs));
+ }
+ /* partitions */
+ if (explain_flags & DESCRIBE_PARTITIONS)
+ item_list.push_back(item_null);
+ /* type */
+ item_list.push_back(new Item_string(join_type_str[JT_ALL],
+ strlen(join_type_str[JT_ALL]),
+ cs));
+ /* possible_keys */
+ 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);
+ /* in_rows */
+ if (explain_flags & DESCRIBE_EXTENDED)
+ item_list.push_back(item_null);
+ /* rows */
+ item_list.push_back(item_null);
+ /* extra */
+ if (select_lex->master_unit()->global_parameters->order_list.first)
+ item_list.push_back(new Item_string("Using filesort",
+ 14, cs));
+ else
+ item_list.push_back(new Item_string("", 0, cs));
+
+ if (result->send_data(item_list))
+ return 1;
+ return 0;
+}
+
+
/**
EXPLAIN handling.
- Send a description about what how the select will be done to stdout.
+ 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
*/
-static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
- bool distinct,const char *message)
+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)
{
List<Item> field_list;
List<Item> item_list;
+ JOIN *join= this; /* Legacy: this code used to be a non-member function */
THD *thd=join->thd;
- select_result *result=join->result;
Item *item_null= new Item_null();
CHARSET_INFO *cs= system_charset_info;
int quick_type;
- DBUG_ENTER("select_describe");
+ int error= 0;
+ DBUG_ENTER("JOIN::print_explain");
DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s",
(ulong)join->select_lex, join->select_lex->type,
message ? message : "NULL"));
+ DBUG_ASSERT(have_query_plan == QEP_AVAILABLE);
/* Don't log this into the slow query log */
- thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED);
- join->unit->offset_limit_cnt= 0;
+
+ 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
@@ -21178,101 +21444,32 @@ static void select_describe(JOIN *join,
*/
if (message)
{
- item_list.push_back(new Item_int((int32)
- join->select_lex->select_number));
- item_list.push_back(new Item_string(join->select_lex->type,
- strlen(join->select_lex->type), cs));
- for (uint i=0 ; i < 7; i++)
- item_list.push_back(item_null);
- if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
- item_list.push_back(item_null);
- if (join->thd->lex->describe & DESCRIBE_EXTENDED)
- item_list.push_back(item_null);
-
- item_list.push_back(new Item_string(message,strlen(message),cs));
- if (result->send_data(item_list))
- join->error= 1;
+ if (print_explain_message_line(result, join->select_lex, on_the_fly,
+ explain_flags, message))
+ error= 1;
+
}
else if (join->select_lex == join->unit->fake_select_lex)
{
- /*
- here we assume that the query will return at least two rows, so we
- show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
- and no filesort will be actually done, but executing all selects in
- the UNION to provide precise EXPLAIN information will hardly be
- appreciated :)
- */
- char table_name_buffer[SAFE_NAME_LEN];
- item_list.empty();
- /* id */
- item_list.push_back(new Item_null);
- /* select_type */
- item_list.push_back(new Item_string(join->select_lex->type,
- strlen(join->select_lex->type),
- cs));
- /* table */
- {
- SELECT_LEX *sl= join->unit->first_select();
- uint len= 6, lastop= 0;
- memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
- for (; sl && len + lastop + 5 < NAME_LEN; sl= sl->next_select())
- {
- len+= lastop;
- lastop= my_snprintf(table_name_buffer + len, NAME_LEN - len,
- "%u,", sl->select_number);
- }
- if (sl || 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 '>'
- }
- item_list.push_back(new Item_string(table_name_buffer, len, cs));
- }
- /* partitions */
- if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
- item_list.push_back(item_null);
- /* type */
- item_list.push_back(new Item_string(join_type_str[JT_ALL],
- strlen(join_type_str[JT_ALL]),
- cs));
- /* possible_keys */
- 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);
- /* in_rows */
- if (join->thd->lex->describe & DESCRIBE_EXTENDED)
- item_list.push_back(item_null);
- /* rows */
- item_list.push_back(item_null);
- /* extra */
- if (join->unit->global_parameters->order_list.first)
- item_list.push_back(new Item_string("Using filesort",
- 14, cs));
- else
- item_list.push_back(new Item_string("", 0, cs));
-
- if (result->send_data(item_list))
- join->error= 1;
+ if (print_fake_select_lex_join(result, on_the_fly,
+ join->select_lex,
+ explain_flags))
+ error= 1;
}
else if (!join->select_lex->master_unit()->derived ||
join->select_lex->master_unit()->derived->is_materialized_derived())
{
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;
uint select_id= join->select_lex->select_number;
+ JOIN_TAB* const first_top_tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS);
- for (JOIN_TAB *tab= first_breadth_first_tab(join); tab;
- tab= next_breadth_first_tab(join, tab))
+ for (JOIN_TAB *tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); tab;
+ tab= next_breadth_first_tab(join, WALK_OPTIMIZATION_TABS, tab))
{
if (tab->bush_root_tab)
{
@@ -21304,6 +21501,7 @@ static void select_describe(JOIN *join,
tmp3.length(0);
tmp4.length(0);
quick_type= -1;
+ QUICK_SELECT_I *quick= NULL;
/* Don't show eliminated tables */
if (table->map & join->eliminated_tables)
@@ -21320,17 +21518,19 @@ static void select_describe(JOIN *join,
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->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;
+ 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;
+ tab_type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE;
}
/* table */
@@ -21360,7 +21560,7 @@ static void select_describe(JOIN *join,
cs));
}
/* "partitions" column */
- if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
+ if (explain_flags & DESCRIBE_PARTITIONS)
{
#ifdef WITH_PARTITION_STORAGE_ENGINE
partition_info *part_info;
@@ -21379,8 +21579,8 @@ static void select_describe(JOIN *join,
#endif
}
/* "type" column */
- item_list.push_back(new Item_string(join_type_str[tab->type],
- strlen(join_type_str[tab->type]),
+ 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())
@@ -21404,7 +21604,7 @@ static void select_describe(JOIN *join,
item_list.push_back(item_null);
/* Build "key", "key_len", and "ref" values and add them to item_list */
- if (tab->type == JT_NEXT)
+ if (tab_type == JT_NEXT)
{
key_info= table->key_info+tab->index;
key_len= key_info->key_length;
@@ -21423,22 +21623,30 @@ static void select_describe(JOIN *join,
length= (longlong10_to_str(key_len, keylen_str_buf, 10) -
keylen_str_buf);
tmp3.append(keylen_str_buf, length, cs);
- if (tab->ref.key_parts)
+ if (tab->ref.key_parts && tab_type != JT_FT)
{
- for (store_key **ref=tab->ref.key_copy ; *ref ; ref++)
+ store_key **ref=tab->ref.key_copy;
+ for (uint kp= 0; kp < tab->ref.key_parts; kp++)
{
if (tmp4.length())
tmp4.append(',');
- tmp4.append((*ref)->name(), strlen((*ref)->name()), cs);
+
+ if ((key_part_map(1) << kp) & tab->ref.const_ref_part_map)
+ tmp4.append("const");
+ else
+ {
+ tmp4.append((*ref)->name(), strlen((*ref)->name()), cs);
+ ref++;
+ }
}
}
}
- if (is_hj && tab->type != JT_HASH)
+ if (is_hj && tab_type != JT_HASH)
{
tmp2.append(':');
tmp3.append(':');
}
- if (tab->type == JT_HASH_NEXT)
+ if (tab_type == JT_HASH_NEXT)
{
register uint length;
key_info= table->key_info+tab->index;
@@ -21453,9 +21661,9 @@ static void select_describe(JOIN *join,
tab->select && tab->select->quick)
=======*/
}
- if (tab->type != JT_CONST && tab->select && tab->select->quick)
+ if (tab->type != JT_CONST && tab->select && quick)
tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3);
- if (key_info || (tab->select && tab->select->quick))
+ if (key_info || (tab->select && quick))
{
if (tmp2.length())
item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
@@ -21465,7 +21673,7 @@ static void select_describe(JOIN *join,
item_list.push_back(new Item_string(tmp3.ptr(),tmp3.length(),cs));
else
item_list.push_back(item_null);
- if (key_info && tab->type != JT_NEXT)
+ if (key_info && tab_type != JT_NEXT)
item_list.push_back(new Item_string(tmp4.ptr(),tmp4.length(),cs));
else
item_list.push_back(item_null);
@@ -21508,7 +21716,7 @@ static void select_describe(JOIN *join,
table_list->schema_table)
{
/* in_rows */
- if (join->thd->lex->describe & DESCRIBE_EXTENDED)
+ if (explain_flags & DESCRIBE_EXTENDED)
item_list.push_back(item_null);
/* rows */
item_list.push_back(item_null);
@@ -21516,9 +21724,9 @@ static void select_describe(JOIN *join,
else
{
ha_rows examined_rows;
- if (tab->select && tab->select->quick)
- examined_rows= tab->select->quick->records;
- else if (tab->type == JT_NEXT || tab->type == JT_ALL || is_hj)
+ if (tab->select && quick)
+ examined_rows= quick->records;
+ else if (tab_type == JT_NEXT || tab_type == JT_ALL || is_hj)
{
if (tab->limit)
examined_rows= tab->limit;
@@ -21545,7 +21753,7 @@ static void select_describe(JOIN *join,
MY_INT64_NUM_DECIMAL_DIGITS));
/* Add "filtered" field to item_list. */
- if (join->thd->lex->describe & DESCRIBE_EXTENDED)
+ if (explain_flags & DESCRIBE_EXTENDED)
{
float f= 0.0;
if (examined_rows)
@@ -21557,11 +21765,11 @@ static void select_describe(JOIN *join,
/* Build "Extra" field and add it to item_list. */
key_read=table->key_read;
- if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&
+ if ((tab_type == JT_NEXT || tab_type == JT_CONST) &&
table->covering_keys.is_set(tab->index))
key_read=1;
if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT &&
- !((QUICK_ROR_INTERSECT_SELECT*)tab->select->quick)->need_to_fetch_row)
+ !((QUICK_ROR_INTERSECT_SELECT*)quick)->need_to_fetch_row)
key_read=1;
if (tab->info)
@@ -21589,8 +21797,8 @@ static void select_describe(JOIN *join,
uint keyno= MAX_KEY;
if (tab->ref.key_parts)
keyno= tab->ref.key;
- else if (tab->select && tab->select->quick)
- keyno = tab->select->quick->index;
+ else if (tab->select && quick)
+ keyno = quick->index;
if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
table->file->pushed_idx_cond)
@@ -21629,7 +21837,7 @@ static void select_describe(JOIN *join,
{
extra.append(STRING_WITH_LEN("; Using where with pushed "
"condition"));
- if (thd->lex->describe & DESCRIBE_EXTENDED)
+ if (explain_flags & DESCRIBE_EXTENDED)
{
extra.append(STRING_WITH_LEN(": "));
((COND *)pushed_cond)->print(&extra, QT_ORDINARY);
@@ -21722,7 +21930,7 @@ static void select_describe(JOIN *join,
extra.append(STRING_WITH_LEN("; End temporary"));
else if (tab->do_firstmatch)
{
- if (tab->do_firstmatch == join->join_tab - 1)
+ if (tab->do_firstmatch == /*join->join_tab*/ first_top_tab - 1)
extra.append(STRING_WITH_LEN("; FirstMatch"));
else
{
@@ -21772,9 +21980,28 @@ static void select_describe(JOIN *join,
// For next iteration
used_tables|=table->map;
if (result->send_data(item_list))
- join->error= 1;
+ error= 1;
}
}
+ DBUG_RETURN(error);
+}
+
+
+/*
+ See st_select_lex::print_explain() for the SHOW EXPLAIN counterpart
+*/
+
+static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
+ bool distinct,const char *message)
+{
+ 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);
+
for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit();
unit;
unit= unit->next_unit())
@@ -21817,7 +22044,7 @@ bool mysql_explain_union(THD *thd, SELEC
for (SELECT_LEX *sl= first; sl; sl= sl->next_select())
{
- sl->set_explain_type();
+ sl->set_explain_type(FALSE);
sl->options|= SELECT_DESCRIBE;
}
=== modified file 'sql/sql_select.h'
--- sql/sql_select.h 2012-05-21 18:54:41 +0000
+++ sql/sql_select.h 2012-06-14 20:50:17 +0000
@@ -101,6 +101,13 @@ typedef struct st_table_ref
uchar *key_buff; ///< value to look for with key
uchar *key_buff2; ///< key_buff+key_length
store_key **key_copy; //
+
+ /*
+ Bitmap of key parts which refer to constants. key_copy only has copiers for
+ non-const key parts.
+ */
+ key_part_map const_ref_part_map;
+
Item **items; ///< val()'s for each keypart
/*
Array of pointers to trigger variables. Some/all of the pointers may be
@@ -896,6 +903,20 @@ class JOIN :public Sql_alloc
public:
JOIN_TAB *join_tab, **best_ref;
+
+ /*
+ For "Using temporary+Using filesort" queries, JOIN::join_tab can point to
+ either:
+ 1. array of join tabs describing how to run the select, or
+ 2. array of single join tab describing read from the temporary table.
+
+ SHOW EXPLAIN code needs to read/show #1. This is why two next members are
+ there for saving it.
+ */
+ JOIN_TAB *table_access_tabs;
+ uint top_table_access_tabs_count;
+
+
JOIN_TAB **map2table; ///< mapping between table indexes and JOIN_TABs
JOIN_TAB *join_tab_save; ///< saved join_tab for subquery reexecution
@@ -1161,8 +1182,14 @@ class JOIN :public Sql_alloc
const char *zero_result_cause; ///< not 0 if exec must return zero result
bool union_part; ///< this subselect is part of union
+
+ enum join_optimization_state { NOT_OPTIMIZED=0,
+ OPTIMIZATION_IN_PROGRESS=1,
+ OPTIMIZATION_DONE=2};
bool optimized; ///< flag to avoid double optimization in EXPLAIN
bool initialized; ///< flag to avoid double init_execution calls
+
+ enum { QEP_NOT_PRESENT_YET, QEP_AVAILABLE, QEP_DELETED} have_query_plan;
/*
Additional WHERE and HAVING predicates to be considered for IN=>EXISTS
@@ -1245,6 +1272,7 @@ class JOIN :public Sql_alloc
ref_pointer_array_size= 0;
zero_result_cause= 0;
optimized= 0;
+ have_query_plan= QEP_NOT_PRESENT_YET;
initialized= 0;
cond_equal= 0;
having_equal= 0;
@@ -1273,9 +1301,11 @@ class JOIN :public Sql_alloc
SELECT_LEX_UNIT *unit);
bool prepare_stage2();
int optimize();
+ int optimize_inner();
int reinit();
int init_execution();
void exec();
+ void exec_inner();
int destroy();
void restore_tmp();
bool alloc_func_list();
@@ -1387,6 +1417,11 @@ 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);
private:
/**
TRUE if the query contains an aggregate function but has no GROUP
@@ -1738,6 +1773,9 @@ inline bool optimizer_flag(THD *thd, uin
return (thd->variables.optimizer_switch & flag);
}
+int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly,
+ SELECT_LEX *select_lex, uint8 select_options);
+
uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select,
ha_rows limit, bool *need_sort, bool *reverse);
ORDER *simple_remove_const(ORDER *order, COND *where);
=== modified file 'sql/sql_show.cc'
--- sql/sql_show.cc 2012-05-21 18:54:41 +0000
+++ sql/sql_show.cc 2012-07-10 17:23:00 +0000
@@ -1998,6 +1998,124 @@ void mysqld_list_processes(THD *thd,cons
DBUG_VOID_RETURN;
}
+
+static
+const char *target_not_explainable_cmd="Target is not running EXPLAINable command";
+
+/*
+ Store the SHOW EXPLAIN output in the temporary table.
+*/
+
+int fill_show_explain(THD *thd, TABLE_LIST *table, COND *cond)
+{
+ const char *calling_user;
+ THD *tmp;
+ my_thread_id thread_id;
+ DBUG_ENTER("fill_show_explain");
+
+ DBUG_ASSERT(cond==NULL);
+ thread_id= thd->lex->show_explain_for_thread->val_int();
+ calling_user= (thd->security_ctx->master_access & PROCESS_ACL) ? NullS :
+ thd->security_ctx->priv_user;
+ /*
+ Find the thread we need EXPLAIN for. Thread search code was copied from
+ kill_one_thread()
+ */
+ mysql_mutex_lock(&LOCK_thread_count); // For unlink from list
+ I_List_iterator<THD> it(threads);
+ while ((tmp=it++))
+ {
+ if (tmp->command == COM_DAEMON)
+ continue;
+ if (tmp->thread_id == thread_id)
+ {
+ mysql_mutex_lock(&tmp->LOCK_thd_data); // Lock from delete
+ break;
+ }
+ }
+ mysql_mutex_unlock(&LOCK_thread_count);
+
+ if (tmp)
+ {
+ Security_context *tmp_sctx= tmp->security_ctx;
+ /*
+ If calling_user==NULL, calling thread has SUPER or PROCESS
+ privilege, and so can do SHOW EXPLAIN on any user.
+
+ if calling_user!=NULL, he's only allowed to view SHOW EXPLAIN on
+ his own threads.
+ */
+ if (calling_user && (!tmp_sctx->user || strcmp(calling_user,
+ tmp_sctx->user)))
+ {
+ my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "PROCESSLIST");
+ mysql_mutex_unlock(&tmp->LOCK_thd_data);
+ DBUG_RETURN(1);
+ }
+
+ if (tmp == thd)
+ {
+ mysql_mutex_unlock(&tmp->LOCK_thd_data);
+ my_error(ER_ERROR_WHEN_EXECUTING_COMMAND, MYF(0), "SHOW EXPLAIN",
+ target_not_explainable_cmd);
+ DBUG_RETURN(1);
+ }
+
+ bool bres;
+ /*
+ Ok we've found the thread of interest and it won't go away because
+ we're holding its LOCK_thd data. Post it a SHOW EXPLAIN request.
+ */
+ bool timed_out;
+ int timeout_sec= 30;
+ Show_explain_request explain_req;
+ select_result_explain_buffer *explain_buf;
+
+ explain_buf= new select_result_explain_buffer(thd, table->table);
+
+ explain_req.explain_buf= explain_buf;
+ explain_req.target_thd= tmp;
+ explain_req.request_thd= thd;
+ explain_req.failed_to_produce= FALSE;
+
+ /* Ok, we have a lock on target->LOCK_thd_data, can call: */
+ bres= tmp->apc_target.make_apc_call(thd, &explain_req, timeout_sec, &timed_out);
+
+ if (bres || explain_req.failed_to_produce)
+ {
+ if (thd->killed)
+ {
+ thd->send_kill_message();
+ }
+ else
+ if (timed_out)
+ {
+ my_error(ER_ERROR_WHEN_EXECUTING_COMMAND, MYF(0),
+ "SHOW EXPLAIN",
+ "Timeout");
+ }
+ else
+ {
+ my_error(ER_ERROR_WHEN_EXECUTING_COMMAND, MYF(0),
+ "SHOW EXPLAIN", target_not_explainable_cmd);
+ }
+ bres= TRUE;
+ }
+ else
+ {
+ push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+ ER_YES, explain_req.query_str.c_ptr_safe());
+ }
+ DBUG_RETURN(bres);
+ }
+ else
+ {
+ my_error(ER_NO_SUCH_THREAD, MYF(0), thread_id);
+ DBUG_RETURN(1);
+ }
+}
+
+
int fill_schema_processlist(THD* thd, TABLE_LIST* tables, COND* cond)
{
TABLE *table= tables->table;
@@ -8309,6 +8427,7 @@ ST_FIELD_INFO keycache_fields_info[]=
};
+extern ST_FIELD_INFO show_explain_fields_info[];
/*
Description of ST_FIELD_INFO in table.h
@@ -8340,6 +8459,8 @@ ST_SCHEMA_TABLE schema_tables[]=
{"EVENTS", events_fields_info, create_schema_table,
0, make_old_format, 0, -1, -1, 0, 0},
#endif
+ {"EXPLAIN", show_explain_fields_info, create_schema_table, fill_show_explain,
+ make_old_format, 0, -1, -1, TRUE /*hidden*/ , 0},
{"FILES", files_fields_info, create_schema_table,
hton_fill_schema_table, 0, 0, -1, -1, 0, 0},
{"GLOBAL_STATUS", variables_fields_info, create_schema_table,
=== modified file 'sql/sql_union.cc'
--- sql/sql_union.cc 2012-03-11 22:45:18 +0000
+++ sql/sql_union.cc 2012-06-07 17:19:22 +0000
@@ -720,6 +720,8 @@ bool st_select_lex_unit::exec()
}
}
+ DBUG_EXECUTE_IF("show_explain_probe_union_read",
+ dbug_serve_apcs(thd, 1););
/* Send result to 'result' */
saved_error= TRUE;
{
=== modified file 'sql/sql_yacc.yy'
--- sql/sql_yacc.yy 2012-06-10 10:12:50 +0000
+++ sql/sql_yacc.yy 2012-07-10 17:23:00 +0000
@@ -11614,6 +11614,13 @@ select_var_ident:
Lex->spname= $3;
Lex->sql_command = SQLCOM_SHOW_CREATE_EVENT;
}
+ | describe_command FOR_SYM expr
+ {
+ Lex->sql_command= SQLCOM_SHOW_EXPLAIN;
+ if (prepare_schema_table(YYTHD, Lex, 0, SCH_EXPLAIN))
+ MYSQL_YYABORT;
+ Lex->show_explain_for_thread= $3;
+ }
;
show_engine_param:
=== modified file 'sql/table.h'
--- sql/table.h 2012-05-04 05:16:38 +0000
+++ sql/table.h 2012-05-16 20:59:03 +0000
@@ -1106,7 +1106,12 @@ struct TABLE
See TABLE_LIST::process_index_hints().
*/
bool force_index_group;
- bool distinct,const_table,no_rows, used_for_duplicate_elimination;
+ /*
+ TRUE<=> this table was created with create_tmp_table(... distinct=TRUE..)
+ call
+ */
+ bool distinct;
+ bool const_table,no_rows, used_for_duplicate_elimination;
/**
If set, the optimizer has found that row retrieval should access index
=== added directory 'unittest/sql'
=== added file 'unittest/sql/CMakeLists.txt'
--- unittest/sql/CMakeLists.txt 1970-01-01 00:00:00 +0000
+++ unittest/sql/CMakeLists.txt 2012-06-28 12:46:24 +0000
@@ -0,0 +1,3 @@
+
+MY_ADD_TESTS(my_apc LINK_LIBRARIES mysys EXT cc)
+
=== added file 'unittest/sql/my_apc-t.cc'
--- unittest/sql/my_apc-t.cc 1970-01-01 00:00:00 +0000
+++ unittest/sql/my_apc-t.cc 2012-07-05 18:04:13 +0000
@@ -0,0 +1,227 @@
+/*
+ Copyright (c) 2012, Monty Program Ab
+
+ This program is free software; you can redistribute it and/or modify
+ it under the terms of the GNU General Public License as published by
+ the Free Software Foundation; version 2 of the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
+
+/*
+ This file does standalone APC system tests.
+*/
+#include <stdio.h>
+#include <my_global.h>
+#include <my_pthread.h>
+#include <my_sys.h>
+
+#include <tap.h>
+
+/*
+ A fake THD with enter_cond/exit_cond and some other members.
+*/
+class THD
+{
+ mysql_mutex_t* thd_mutex;
+public:
+ bool killed;
+
+ THD() : killed(FALSE) {}
+ inline const char* enter_cond(mysql_cond_t *cond, mysql_mutex_t* mutex,
+ const char* msg)
+ {
+ mysql_mutex_assert_owner(mutex);
+ thd_mutex= mutex;
+ return NULL;
+ }
+ inline void exit_cond(const char* old_msg)
+ {
+ mysql_mutex_unlock(thd_mutex);
+ }
+};
+
+#include "../sql/my_apc.h"
+
+#define MY_APC_STANDALONE 1
+#include "../sql/my_apc.cc"
+
+volatile bool started= FALSE;
+volatile bool service_should_exit= FALSE;
+volatile bool requestors_should_exit=FALSE;
+
+/* Counters for APC calls */
+int apcs_served= 0;
+int apcs_missed=0;
+int apcs_timed_out=0;
+mysql_mutex_t apc_counters_mutex;
+
+inline void increment_counter(int *var)
+{
+ mysql_mutex_lock(&apc_counters_mutex);
+ *var= *var+1;
+ mysql_mutex_unlock(&apc_counters_mutex);
+}
+
+volatile bool have_errors= false;
+
+Apc_target apc_target;
+mysql_mutex_t target_mutex;
+
+int int_rand(int size)
+{
+ return (int) (0.5 + ((double)rand() / RAND_MAX) * size);
+}
+
+/*
+ APC target thread (the one that will serve the APC requests). We will have
+ one target.
+*/
+void *test_apc_service_thread(void *ptr)
+{
+ my_thread_init();
+ mysql_mutex_init(0, &target_mutex, MY_MUTEX_INIT_FAST);
+ apc_target.init(&target_mutex);
+ apc_target.enable();
+ started= TRUE;
+ fprintf(stderr, "# test_apc_service_thread started\n");
+ while (!service_should_exit)
+ {
+ //apc_target.disable();
+ my_sleep(10000);
+ //apc_target.enable();
+ for (int i = 0; i < 10 && !service_should_exit; i++)
+ {
+ apc_target.process_apc_requests();
+ my_sleep(int_rand(30));
+ }
+ }
+ apc_target.disable();
+ apc_target.destroy();
+ mysql_mutex_destroy(&target_mutex);
+ my_thread_end();
+ pthread_exit(0);
+ return NULL;
+}
+
+
+/*
+ One APC request (to write 'value' into *where_to)
+*/
+class Apc_order : public Apc_target::Apc_call
+{
+public:
+ int value; // The value
+ int *where_to; // Where to write it
+ Apc_order(int a, int *b) : value(a), where_to(b) {}
+
+ void call_in_target_thread()
+ {
+ my_sleep(int_rand(1000));
+ *where_to = value;
+ increment_counter(&apcs_served);
+ }
+};
+
+
+/*
+ APC requestor thread. It makes APC requests, and checks if they were actually
+ executed.
+*/
+void *test_apc_requestor_thread(void *ptr)
+{
+ my_thread_init();
+ fprintf(stderr, "# test_apc_requestor_thread started\n");
+ THD my_thd;
+
+ while (!requestors_should_exit)
+ {
+ int dst_value= 0;
+ int src_value= int_rand(4*1000*100);
+ /* Create an APC to do "dst_value= src_value" assignment */
+ Apc_order apc_order(src_value, &dst_value);
+ bool timed_out;
+
+ mysql_mutex_lock(&target_mutex);
+ bool res= apc_target.make_apc_call(&my_thd, &apc_order, 60, &timed_out);
+ if (res)
+ {
+ if (timed_out)
+ increment_counter(&apcs_timed_out);
+ else
+ increment_counter(&apcs_missed);
+
+ if (dst_value != 0)
+ {
+ fprintf(stderr, "APC was done even though return value says it wasnt!\n");
+ have_errors= true;
+ }
+ }
+ else
+ {
+ if (dst_value != src_value)
+ {
+ fprintf(stderr, "APC was not done even though return value says it was!\n");
+ have_errors= true;
+ }
+ }
+ //my_sleep(300);
+ }
+ fprintf(stderr, "# test_apc_requestor_thread exiting\n");
+ my_thread_end();
+ return NULL;
+}
+
+/* Number of APC requestor threads */
+const int N_THREADS=23;
+
+
+int main(int args, char **argv)
+{
+ pthread_t service_thr;
+ pthread_t request_thr[N_THREADS];
+ int i;
+
+ my_thread_global_init();
+
+ mysql_mutex_init(0, &apc_counters_mutex, MY_MUTEX_INIT_FAST);
+
+ plan(1);
+ diag("Testing APC delivery and execution");
+
+ pthread_create(&service_thr, NULL, test_apc_service_thread, (void*)NULL);
+ while (!started)
+ my_sleep(1000);
+ for (i = 0; i < N_THREADS; i++)
+ pthread_create(&request_thr[i], NULL, test_apc_requestor_thread, (void*)NULL);
+
+ for (i = 0; i < 15; i++)
+ {
+ my_sleep(500*1000);
+ fprintf(stderr, "# %d APCs served %d missed\n", apcs_served, apcs_missed);
+ }
+ fprintf(stderr, "# Shutting down requestors\n");
+ requestors_should_exit= TRUE;
+ for (i = 0; i < N_THREADS; i++)
+ pthread_join(request_thr[i], NULL);
+
+ fprintf(stderr, "# Shutting down service\n");
+ service_should_exit= TRUE;
+ pthread_join(service_thr, NULL);
+
+ mysql_mutex_destroy(&apc_counters_mutex);
+
+ fprintf(stderr, "# Done.\n");
+ my_thread_end();
+ my_thread_global_end();
+
+ ok1(!have_errors);
+ return exit_status();
+}
+
Follow ups
References
