test-example-projects/c++_projects/280w-mysql-8.0.18/mysql-test/t/explain_other.test

--echo # WL#6369: EXPLAIN for other query

#
# We need the Debug Sync Facility.
#
--source include/have_debug.inc
--source include/have_debug_sync.inc

CREATE DATABASE mysqltest1;
USE mysqltest1;
CREATE TABLE t1 (f1 int);
INSERT INTO t1 VALUES (1),(2);

let $QID= `SELECT CONNECTION_ID()`;
# Hide connection ID
--disable_query_log
--error 1295
--eval PREPARE stmt FROM "EXPLAIN FOR CONNECTION $QID";
--enable_query_log

connect (ce, localhost, root,, mysqltest1);
connect (cq, localhost, root,, mysqltest1);

# Kill explaining query
# Test could be enabled when mysqltest will be able to 'send' two commands
# at once. Meanwhile, one could use 3 command line clients to run this test
# manually.
#connection ce;
#let $EID= `SELECT CONNECTION_ID()`;
#
#connection cq;
#let $QID= `SELECT CONNECTION_ID()`;
#SET DEBUG_SYNC="before_join_exec SIGNAL explain_me WAIT_FOR killed";
#send SELECT * FROM t1;
#
#connection ce;
#SET DEBUG_SYNC="now WAIT_FOR explain_me";
#SET DEBUG_SYNC="explain_other_got_thd SIGNAL killme WAIT_FOR killed";
#send EXPLAIN FOR CONNECTION $QID;
#
#connect (ck, localhost, root,, mysqltest1);
#connection ck;
#SET DEBUG_SYNC="now WAIT_FOR killme";
#SET DEBUG_SYNC="kill_thd_end SIGNAL killed";
#eval KILL $EID;
#
#connection ce;
#reap;
#
#connection cq;
#reap;

# Kill query being explained
# Test could be enabled when mysqltest will be able to 'send' two commands
# at once. Meanwhile, one could use 3 command line clients to run this test
# manually.
#connection ce;
#let $EID= `SELECT CONNECTION_ID()`;
#
#connection cq;
#let $QID= `SELECT CONNECTION_ID()`;
#SET DEBUG_SYNC="before_join_exec SIGNAL explain_me WAIT_FOR killed";
#send SELECT * FROM t1;
#
#connection ce;
#SET DEBUG_SYNC="now WAIT_FOR explain_me";
#SET DEBUG_SYNC="explain_other_got_thd SIGNAL killme WAIT_FOR killed";
#send EXPLAIN FOR CONNECTION $QID;
#
#connect (ck, localhost, root,, mysqltest1);
#connection ck;
#SET DEBUG_SYNC="now WAIT_FOR killme";
#SET DEBUG_SYNC="kill_thd_end SIGNAL killed";
#eval KILL $QID;
#
#connection ce;
#reap;
#
#connection cq;
#reap;


connection cq;

--echo Test error throwing
# Can''t explain EXPLAIN
let $point= before_set_plan;
let $err=ER_EXPLAIN_NOT_SUPPORTED;
let $query= EXPLAIN SELECT f1 FROM t1;
--source include/explain_other.inc

# Can''t explain statements not supported by regular EXPLAIN
let $point= before_join_exec;
let $query= SHOW FIELDS IN t1;
--source include/explain_other.inc

# Can''t be used in prepared statement
--error 1295
PREPARE stmt FROM 'EXPLAIN FOR CONNECTION 1';

# Can''t explain prepared statement
connection cq;
PREPARE stmt FROM 'SELECT * FROM t1';
connection ce;
let $query= EXECUTE stmt;
--source include/explain_other.inc

# Can''t be used from a procedure
--error 1235
CREATE PROCEDURE proc6369()
  EXPLAIN FOR CONNECTION 1;

# Can''t explain a statement in a procedure
CREATE PROCEDURE proc6369()
  SELECT * FROM t1;
let $point= before_join_exec;
let $query= CALL proc6369();
let $err= ER_EXPLAIN_NOT_SUPPORTED;
--source include/explain_other.inc
DROP PROCEDURE proc6369;

# Can''t explain itself
# Connection id could vary, hide it.
--disable_query_log
let $qid= `SELECT CONNECTION_ID()`;
--error ER_EXPLAIN_NOT_SUPPORTED
eval EXPLAIN FOR CONNECTION $qid;
--enable_query_log

# Explain for non-existing connection
--error ER_NO_SUCH_THREAD
EXPLAIN FOR CONNECTION 11111111;
--echo Explain queries with ready plan

let $err=0;
let $query= SELECT f1 FROM t1;
--source include/explain_other.inc

let $query= SELECT * FROM (SELECT * FROM t1) tt;
--source include/explain_other.inc

let $query= SELECT * FROM t1 WHERE f1 IN (SELECT * FROM t1);
--source include/explain_other.inc

let $query= SELECT * FROM t1 UNION ALL SELECT * FROM t1 ORDER BY 1;
--source include/explain_other.inc

let $format= json;
let $query= SELECT f1 FROM t1;
--source include/explain_other.inc

let $query= SELECT * FROM (SELECT * FROM t1) tt;
--source include/explain_other.inc

let $query= SELECT * FROM t1 WHERE f1 IN (SELECT * FROM t1);
--source include/explain_other.inc

let $query= SELECT * FROM t1 UNION ALL SELECT * FROM t1 ORDER BY 1;
--source include/explain_other.inc

# Show zero result cause
let $query= SELECT * FROM t1 WHERE 1=0;
--source include/explain_other.inc

--source include/turn_off_only_full_group_by.inc

let $query= SELECT * FROM t1
GROUP BY f1 NOT IN
 (SELECT f1+10 AS f2 FROM t1 AS t2
  GROUP BY f2 NOT IN
  (SELECT f1+100 AS f3 FROM t1 AS t3));
--source include/explain_other.inc

--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc

--echo Explain for non-select queries
let $point= planned_single_insert;
let $query= INSERT INTO t1 VALUES (3);
--source include/explain_other.inc

let $point= before_join_exec;
let $query= INSERT INTO t1 SELECT 4;
--source include/explain_other.inc

let $query= INSERT INTO t1 SELECT f1 + 4 FROM t1;
--source include/explain_other.inc

let $point= before_single_update;
let $query= UPDATE t1 SET f1=4 WHERE f1=4;
--source include/explain_other.inc

let $point= planned_single_update;
let $query= UPDATE t1 SET f1=4 WHERE f1=4;
--source include/explain_other.inc

CREATE TABLE t2 (f2 int);
let $query= UPDATE t1 SET f1=f1+0
ORDER BY f1 NOT IN
 (SELECT f1+10 AS f2 FROM t2
  GROUP BY f2 NOT IN
  (SELECT f1+100 AS f3 FROM t2 AS t3));
--source include/explain_other.inc

CREATE VIEW v1 AS SELECT t1.f1 FROM t1 JOIN t1 tt on t1.f1=tt.f1;
let $point= before_join_exec;
let $query= UPDATE v1 SET f1=5 WHERE f1=5;
--source include/explain_other.inc

let $point= planned_single_delete;
let $query= DELETE FROM t1 WHERE f1=4;
--source include/explain_other.inc

let $format= traditional;
let $point= planned_single_insert;
let $query= INSERT INTO t1 VALUES (3);
--source include/explain_other.inc

let $point= before_join_exec;
let $query= INSERT INTO t1 SELECT 4;
--source include/explain_other.inc

let $query= INSERT INTO t1 SELECT f1 + 4 FROM t1;
--source include/explain_other.inc

let $point= planned_single_update;
let $query= UPDATE t1 SET f1=4 WHERE f1=4;
--source include/explain_other.inc

let $point= before_join_exec;
let $query= UPDATE v1 SET f1=5 WHERE f1=5;
--source include/explain_other.inc

let $point= planned_single_delete;
let $query= DELETE FROM t1 WHERE f1=4;
--source include/explain_other.inc

--echo Explain queries with plan only partially ready
let $point= after_join_optimize;
let $format= traditional;
let $query= SELECT * FROM (SELECT * FROM t1 GROUP BY 1) tt;
--source include/explain_other.inc

let $query= SELECT * FROM t1 UNION ALL SELECT * FROM t1 ORDER BY
  (SELECT * FROM t1 LIMIT 1);
--source include/explain_other.inc

let $format= json;
let $query= SELECT * FROM (SELECT * FROM t1 GROUP BY 1) tt;
--source include/explain_other.inc

let $query= SELECT * FROM t1 UNION ALL SELECT * FROM t1 ORDER BY
  (SELECT * FROM t1 LIMIT 1);
--source include/explain_other.inc

--echo Ensure materialized derived still has plans after materialization
set @optimizer_switch_saved= @@optimizer_switch;
set optimizer_switch='derived_merge=off';
let $point= after_materialize_derived;
let $format= traditional;
let $query= SELECT * FROM (SELECT * FROM t1) tt;
--source include/explain_other.inc

let $format= json;
--source include/explain_other.inc
set optimizer_switch= @optimizer_switch_saved;

--echo WHERE subqueries, neither parent query nor subquery has plan
let $point= before_set_plan;
let $err=0;
let $query= SELECT t2.f1 FROM t1 STRAIGHT_JOIN t1 AS t2
WHERE t1.f1>(SELECT t3.f1 FROM t1 AS t3 LIMIT 1);
--source include/explain_other.inc

--echo the same, parent query has a plan
let $point= after_join_optimize;
let $err=0;
let $query= SELECT t2.f1 FROM t1 STRAIGHT_JOIN t1 AS t2
WHERE t1.f1>(SELECT t3.f1 FROM t1 AS t3 LIMIT 1);
--source include/explain_other.inc

--echo the same, both have a plan
let $point= before_reset_query_plan;
let $err=0;
let $query= SELECT t2.f1 FROM t1 STRAIGHT_JOIN t1 AS t2
WHERE t1.f1>(SELECT t3.f1 FROM t1 AS t3 LIMIT 1);
--source include/explain_other.inc
--echo To be compared with regular EXPLAIN:
eval EXPLAIN FORMAT=JSON $query;

let $query= SELECT 1 FROM t1 WHERE
ROW(f1,f1) >= ROW('1', (SELECT 1 FROM t1 WHERE f1 > 1234));
--source include/explain_other.inc
eval EXPLAIN FORMAT=JSON $query;

CREATE TABLE t3 (pk INT PRIMARY KEY);
INSERT INTO t3 SELECT DISTINCT * FROM t1;
let $query= SELECT * FROM t3 WHERE pk=(SELECT f1 FROM t1 limit 1);
--source include/explain_other.inc

connection default;

disconnect ce;
--echo # Check access rights checking
connection cq;
CREATE USER 'privtest'@'localhost';
GRANT SUPER ON *.* TO 'privtest'@'localhost';
GRANT SELECT ON mysqltest1.t2 TO 'privtest'@'localhost';
CREATE VIEW v2 AS SELECT * FROM t2;
GRANT SELECT,INSERT,UPDATE ON mysqltest1.v2 TO 'privtest'@'localhost';

connect (ce, localhost, privtest,, mysqltest1);

# Show ACCESS DENIED for queries of other users

let $point= before_join_exec;
let $format= traditional;
let $query= SELECT f1 FROM t1;
let $err= 1045;
--source include/explain_other.inc

SELECT f2 FROM v2;
let $query= SELECT f2 FROM v2;
let $err=1045;
--source include/explain_other.inc

disconnect cq;
connect (cq, localhost, privtest,, mysqltest1);

# Show EXPLAIN CAN''T BE ISSUED if user lacks EXPLAIN rights, but can
# execute statement. 

UPDATE v2 SET f2=1;
let $query= UPDATE v2 SET f2=1;
let $point= planned_single_update;
let $err=1345;
--source include/explain_other.inc

INSERT INTO v2 VALUES(1);
let $query= INSERT INTO v2 VALUES(1);
let $point= planned_single_insert;
let $err=1345;
--source include/explain_other.inc

INSERT INTO v2 SELECT 3 FROM t2;
let $query= INSERT INTO v2 SELECT 3 FROM t2;
let $point= before_join_exec;
let $err=1345;
--source include/explain_other.inc

SELECT f2 FROM v2;
--error 1345
EXPLAIN SELECT f2 FROM v2;

connection default;

DROP USER 'privtest'@localhost;
DROP VIEW v1, v2;
DROP TABLE t1, t2, t3;
disconnect ce;
disconnect cq;

# Reconnect as root for non-priv bugs tests
connect (ce, localhost, root,, mysqltest1);
connect (cq, localhost, root,, mysqltest1);
connection ce;
--echo #
--echo # Bug#14586538: EXPLAIN OTHER RESULT NOT MATCHING WITH EXPLAIN
--echo #

CREATE TABLE h2 (
pk int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (pk)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

insert into h2 values (1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE aa3 (
  col_int_key int(11) DEFAULT NULL,
  KEY col_int_key (col_int_key)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

let $point= before_join_exec;
let $err= 0;
let $query= SELECT alias1.pk AS field1 FROM h2 AS alias1 LEFT JOIN aa3 AS alias2 ON  alias1.pk =  alias2.col_int_key WHERE alias1.pk <> 9 GROUP BY field1 ORDER BY field1 LIMIT 1 OFFSET 3;
--eval EXPLAIN $query;
--echo Should show same # of rows as above
--source include/explain_other.inc

DROP TABLE h2, aa3;

--echo #
--echo # Bug#14591376: EXPLAIN OTHER RESULT NOT MATCHING WITH EXPLAIN - REF
--echo #

CREATE TABLE t1 (
  pk int(11),
  col_int_key int(11) DEFAULT NULL,
  KEY col_int_key (col_int_key)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO t1 VALUES (NULL,NULL);
INSERT INTO t1 VALUES (6,NULL);
INSERT INTO t1 VALUES (8,-1131610112);
INSERT INTO t1 VALUES (2,-1009057792);
INSERT INTO t1 VALUES (-1220345856,1);
INSERT INTO t1 VALUES (NULL,-185204736);

let $query= SELECT pk FROM t1 WHERE col_int_key= 8;
--source include/explain_other.inc

DROP TABLE t1;

--echo # From Bug#14463247 MYSQL CRASHES ON THE QUERY
--echo #

CREATE TABLE tbl1 (
  login int(11) NOT NULL,
  numb decimal(15,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (login),
  KEY numb (numb)
)  ;

CREATE TABLE tbl2 (
  login int(11) NOT NULL,
  cmd tinyint(4) NOT NULL,
  nump decimal(15,2) NOT NULL DEFAULT '0.00',
  KEY cmd (cmd),
  KEY login (login)
) ;

insert into tbl1 (login) values(1),(2);
insert ignore into tbl2 (login) values(1),(2);

-- disable_query_log
-- disable_result_log
ANALYZE TABLE tbl1, tbl2;
-- enable_result_log
-- enable_query_log

let $point= before_reset_query_plan;
let $query= 
SELECT 
  t1.login AS tlogin, 
  numb - 
  IFNULL((SELECT sum(nump) FROM tbl2 WHERE login=t1.login), 0) -
  IFNULL((SELECT sum(nump) FROM tbl2 WHERE login=t1.login), 0) as sp
FROM tbl1 t1, tbl2 t2 
WHERE t1.login=t2.login 
GROUP BY t1.login 
ORDER BY numb - IFNULL((SELECT sum(nump) FROM tbl2 WHERE login=t1.login), 0)
              - IFNULL((SELECT sum(nump) FROM tbl2 WHERE login=t1.login), 0)
;

--source include/explain_other.inc

let $format= json;
--source include/explain_other.inc

DROP TABLE tbl1, tbl2;

--echo # End of tests for WL#6369


--echo #
--echo # Bug#19822146 EXPLAIN FOR CONNECTION CRASHES IN 
--echo #              EXPLAIN_JOIN::EXPLAIN_QEP_TAB
--echo #

connection default;

create table t1(a char(10) charset latin1, key(a)) engine=innodb;
create table t2(a binary(10), key(a)) engine=innodb;

insert into t1 values('1'),('2'),('3'),('4');
insert into t2 values('1'),('2'),('s');
analyze table t1,t2;

let $query= select 1 from  t1 inner join t2 using(a);

--echo # Connection cq
connection cq;
--disable_query_log
let $QID= `SELECT CONNECTION_ID()`;
--enable_query_log
--echo # Let thread1 to suspend itself right before creating Quick object.
SET DEBUG_SYNC= 'quick_not_created SIGNAL ready_for_explain WAIT_FOR explained';
--send_eval $query;  

--echo # Connection ce
connection ce;
SET DEBUG_SYNC= 'now WAIT_FOR ready_for_explain';
SET DEBUG_SYNC= 'after_explain_other SIGNAL explained';
--disable_query_log
eval EXPLAIN FOR CONNECTION $QID; 
--enable_query_log

connection cq;
--echo connection: cq
--reap 

connection default;
SET DEBUG_SYNC= 'RESET';

--echo # Connection cq
connection cq;
--echo # Let thread1 to suspend itself right before the mutex section.
SET DEBUG_SYNC= 'quick_created_before_mutex SIGNAL ready_for_explain WAIT_FOR explained';
--send_eval $query;

--echo # Connection ce
connection ce;
SET DEBUG_SYNC= 'now WAIT_FOR ready_for_explain';
SET DEBUG_SYNC= 'after_explain_other SIGNAL explained';
--disable_query_log
eval EXPLAIN FOR CONNECTION $QID; 
--enable_query_log

connection cq;
--echo connection: cq
--reap

connection default;
SET DEBUG_SYNC= 'RESET';

--echo # Connection cq
connection cq;
--echo # Let thread1 to suspend itself right after quick object is droped.
SET DEBUG_SYNC= 'quick_droped_after_mutex SIGNAL ready_for_explain WAIT_FOR explained';
--send_eval $query;

--echo # Connection ce
connection ce;
SET DEBUG_SYNC= 'now WAIT_FOR ready_for_explain';
SET DEBUG_SYNC= 'after_explain_other SIGNAL explained';
--disable_query_log
eval EXPLAIN FOR CONNECTION $QID; 
--enable_query_log

connection cq;
--echo connection: cq
--reap

--echo # Connection default
connection default;
DROP TABLE t1, t2;
--echo # End of test Bug#19822146

--echo #
--echo # Bug#25422034 CRASH IN TABLE_LIST::QUERY_BLOCK_ID_FOR_EXPLAIN DURING EXPLAIN FOR CONNECTION
--echo #

connection default;
create table t1(a int);
insert into t1 values(1),(2);
let $query= select * from (select * from t1) dt;
let $point= derived_not_set;
let $format= traditional;
# Stmt is of a supported type, but cannot be explained while in resolution
let $err= ER_EXPLAIN_NOT_SUPPORTED;
--source include/explain_other.inc
let $err=0;
DROP TABLE t1;

SET DEBUG_SYNC= 'RESET';
USE test;
DROP DATABASE mysqltest1;
--echo # End of test

disconnect cq;
disconnect ce;