285 lines
8.4 KiB
Text
285 lines
8.4 KiB
Text
#
|
|
# Tests for the iteration-based FORMAT=JSON, that works with hypergraph
|
|
# optimizer
|
|
#
|
|
|
|
--source include/have_hypergraph.inc
|
|
--source include/elide_costs.inc
|
|
|
|
--echo #
|
|
--echo # Table scan, subquery, aggregates
|
|
CREATE TABLE t1 ( f1 INT PRIMARY KEY );
|
|
INSERT INTO t1 VALUES ( 1 );
|
|
INSERT INTO t1 VALUES ( 2 );
|
|
INSERT INTO t1 VALUES ( 3 );
|
|
ANALYZE TABLE t1;
|
|
|
|
EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE f1 = ( SELECT MIN(f1) FROM t1 AS i WHERE i.f1 > t1.f1 );
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE f1 = ( SELECT MIN(f1) FROM t1 AS i WHERE i.f1 > t1.f1 );
|
|
EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE f1 > ( SELECT f1 FROM t1 LIMIT 1 );
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE f1 > ( SELECT f1 FROM t1 LIMIT 1 );
|
|
drop table t1;
|
|
|
|
|
|
--echo #
|
|
--echo # Index range scan
|
|
create table t1 ( a int, b int, c int, d int, primary key(a,b));
|
|
insert into t1 values
|
|
(1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
|
|
(1,2,5,1), (1,3,1,2), (1,4,2,3),
|
|
(2,1,3,4), (2,3,4,5), (2,4,5,1),
|
|
(3,1,1,2), (3,2,2,3), (3,4,3,4),
|
|
(4,1,4,5), (4,2,5,1), (4,3,1,2);
|
|
explain format=TREE select * from t1 where a > 2;
|
|
explain format=JSON select * from t1 where a > 2;
|
|
drop table t1;
|
|
|
|
|
|
--echo # Index lookup. Nested loop join
|
|
set @old_opt_switch=@@optimizer_switch;
|
|
set optimizer_switch='firstmatch=off,materialization=off,duplicateweedout=off,loosescan=on';
|
|
CREATE TABLE t1 ( i INTEGER, PRIMARY KEY (i) );
|
|
CREATE TABLE t2 ( i INTEGER, INDEX i1 (i) );
|
|
INSERT INTO t1 VALUES (2), (3), (4), (5);
|
|
INSERT INTO t2 VALUES (1), (2), (3), (4);
|
|
ANALYZE TABLE t1, t2;
|
|
EXPLAIN format=TREE SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i FROM t2);
|
|
EXPLAIN format=JSON SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i FROM t2);
|
|
DROP TABLE t1,t2;
|
|
set optimizer_switch=@old_opt_switch;
|
|
|
|
|
|
--echo # Index lookup. Nested loop join. Filter.
|
|
CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB STATS_PERSISTENT=0;
|
|
INSERT INTO t1 VALUES (-100,1),(1,6);
|
|
CREATE TABLE t2 (
|
|
col_int_key INT,
|
|
col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT",
|
|
pk INT NOT NULL,
|
|
PRIMARY KEY (pk),
|
|
KEY (col_int_key)
|
|
) ENGINE=InnoDB STATS_PERSISTENT=0;
|
|
INSERT INTO t2 VALUES
|
|
(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8);
|
|
EXPLAIN FORMAT=TREE SELECT t1.*,t2.* FROM t1 straight_join t2
|
|
ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
|
|
EXPLAIN FORMAT=JSON SELECT t1.*,t2.* FROM t1 straight_join t2
|
|
ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
|
|
DROP TABLE t1,t2;
|
|
|
|
|
|
--echo # Group aggregates, hash join, sort.
|
|
CREATE TABLE t1 (
|
|
pk int NOT NULL AUTO_INCREMENT,
|
|
col_varchar varchar(1),
|
|
col_varchar_key varchar(1),
|
|
PRIMARY KEY (pk),
|
|
KEY idx_CC_col_varchar_key (col_varchar_key)
|
|
);
|
|
INSERT INTO t1 VALUES (1,'n','X'),(2,'Y','8'),(3,'R','l');
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=TREE SELECT
|
|
t1.col_varchar_key AS field1 FROM (t1, t1 as alias1)
|
|
WHERE NOT EXISTS( SELECT alias2.col_varchar_key FROM t1 AS alias2
|
|
WHERE alias2.col_varchar_key >= t1.col_varchar)
|
|
GROUP BY field1;
|
|
EXPLAIN FORMAT=JSON SELECT
|
|
t1.col_varchar_key AS field1 FROM (t1, t1 as alias1)
|
|
WHERE NOT EXISTS( SELECT alias2.col_varchar_key FROM t1 AS alias2
|
|
WHERE alias2.col_varchar_key >= t1.col_varchar)
|
|
GROUP BY field1;
|
|
drop table t1;
|
|
|
|
--echo # Information Schema
|
|
explain format=TREE select * from information_schema.engines e WHERE e.ENGINE="MyISAM";
|
|
explain format=JSON select * from information_schema.engines e WHERE e.ENGINE="MyISAM";
|
|
|
|
|
|
--echo # Materialize, window aggregates, Stream
|
|
CREATE TABLE t0 (i0 INTEGER);
|
|
INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
|
|
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, f3 INTEGER,
|
|
KEY(f1), KEY(f1,f2), KEY(f3));
|
|
INSERT INTO t1
|
|
SELECT i0, i0 + 10*i0,
|
|
i0 + 10*i0 + 100*i0
|
|
FROM t0 AS a0;
|
|
INSERT INTO t1
|
|
SELECT i0, i0 + 10*i0,
|
|
i0 + 10*i0 + 100*i0
|
|
FROM t0 AS a0;
|
|
INSERT INTO t1 VALUES (NULL, 1, 2);
|
|
INSERT INTO t1 VALUES (NULL, 1, 3);
|
|
ANALYZE TABLE t0, t1;
|
|
|
|
set sql_mode="";
|
|
EXPLAIN FORMAT=TREE SELECT * FROM
|
|
(SELECT f1, SUM(f2) OVER() FROM t1 GROUP BY f1) as dt
|
|
WHERE f1 > 2;
|
|
EXPLAIN FORMAT=JSON SELECT * FROM
|
|
(SELECT f1, SUM(f2) OVER() FROM t1 GROUP BY f1) as dt
|
|
WHERE f1 > 2;
|
|
|
|
--echo # Filter, nested loop
|
|
EXPLAIN FORMAT=TREE SELECT /*+ JOIN_ORDER(t0, dt) */ * FROM
|
|
(SELECT f1, f2, f3 FROM t1) as dt, t0
|
|
WHERE f1 > 3 and f2 < 50 and i0 > 3;
|
|
EXPLAIN FORMAT=JSON SELECT /*+ JOIN_ORDER(t0, dt) */ * FROM
|
|
(SELECT f1, f2, f3 FROM t1) as dt, t0
|
|
WHERE f1 > 3 and f2 < 50 and i0 > 3;
|
|
|
|
drop table t0, t1;
|
|
|
|
--echo # Explain analyze; Temporary table.
|
|
CREATE TABLE t1 (a INT NOT NULL, b CHAR(3) NOT NULL, PRIMARY KEY (a));
|
|
INSERT INTO t1 VALUES (1,'ABC'), (2,'EFG'), (3,'HIJ');
|
|
CREATE TABLE t2 (a INT NOT NULL,b CHAR(3) NOT NULL,PRIMARY KEY (a, b));
|
|
INSERT INTO t2 VALUES (1,'a'),(1,'b'),(3,'F');
|
|
ANALYZE TABLE t1, t2;
|
|
# Mask out all actual times
|
|
--replace_regex $elide_time
|
|
EXPLAIN analyze FORMAT=TREE SELECT t1.a, GROUP_CONCAT(t2.b) AS b FROM t1 LEFT JOIN t2 ON t1.a=t2.a GROUP BY t1.a ORDER BY t1.b;
|
|
EXPLAIN FORMAT=JSON SELECT t1.a, GROUP_CONCAT(t2.b) AS b FROM t1 LEFT JOIN t2 ON t1.a=t2.a GROUP BY t1.a ORDER BY t1.b;
|
|
drop table t1;
|
|
drop table t2;
|
|
|
|
|
|
--echo # Zero rows.
|
|
CREATE TABLE t1 (a INTEGER NOT NULL);
|
|
INSERT INTO t1 VALUES (1),(2),(3),(4);
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=TREE SELECT * FROM t1 AS a LEFT JOIN t1 AS b ON FALSE
|
|
LEFT JOIN t1 AS c ON b.a=c.a;
|
|
EXPLAIN FORMAT=JSON SELECT * FROM t1 AS a LEFT JOIN t1 AS b ON FALSE
|
|
LEFT JOIN t1 AS c ON b.a=c.a;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#35382014: Mysqld crash: Assertion `item_name.is_set()' failed
|
|
--echo # in sql/item.cc
|
|
--echo #
|
|
|
|
CREATE TABLE t (a INT);
|
|
--echo # Used to hit assertion in debug builds.
|
|
EXPLAIN FORMAT=JSON INTO @var
|
|
SELECT 1 + 1 AS x FROM t GROUP BY x WITH ROLLUP HAVING x = 1;
|
|
--echo # Used to show the GROUP BY clause as "group by ``".
|
|
SELECT JSON_UNQUOTE(JSON_EXTRACT(@var, '$.query')) AS query;
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#35537921 Contribution by Tencent:
|
|
--echo # explain format=tree lost the subquery in the hash join
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
|
|
|
|
let $query =
|
|
SELECT * FROM t1 x1 JOIN t1 x2 ON x2.a=
|
|
(SELECT MIN(x3.a) FROM t1 x3 WHERE x1.a=x3.a);
|
|
|
|
--replace_regex $elide_costs
|
|
--eval EXPLAIN FORMAT=TREE $query
|
|
|
|
--eval EXPLAIN FORMAT=JSON $query
|
|
|
|
let $query =
|
|
SELECT * FROM t1 x1 JOIN t1 x2 ON x2.a<
|
|
(SELECT MIN(x3.a) FROM t1 x3 WHERE x1.a=x3.a);
|
|
|
|
--replace_regex $elide_costs
|
|
--eval EXPLAIN FORMAT=TREE $query
|
|
|
|
--eval EXPLAIN FORMAT=JSON $query
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#34569685 No explain output for subquery
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
|
|
|
|
ANALYZE TABLE t1;
|
|
|
|
let $query =
|
|
SELECT LAST_VALUE((SELECT x1.a FROM t1))
|
|
OVER (PARTITION BY b) FROM t1 x1;
|
|
|
|
--replace_regex $elide_costs_and_rows
|
|
--eval EXPLAIN FORMAT=TREE $query
|
|
|
|
--eval EXPLAIN FORMAT=JSON $query
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#34727172 EXPLAIN FORMAT=JSON returns invalid JSON
|
|
--echo # on INSERT statements with hypergraph
|
|
--echo #
|
|
|
|
CREATE TABLE t (i INT);
|
|
INSERT INTO t VALUES (1), (2), (3);
|
|
|
|
ANALYZE TABLE t;
|
|
|
|
EXPLAIN FORMAT=JSON INSERT INTO t VALUES (4), (5), (6);
|
|
EXPLAIN FORMAT=JSON REPLACE INTO t VALUES (7), (8), (9);
|
|
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#36134568 Add query type to iterator-based EXPLAIN FORMAT=JSON
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (i1 INT PRIMARY KEY, i2 INT);
|
|
CREATE TABLE t2 (i3 INT, i4 INT);
|
|
INSERT INTO t1 VALUES (1,2), (2,3), (3,4);
|
|
INSERT INTO t2 SELECT i2, i1 FROM t1;
|
|
ANALYZE TABLE t1, t2;
|
|
|
|
EXPLAIN FORMAT=JSON INTO @v1 SELECT * FROM t1 JOIN t2 ON i1 = i3 WHERE i2 = 2;
|
|
SELECT JSON_EXTRACT(@v1, '$.query_type') = 'select';
|
|
|
|
SET @v1 = NULL;
|
|
|
|
EXPLAIN FORMAT=JSON INTO @v1 SELECT * FROM t1;
|
|
SELECT JSON_EXTRACT(@v1, '$.query_type') = 'select';
|
|
|
|
SET @v1 = NULL;
|
|
|
|
EXPLAIN FORMAT=JSON INTO @v1 INSERT INTO t1 VALUES (4,5);
|
|
SELECT JSON_EXTRACT(@v1, '$.query_type') = 'insert';
|
|
|
|
SET @v1 = NULL;
|
|
|
|
EXPLAIN FORMAT=JSON INTO @v1 INSERT INTO t1 SELECT * FROM t2;
|
|
SELECT JSON_EXTRACT(@v1, '$.query_type') = 'insert';
|
|
|
|
SET @v1 = NULL;
|
|
|
|
EXPLAIN FORMAT=JSON INTO @v1 UPDATE t1 SET i2 = i2 + 1 WHERE i1 = 1;
|
|
SELECT JSON_EXTRACT(@v1, '$.query_type') = 'update';
|
|
|
|
SET @v1 = NULL;
|
|
|
|
EXPLAIN FORMAT=JSON INTO @v1 REPLACE t1 SELECT * FROM t2;
|
|
SELECT JSON_EXTRACT(@v1, '$.query_type') = 'replace';
|
|
|
|
SET @v1 = NULL;
|
|
|
|
EXPLAIN FORMAT=JSON INTO @v1 DELETE FROM t1;
|
|
SELECT JSON_EXTRACT(@v1, '$.query_type') = 'delete';
|
|
|
|
SET @v1 = NULL;
|
|
|
|
EXPLAIN FORMAT=JSON INTO @v1 UPDATE t1, t2 SET i1 = i1 - 1, i3 = i3 + 1;
|
|
SELECT JSON_EXTRACT(@v1, '$.query_type') = 'update';
|
|
|
|
SET @v1 = NULL;
|
|
|
|
EXPLAIN FORMAT=JSON INTO @v1 DELETE t1, t2 FROM t1, t2;
|
|
SELECT JSON_EXTRACT(@v1, '$.query_type') = 'delete';
|
|
|
|
DROP TABLE t1, t2;
|
|
SET @v1=NULL;
|