path: root/mysql-test/main/show_analyze.test

#
# Tests for SHOW ANALYZE FOR functionality
#
--source include/have_debug.inc
--source include/have_debug_sync.inc
--source include/have_innodb.inc
--source include/have_perfschema.inc
# Using valgrind can cause 'reap' to fail. See comment below
--source include/not_valgrind.inc

--disable_warnings
drop table if exists t0, t1, t2, t3, t4;
drop view if exists v1;
--enable_warnings
SET @old_debug= @@session.debug_dbug;

#
# 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 ANALYZE command, and the
# test fails.
#
-- source include/not_embedded.inc

set debug_sync='RESET';

create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int);
alter table t1 add b int, add c int, add filler char(32);
insert into t1 select A.a, 10*B.a, 100*C.a, 'foo filler' from t0 A, t0 B, t0 C;
alter table t1 add key(a), add key(b);
analyze table t1;

#
# Try SHOW ANALYZE for a non-existent thread
#
--error ER_NO_SUCH_THREAD
show analyze for 1001;

--error ER_SET_CONSTANTS_ONLY
show analyze for (select a from t0 limit 1);

#
# Setup two threads and their ids
#
let $thr1=`select connection_id()`;
connect (con1, localhost, root,,);
connection con1;
let $thr2=`select connection_id()`;
SET @old_debug= @@session.debug_dbug;
connection default;

# SHOW ANALYZE FOR <idle thread>
--error ER_TARGET_NOT_EXPLAINABLE
evalp show analyze for $thr2;

# SHOW ANALYZE FOR <ourselves>
--error ER_TARGET_NOT_EXPLAINABLE
evalp show analyze for $thr1;

let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2;

#
# Test SHOW ANALYZE for simple queries
#
connection con1;
set @show_explain_probe_select_id=1;
SET debug_dbug='+d,show_explain_probe_join_exec_end';
send select count(*) from t1 where c < 500;

connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;


send select max(c) from t1 where c < 10;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;


--echo # We can catch ANALYZE too.
send analyze select max(c) from t1 where a < 10;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
SET debug_dbug=@old_debug;


--echo # UNION, select, first branch
set @show_explain_probe_select_id=1;
SET debug_dbug='+d,show_explain_probe_join_exec_end';
send select max(a) from t0 A where a<=5 union select max(a+1) from t0 B where a>=9;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;


--echo # UNION, select, second branch
set @show_explain_probe_select_id=2; # <--- Second branch
SET debug_dbug='+d,show_explain_probe_join_exec_end';
send select max(a) from t0 A where a<=5 union select max(a+1) from t0 B where a>=9;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;


--echo # UNION, analyze, first branch
set @show_explain_probe_select_id=1;
SET debug_dbug='+d,show_explain_probe_join_exec_end';
send analyze select a from t0 A where a<=5 union select a+1 from t0 B where a>=9;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;


--echo # UNION, analyze, second branch
set @show_explain_probe_select_id=2;
send analyze select a from t0 A where a<=5 union select a+1 from t0 B where a>=9;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
SET debug_dbug=@old_debug;


--echo # Uncorrelated  subquery, select
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 a>6) from t0 A where a<2;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
SET debug_dbug=@old_debug;


--echo # Uncorrelated  subquery, analyze
set @show_explain_probe_select_id=1;
SET debug_dbug='+d,show_explain_probe_join_exec_end';
send analyze select a, (select max(a) from t0 B where a>6) from t0 A where a<2;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
SET debug_dbug=@old_debug;


--echo # correlated subquery, select, before execution start
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<2;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
SET debug_dbug=@old_debug;


--echo # correlated subquery, select, after execution
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<2;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
SET debug_dbug=@old_debug;


--echo # correlated subquery, analyze
set @show_explain_probe_select_id=1;
SET debug_dbug='+d,show_explain_probe_join_exec_end';
send analyze select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
SET debug_dbug=@old_debug;


--echo # Try to do SHOW ANALYZE for a query that runs a  SET command:
--echo #
create table t2 (a int);
insert into t2 values (1),(2);
set @show_explain_probe_select_id=3; # Stop in the subquery.
SET debug_dbug='+d,show_explain_probe_join_exec_start';
# t2 has 2 rows so we will stop in the subquery twice: 
#  - first one to serve the SHOW ANALYZE request
#  - second one when waiting to be KILLed.
send set @foo= (select max(a) from t2
                 where a + (select max(a) from t0 where t0.a>t2.a) < 10000);
connection default;
--source include/wait_condition.inc
--error ER_TARGET_NOT_EXPLAINABLE
evalp show analyze for $thr2;
evalp kill query $thr2;
connection con1;
--error ER_QUERY_INTERRUPTED
reap;
SET debug_dbug=@old_debug;
drop table t2;

--echo #
--echo # Attempt SHOW ANALYZE 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
evalp show analyze for $thr2;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
drop table t2;
SET debug_dbug=@old_debug;


--echo #
--echo # Attempt SHOW ANALYZE 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
evalp show analyze for $thr2;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
drop table t2;
SET debug_dbug=@old_debug;


--echo #
--echo # Multiple SHOW ANALYZE 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 analyze for $thr2;
--source include/wait_condition.inc
evalp show analyze for $thr2;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
SET debug_dbug=@old_debug;
drop table t2;


--echo #
--echo # SHOW ANALYZE for SELECT ... ORDER BY with "Using filesort"
--echo #
SET debug_dbug='+d,show_explain_probe_join_exec_end';
set @show_explain_probe_select_id=1;
send select * from t0 order by a;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
SET debug_dbug=@old_debug;


--echo #
--echo # SHOW ANALYZE for SELECT ... with "Using temporary"
--echo #
connection con1;
SET debug_dbug='+d,show_explain_probe_join_exec_end';
set @show_explain_probe_select_id=1;
send select distinct a from t0;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
SET debug_dbug=@old_debug;


--echo #
--echo # SHOW ANALYZE for SELECT ... with "Using temporary; Using filesort"
--echo #
SET debug_dbug='+d,show_explain_probe_join_exec_end';
set @show_explain_probe_select_id=1;
send select distinct a from t0;
connection default;
--source include/wait_condition.inc
evalp show analyze for $thr2;
connection con1;
reap;
SET debug_dbug=@old_debug;


drop table t0,t1;

--echo #
--echo # MDEV-28124: Server crashes in Explain_aggr_filesort::print_json_members
--echo # upon SHOW ANALYZE/EXPLAIN FORMAT=JSON
--echo #

let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2;
connection con1;
set @show_explain_probe_query= 'SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency';
set debug_dbug='+d,explain_notify_tables_are_closed';

# Statement guarantees to produce 0 on every run
send SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency;
connection default;
--source include/wait_condition.inc
--error ER_TARGET_NOT_EXPLAINABLE
evalp SHOW ANALYZE FOR $thr2;

connection con1;
reap;

--echo # End
connection default;
disconnect con1;
set debug_sync='RESET';


--echo #
--echo # MDEV-28201: Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON
--echo #
CREATE TABLE t1 ( a varchar(1));
INSERT INTO t1 VALUES ('a'),('b');
--source include/analyze-format.inc
ANALYZE format=json 
SELECT 1 FROM t1 GROUP BY convert_tz('1969-12-31 22:00:00',a,'+10:00');
DROP TABLE t1;

--echo #
--echo # MDEV-31432 tmp_table field accessed after free
--echo # testing for the above (MDEV-28201) caused use after free error
--echo #
create table t1 (x int) engine=myisam;
insert into t1 values(1);
set @tmp=@@optimizer_trace;
set @@optimizer_trace=1;
# Different warning text is produced in regular and --ps-protocol runs:
--disable_warnings
SELECT
  1 IN
  ((
     SELECT
       1 IN (SELECT 1 AS x0
             FROM
               (
                SELECT *
                FROM (SELECT 1 AS x) AS x5
                GROUP BY x,x
                HAVING
                  x IN (
                    SELECT *
                    FROM t1 AS x1
                    WHERE
                      x IN (SELECT 1 AS x
                            FROM t1 AS x3
                            GROUP BY x
                            HAVING
                              x IN (SELECT 0 FROM t1 AS x4)
                           )
                  )
               ) AS x6
            )
     FROM
       t1
  )) as VAL;
--enable_warnings
set optimizer_trace=@tmp;
drop table t1;