781 lines
26 KiB
Text
781 lines
26 KiB
Text
#
|
|
# Test of EXPLAIN FORMAT=tree, and more specifically, the transformation from
|
|
# the existing optimizer structures to the new executor. It contains a number
|
|
# of tests for various parts of the transformation, as well as some corner
|
|
# cases that demonstrate odd or unexpected behavior.
|
|
#
|
|
|
|
set optimizer_switch='batched_key_access=off,block_nested_loop=off,mrr_cost_based=off';
|
|
|
|
--source include/not_hypergraph.inc # Everything in this test depends heavily on the plans chosen.
|
|
--source include/elide_costs.inc
|
|
|
|
|
|
# Basic table scan.
|
|
CREATE TABLE t1 ( f1 INT );
|
|
INSERT INTO t1 VALUES ( 1 );
|
|
INSERT INTO t1 VALUES ( 2 );
|
|
INSERT INTO t1 VALUES ( 3 );
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
# Basic INSERT.
|
|
CREATE TABLE t1 ( f1 INT );
|
|
CREATE TABLE t2 ( f1 INT );
|
|
EXPLAIN FORMAT=tree INSERT INTO t2 SELECT * FROM t1;
|
|
DROP TABLE t1, t2;
|
|
|
|
# Multi-table UPDATE.
|
|
CREATE TABLE t1 ( f1 INT );
|
|
CREATE TABLE t2 ( f2 INT );
|
|
EXPLAIN FORMAT=tree UPDATE t1, t2 SET f1=f1+2, f2=f2+1 WHERE f1 = f2;
|
|
DROP TABLE t1, t2;
|
|
|
|
# Multi-table DELETE.
|
|
CREATE TABLE t1 ( f1 INT );
|
|
CREATE TABLE t2 ( f2 INT );
|
|
EXPLAIN FORMAT=tree DELETE t1, t2 FROM t1, t2;
|
|
DROP TABLE t1, t2;
|
|
|
|
# Subquery in SELECT list.
|
|
CREATE TABLE t1 ( f1 INT );
|
|
INSERT INTO t1 VALUES ( 1 );
|
|
INSERT INTO t1 VALUES ( 2 );
|
|
INSERT INTO t1 VALUES ( 3 );
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT f1, (SELECT MIN(f1) FROM t1 i WHERE i.f1 > t1.f1) < 3 FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
# Index scan.
|
|
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 ORDER BY f1 DESC;
|
|
DROP TABLE t1;
|
|
|
|
# Various form of grouping and aggregation.
|
|
CREATE TABLE t1 ( f1 INT, INDEX ( f1 ) );
|
|
INSERT INTO t1 VALUES ( 1 );
|
|
INSERT INTO t1 VALUES ( 2 );
|
|
INSERT INTO t1 VALUES ( 3 );
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT SUM(f1) FROM t1;
|
|
EXPLAIN FORMAT=tree SELECT f1 FROM t1 GROUP BY f1;
|
|
EXPLAIN FORMAT=tree SELECT f1,COUNT(*) FROM t1 GROUP BY f1;
|
|
EXPLAIN FORMAT=tree SELECT f1,COUNT(*) FROM t1 GROUP BY f1 WITH ROLLUP;
|
|
DROP TABLE t1;
|
|
|
|
# Only const tables.
|
|
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=2;
|
|
DROP TABLE t1;
|
|
|
|
# A join (against a const table).
|
|
CREATE TABLE t1 ( f1 INT PRIMARY KEY );
|
|
INSERT INTO t1 VALUES ( 1 );
|
|
INSERT INTO t1 VALUES ( 2 );
|
|
INSERT INTO t1 VALUES ( 3 );
|
|
CREATE TABLE t2 ( f1 INT PRIMARY KEY );
|
|
INSERT INTO t2 SELECT * FROM t1;
|
|
ANALYZE TABLE t1, t2;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f1 + 2 AND t2.f1 = 3;
|
|
DROP TABLE t1, t2;
|
|
|
|
# Right join against a const table.
|
|
CREATE TABLE t1 ( f1 INT PRIMARY KEY );
|
|
INSERT INTO t1 VALUES ( 1 );
|
|
INSERT INTO t1 VALUES ( 2 );
|
|
INSERT INTO t1 VALUES ( 3 );
|
|
CREATE TABLE t2 AS SELECT * FROM t1;
|
|
ANALYZE TABLE t1, t2;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 USING (f1) WHERE t1.f1=2;
|
|
DROP TABLE t1, t2;
|
|
|
|
# Demonstrate that filters are put on the correct point with nested outer joins.
|
|
# In particular, the isnull(t3.c) should be placed between the two left join iterators.
|
|
CREATE TABLE t1 ( a INT );
|
|
CREATE TABLE t2 ( a INT );
|
|
CREATE TABLE t3 ( a INT, b INT );
|
|
INSERT INTO t1 VALUES ( 1 );
|
|
INSERT INTO t2 VALUES ( 3 );
|
|
INSERT INTO t3 VALUES ( 2, 0 );
|
|
ANALYZE TABLE t1, t2, t3;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 USING (a) ) ON t3.b IS NULL;
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
# Anti-join (due to f1 being not nullable, yet asking for NULL).
|
|
CREATE TABLE t1 ( f1 INT PRIMARY KEY );
|
|
INSERT INTO t1 VALUES ( 1 );
|
|
INSERT INTO t1 VALUES ( 2 );
|
|
INSERT INTO t1 VALUES ( 3 );
|
|
CREATE TABLE t2 AS SELECT * FROM t1;
|
|
ANALYZE TABLE t1, t2;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 USING (f1) WHERE t2.f1 IS NULL;
|
|
DROP TABLE t1, t2;
|
|
|
|
# Adding limit on the right side of joins.
|
|
CREATE TABLE t1 (a INT, b INT);
|
|
CREATE TABLE t2 (a INT, c INT, KEY(a));
|
|
INSERT INTO t1 VALUES (1, 1), (2, 2);
|
|
INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5),
|
|
(2, 1), (2, 2), (2, 3), (2, 4), (2, 5),
|
|
(3, 1), (3, 2), (3, 3), (3, 4), (3, 5),
|
|
(4, 1), (4, 2), (4, 3), (4, 4), (4, 5);
|
|
ANALYZE TABLE t1, t2;
|
|
FLUSH STATUS;
|
|
EXPLAIN FORMAT=tree SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3;
|
|
DROP TABLE t1, t2;
|
|
|
|
# Sort.
|
|
CREATE TABLE t1 ( f1 INT );
|
|
INSERT INTO t1 VALUES ( 1 );
|
|
INSERT INTO t1 VALUES ( 2 );
|
|
INSERT INTO t1 VALUES ( 3 );
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1 ORDER BY f1 DESC;
|
|
DROP TABLE t1;
|
|
|
|
# Pointless materialization.
|
|
# Also demonstrates printout of sorting without addon fields.
|
|
CREATE TABLE t1 ( a LONGBLOB, b INT );
|
|
INSERT INTO t1 VALUES ('a', 0);
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT 0 AS foo FROM t1 WHERE 0 = (SELECT group_concat(b) FROM t1 t GROUP BY t1.a) ;
|
|
DROP TABLE t1;
|
|
|
|
# A case where we use hash deduplication for a temporary table (due to the blob).
|
|
# We don't show explicitly that it's using hash, though.
|
|
CREATE TABLE t1 (a text, b varchar(10));
|
|
INSERT INTO t1 VALUES (repeat('1', 1300),'one'), (repeat('1', 1300),'two');
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT SUBSTRING(a,1,10), LENGTH(a) FROM t1 GROUP BY a;
|
|
DROP TABLE t1;
|
|
|
|
# Double limit, with unique on the way into materialization.
|
|
CREATE TABLE t1 ( f1 VARCHAR(100) );
|
|
INSERT INTO t1 VALUES ('abc');
|
|
INSERT INTO t1 VALUES ('abc');
|
|
INSERT INTO t1 VALUES ('def');
|
|
INSERT INTO t1 VALUES ('def');
|
|
INSERT INTO t1 VALUES ('ghi');
|
|
ANALYZE TABLE t1;
|
|
--replace_regex /cost=1.76..2.27/cost=1.76..2.28/
|
|
EXPLAIN FORMAT=tree SELECT DISTINCT f1 FROM t1 LIMIT 2;
|
|
DROP TABLE t1;
|
|
|
|
# Pointless extra limit (pushed down into the temporary table, but not removed on the outside).
|
|
CREATE TABLE t1 (a int PRIMARY KEY);
|
|
INSERT INTO t1 values (1), (2);
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1;
|
|
DROP TABLE t1;
|
|
|
|
# Double materialization; one of them contains an unique, the other one is
|
|
# pretty much useless.
|
|
CREATE TABLE t1 (a INTEGER, b INTEGER);
|
|
INSERT INTO t1 VALUES (1,3), (2,4), (1,5),
|
|
(1,3), (2,1), (1,5), (1,7), (3,1),
|
|
(3,2), (3,1), (2,4);
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT DISTINCT (COUNT(DISTINCT b) + 1) AS c FROM t1 GROUP BY a;
|
|
DROP TABLE t1;
|
|
|
|
# A subquery within WHERE. Test both dependent and independent queries.
|
|
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=tree SELECT * FROM t1 WHERE f1 > ( SELECT f1 FROM t1 LIMIT 1 );
|
|
DROP TABLE t1;
|
|
|
|
# A subquery in the SELECT list of a condition subquery.
|
|
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 ( SELECT MIN(f1) FROM t1 AS ii WHERE ii.f1 > t1.f1 ) > i.f1 FROM t1 AS i ) ;
|
|
DROP TABLE t1;
|
|
|
|
# Deeply nested subqueries in the SELECT list.
|
|
# The addition is to keep the optimizer from flattening out the queries.
|
|
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 ( SELECT ( SELECT ( SELECT MIN(f1) FROM t1 i WHERE i.f1 > t1.f1 ) + 1 ) + 1 ) FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
# A subquery which is pushed to a temporary table due to ordering.
|
|
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 ( SELECT f1 FROM t1 AS inner_t1 WHERE inner_t1.f1 > t1.f1 LIMIT 1 ) AS tmp1 FROM t1 ORDER BY tmp1;
|
|
DROP TABLE t1;
|
|
|
|
# Condition pushed before filesort.
|
|
CREATE TABLE t1 (a INTEGER, b INTEGER);
|
|
INSERT INTO t1 VALUES (1,3), (2,4), (1,5),
|
|
(1,3), (2,1), (1,5), (1,7), (3,1),
|
|
(3,2), (3,1), (2,4);
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1 WHERE a > 3 ORDER BY b;
|
|
DROP TABLE t1;
|
|
|
|
# Single-table modifications don't have a JOIN, so they have a simple explain.
|
|
CREATE TABLE t1 (i INT);
|
|
EXPLAIN INSERT INTO t1 VALUES (10);
|
|
EXPLAIN FORMAT=tree INSERT INTO t1 VALUES (10);
|
|
EXPLAIN REPLACE INTO t1 VALUES (10);
|
|
EXPLAIN FORMAT=tree REPLACE INTO t1 VALUES (10);
|
|
DROP TABLE t1;
|
|
|
|
# Limit pushed into filesort.
|
|
CREATE TABLE t1 (a INTEGER, b INTEGER);
|
|
INSERT INTO t1 VALUES (1,3), (2,4), (1,5),
|
|
(1,3), (2,1), (1,5), (1,7), (3,1),
|
|
(3,2), (3,1), (2,4);
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1 ORDER BY b LIMIT 3;
|
|
DROP TABLE t1;
|
|
|
|
# LATERAL, with two different invalidators. One of them is pushed up above
|
|
# the join, because it's an outer join.
|
|
CREATE TABLE t1 ( a INTEGER );
|
|
CREATE TABLE t2 ( a INTEGER );
|
|
CREATE TABLE t3 ( a INTEGER );
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN t2 USING ( a ),
|
|
LATERAL ( SELECT * FROM t3 WHERE t3.a = t2.a LIMIT 1 ) t3d,
|
|
LATERAL ( SELECT * FROM t3 WHERE t3.a > t1.a LIMIT 1 ) t4d;
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
# LATERAL, with a join that's pushed the same way as previously, but not
|
|
# beyond the join involving t1.
|
|
CREATE TABLE t1 ( a INTEGER );
|
|
CREATE TABLE t2 ( a INTEGER );
|
|
CREATE TABLE t3 ( a INTEGER );
|
|
CREATE TABLE t4 ( a INTEGER );
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1 LEFT JOIN (
|
|
t2 LEFT JOIN t3 USING ( a ) CROSS JOIN
|
|
LATERAL ( SELECT * FROM t4 WHERE t4.a = t3.a LIMIT 1 ) t4d
|
|
) ON t1.a = t4d.a;
|
|
DROP TABLE t1, t2, t3, t4;
|
|
|
|
# Demontrate that we can print the sub-iterator for the WHERE subquery
|
|
# that originally came from a table path.
|
|
CREATE TABLE t1 ( f1 INTEGER );
|
|
EXPLAIN FORMAT=TREE SELECT * FROM ( SELECT * FROM t1 LIMIT 2 OFFSET 1 ) AS alias1
|
|
WHERE f1 <= ANY ( SELECT f1 FROM t1 ) ORDER BY f1;
|
|
DROP TABLE t1;
|
|
|
|
# A non-recursive CTE. The case of multiple uses is tested in derived_correlated.
|
|
CREATE TABLE t1 ( f1 INT );
|
|
CREATE TABLE t2 ( f1 INT );
|
|
EXPLAIN format=tree WITH my_cte AS ( SELECT * FROM t1 LIMIT 3 ) SELECT * FROM my_cte, t2;
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
|
|
# A simple semijoin.
|
|
CREATE TABLE t1 (i INTEGER);
|
|
CREATE TABLE t2 (i INTEGER);
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1 WHERE (t1.i) IN (SELECT t2.i FROM t2);
|
|
DROP TABLE t1, t2;
|
|
|
|
# A semijoin of two const tables against a multi-table join.
|
|
CREATE TABLE t1 (pk INTEGER PRIMARY KEY, i INTEGER);
|
|
CREATE TABLE t2 (pk INTEGER PRIMARY KEY, i INTEGER);
|
|
CREATE TABLE t3 (i INTEGER);
|
|
CREATE TABLE t4 (i INTEGER);
|
|
INSERT INTO t1 VALUES (2, 3);
|
|
INSERT INTO t2 VALUES (4, 5);
|
|
ANALYZE TABLE t1, t2;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1,t2 WHERE (t1.i) IN (SELECT t3.i FROM t3,t4) AND t1.pk = 2 AND t2.pk = 4;
|
|
DROP TABLE t1, t2, t3, t4;
|
|
|
|
# Conditions should be pushed up above outer joins, but not above semijoins.
|
|
CREATE TABLE t1 (i INTEGER);
|
|
CREATE TABLE t2 (i INTEGER);
|
|
CREATE TABLE t3 (i INTEGER, j INTEGER);
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1 WHERE t1.i IN ( SELECT i FROM t2 LEFT JOIN t3 USING (i) WHERE t3.j = 1234 OR t3.j IS NULL );
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
# Semijoin by materialization; there's a materialization with deduplication,
|
|
# which allows us to run the rest as a normal join.
|
|
set @old_opt_switch=@@optimizer_switch;
|
|
set optimizer_switch='firstmatch=off';
|
|
CREATE TABLE t1 ( a INTEGER, b INTEGER );
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
|
|
DROP TABLE t1;
|
|
set @@optimizer_switch=@old_opt_switch;
|
|
|
|
# Same, for a NOT IN query (which then becomes an antijoin).
|
|
set @old_opt_switch=@@optimizer_switch;
|
|
set optimizer_switch='firstmatch=off';
|
|
CREATE TABLE t1 ( a INTEGER NOT NULL, b INTEGER NOT NULL );
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT a FROM t1 WHERE a NOT IN ( SELECT b FROM t1 WHERE b > 2 );
|
|
DROP TABLE t1;
|
|
set @@optimizer_switch=@old_opt_switch;
|
|
|
|
# Demonstrate that if there's aggregation within the IN, it just becomes
|
|
# an EXISTS clause instead of the strategy above. (If this changes, one would
|
|
# need to modify the setup of MaterializeIterator for semijoin materialization.)
|
|
set @old_opt_switch=@@optimizer_switch;
|
|
set optimizer_switch='firstmatch=off';
|
|
CREATE TABLE t1 ( a INTEGER, b INTEGER );
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT a FROM t1 WHERE a = ANY ( SELECT MAX(a) FROM t1 WHERE b = 2 );
|
|
DROP TABLE t1;
|
|
set @@optimizer_switch=@old_opt_switch;
|
|
|
|
# Semijoin by duplicate weedout.
|
|
set @old_opt_switch=@@optimizer_switch;
|
|
set optimizer_switch='firstmatch=off,materialization=off,loosescan=off';
|
|
CREATE TABLE t1 ( i INTEGER );
|
|
CREATE TABLE t2 ( i INTEGER );
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
INSERT INTO t2 VALUES (2);
|
|
ANALYZE TABLE t1, t2;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1 WHERE (t1.i) IN (SELECT t2.i FROM t2);
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
set @@optimizer_switch=@old_opt_switch;
|
|
|
|
# Confluent weedout becomes LIMIT 1.
|
|
#
|
|
# The only difference between this and the previous query is that t2 has
|
|
# somewhat more data, which flips the join order. This means that the
|
|
# weedout can go over t2 only instead of both tables, which makes it a
|
|
# confluent weedout, which we rewrite to LIMIT 1.
|
|
set @old_opt_switch=@@optimizer_switch;
|
|
set optimizer_switch='firstmatch=off,materialization=off,loosescan=off';
|
|
CREATE TABLE t1 ( i INTEGER );
|
|
CREATE TABLE t2 ( i INTEGER );
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
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);
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
set @@optimizer_switch=@old_opt_switch;
|
|
|
|
# A simple single-table loose scan.
|
|
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);
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
set @@optimizer_switch=@old_opt_switch;
|
|
|
|
--echo #
|
|
--echo # Bug#29904996 EXPLAIN FORMAT=TREE PRINTS OUT HIDDEN COLUMN NAME INSTEAD
|
|
--echo # OF INDEXED EXPRESSION
|
|
--echo #
|
|
CREATE TABLE t1 (
|
|
col_int_key INTEGER,
|
|
col_json JSON,
|
|
KEY mv_idx ((CAST(col_json->'$[*]' AS CHAR(40) ARRAY)))
|
|
);
|
|
|
|
CREATE TABLE t2 (col_int INTEGER);
|
|
|
|
--echo # See that we print the indexed expression, and not the hidden column
|
|
--echo # name.
|
|
EXPLAIN FORMAT=tree SELECT /*+ NO_BNL(t1, t2) */ * FROM t2
|
|
JOIN t1 ON 1 WHERE (CAST("1" AS JSON) MEMBER OF( t1.col_json->'$[*]'));
|
|
|
|
DROP TABLE t1, t2;
|
|
|
|
# EXPLAIN ANALYZE of a simple query.
|
|
CREATE TABLE t1 (a INTEGER, b INTEGER, PRIMARY KEY ( a ));
|
|
INSERT INTO t1 VALUES (1,3), (2,4), (3,5);
|
|
ANALYZE TABLE t1;
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE SELECT * FROM t1 AS a JOIN t1 AS b ON a.a=b.b ORDER BY a.b+b.a LIMIT 3;
|
|
|
|
# Same, but with some parts that are never executed.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE SELECT * FROM t1 AS a, t1 AS b WHERE a.b=500;
|
|
DROP TABLE t1;
|
|
|
|
# EXPLAIN ANALYZE FOR CONNECTION makes no sense.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
EXPLAIN ANALYZE FOR CONNECTION 1;
|
|
|
|
# EXPLAIN ANALYZE with FORMAT= is supported, but currently only for FORMAT=TREE.
|
|
--error ER_NOT_SUPPORTED_YET
|
|
EXPLAIN ANALYZE FORMAT=TRADITIONAL SELECT 1;
|
|
--error ER_NOT_SUPPORTED_YET
|
|
EXPLAIN ANALYZE FORMAT=JSON SELECT 1;
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE FORMAT=TREE SELECT 1;
|
|
|
|
# An information_schema table.
|
|
EXPLAIN FORMAT=tree SELECT * FROM INFORMATION_SCHEMA.ENGINES;
|
|
|
|
# Union materialization directly into a temporary table (no double materialization).
|
|
CREATE TABLE t1 ( f1 INT PRIMARY KEY );
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
ANALYZE TABLE t1;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1, ( SELECT f1 FROM t1 UNION SELECT f1 + 10 FROM t1 ) d1;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t1, ( SELECT f1 FROM t1 UNION ALL SELECT f1 + 10 FROM t1 ) d1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug #30444266: EXPLAIN ANALYZE DOES NOT EXECUTE THE SUBQUERIES IN THE SELECT LIST
|
|
--echo #
|
|
CREATE TABLE t1 ( f1 INTEGER );
|
|
INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
ANALYZE TABLE t1;
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE SELECT f1, ( SELECT COUNT(*) FROM t1 AS inner_t1 WHERE inner_t1.f1 < t1.f1 ) FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#17978975 EXPLAIN FOR MULTI-TABLE UPDATE/DELETE DOES NOT MENTION TEMPORARY TABLE
|
|
--echo #
|
|
|
|
CREATE TABLE t1(a INT, b INT);
|
|
CREATE TABLE t2(a INT, b INT);
|
|
ANALYZE TABLE t1,t2;
|
|
# Show when the table is updated on the fly, or with a tmp table:
|
|
EXPLAIN FORMAT=TREE UPDATE t1,t2 SET t1.a=1,t2.b=1;
|
|
EXPLAIN FORMAT=TREE UPDATE t1,t2 SET t1.a=1,t2.b=1 WHERE t2.a=t1.a;
|
|
EXPLAIN FORMAT=TREE DELETE t1.*,t2.* FROM t1,t2;
|
|
EXPLAIN FORMAT=TREE DELETE t1.*,t1bis.* FROM t1, t1 AS t1bis;
|
|
DROP TABLE t1,t2;
|
|
|
|
--echo #
|
|
--echo # Bug #30941336: EXPLAIN ANAYLZE SELECT COUNT(*) FROM MYSQL.USER COREDUMP UNDER DEBUG VERSION
|
|
--echo #
|
|
|
|
CREATE TABLE t1 ( a INTEGER );
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE SELECT COUNT(*) FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug #31559245: REGRESSION: CRASH/ASSERT IN SUBSELECT_HASH_SJ_ENGINE::ROOT_ACCESS_PATH
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INTEGER);
|
|
EXPLAIN FORMAT=tree SELECT (1 IN (SELECT 1 FROM t1)) WHERE FALSE;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug #32688540: MATERIALIZE ITERATORS HAVE SKEWED TIMINGS FROM SKIPPED MATERIALIZATIONS
|
|
--echo #
|
|
|
|
# The important thing here is that the Materialize node has rows=3 and loops=1,
|
|
# because even though it's been asked to materialize five times, it only materialized
|
|
# one of them. The LIMIT is to avoid temporary table merging.
|
|
CREATE TABLE t1 (a INTEGER);
|
|
INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
|
|
ANALYZE TABLE t1;
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE SELECT * FROM t1 LEFT JOIN ( SELECT * FROM t1 ORDER BY t1.a LIMIT 3 ) d1 ON TRUE;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug #32915233: RECENT REGRESSION:ASSERTION `EXPLAIN.CHILDREN.SIZE() <= 1' FAILED.
|
|
--echo #
|
|
|
|
# Sets up an AlternativeIterator in the table access path.
|
|
CREATE TABLE t1 (a int);
|
|
SET sql_mode='';
|
|
EXPLAIN FORMAT=tree SELECT a IN (SELECT a FROM (table t1) AS d) FROM t1 GROUP BY (@b:=5);
|
|
SET sql_mode=DEFAULT;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug #33030136: EXPLAIN: ASSERTION `CURRENT_THD->LEX->USING_HYPERGRAPH_OPTIMIZER' FAILED.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INTEGER);
|
|
|
|
EXPLAIN FORMAT=tree
|
|
(SELECT 1 FROM t1)
|
|
UNION ALL
|
|
SELECT 1 IN (SELECT 1 FROM t1) FROM t1 WHERE 1=2;
|
|
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Demonstrates printing an index range scan with too many ranges to print
|
|
# (more than three). The pushed index condition is redundant.
|
|
#
|
|
|
|
CREATE TABLE t1 (pk INTEGER NOT NULL, a INTEGER, b INTEGER, KEY (a), KEY (b), PRIMARY KEY (pk));
|
|
ANALYZE TABLE t1;
|
|
|
|
EXPLAIN FORMAT=tree
|
|
SELECT * FROM t1 WHERE a IN (2,4,6,8,10);
|
|
|
|
# Also, with printing of a reverse-scan iterator.
|
|
EXPLAIN FORMAT=tree
|
|
SELECT * FROM t1 WHERE a IN (2,4,6,8,10) ORDER BY a DESC;
|
|
|
|
#
|
|
# EXPLAIN ANALYZE on an IndexMergeIterator. Also demonstrates printing of
|
|
# scanning over clustered primary keys. (The filter is redundant.)
|
|
#
|
|
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE
|
|
SELECT /*+ INDEX_MERGE(t1) */ * FROM t1 WHERE pk > 1 OR a > 2 OR b > 3;
|
|
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Demonstrates printing index skip scan.
|
|
#
|
|
|
|
CREATE TABLE t1 (a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER, KEY idx1 (a,b,c,d,e));
|
|
ANALYZE TABLE t1;
|
|
|
|
EXPLAIN FORMAT=tree
|
|
SELECT /*+ SKIP_SCAN(t1) */ * FROM t1 WHERE a IN (2,4,6,8,10) AND b=5 AND e BETWEEN 100 AND 200;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#33343948: Multi-valued index ranges are printed as binary
|
|
--echo #
|
|
|
|
# Demonstrates printing of range scans over an index on a string
|
|
# column with a binary collation. Range values used to be printed as a
|
|
# hex strings.
|
|
|
|
CREATE TABLE t(a VARCHAR(10) COLLATE utf8mb4_0900_bin, KEY (a));
|
|
INSERT INTO t VALUES ('abc'), ('def'), ('ghi');
|
|
ANALYZE TABLE t;
|
|
EXPLAIN FORMAT=tree SELECT * FROM t WHERE a > 'abc' AND a < 'ghi';
|
|
# Also verify that special characters are escaped.
|
|
EXPLAIN FORMAT=tree
|
|
SELECT * FROM t WHERE a > 'abc''def' AND a < CONCAT('z', UNHEX('090A1A5C0D'));
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#33364732: Preliminary fixes for WL#14673
|
|
--echo #
|
|
|
|
# Demonstrates printing of DELETE with an impossible WHERE clause.
|
|
|
|
CREATE TABLE t(x INTEGER);
|
|
INSERT INTO t VALUES (1), (2), (3);
|
|
ANALYZE TABLE t;
|
|
EXPLAIN FORMAT=TREE DELETE t FROM t WHERE 0=1;
|
|
|
|
# Demonstrates EXPLAIN ANALYZE on a DELETE statement.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE DELETE t1 FROM t t1, t t2 WHERE t1.x = t2.x + 1;
|
|
# EXPLAIN ANALYZE DELETE should delete rows. Currently, it does not.
|
|
# (And EXPLAIN ANALYZE UPDATE/INSERT don't modify the table either.)
|
|
# Tracked in bug#33962407.
|
|
--sorted_result
|
|
SELECT * FROM t;
|
|
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # WL#14672: Enable the hypergraph optimizer for UPDATE
|
|
--echo #
|
|
|
|
CREATE TABLE t(x INTEGER, y INTEGER);
|
|
INSERT INTO t VALUES (1, 2), (2, 3), (3, 4);
|
|
ANALYZE TABLE t;
|
|
|
|
# Demonstrates EXPLAIN ANALYZE on an UPDATE statement.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE UPDATE t t1, t t2 SET t1.x = t2.y WHERE t1.x = t2.x;
|
|
# EXPLAIN ANALYZE UPDATE should update rows. Currently, it does not.
|
|
# (And EXPLAIN ANALYZE INSERT/DELETE don't modify the table either.)
|
|
# Tracked in bug#33962407.
|
|
--sorted_result
|
|
SELECT * FROM t;
|
|
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#33905399 Missing profiling data for CTE
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
|
|
INSERT INTO t1 VALUES (1,1);
|
|
ANALYZE TABLE t1;
|
|
|
|
# Only second CTE reference evaluated.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE
|
|
WITH tx AS (SELECT FLOOR(5*RAND(0)) i1 FROM t1 j1 JOIN t1 j2 ON j1.b=j2.a)
|
|
(SELECT 1 FROM t1 d1 JOIN t1 d2 ON d1.a=10*d2.a JOIN tx ON d2.b=i1) UNION
|
|
SELECT 2 FROM tx;
|
|
|
|
# Only second CTE reference evaluated (and no 'ANALYZE').
|
|
EXPLAIN FORMAT=TREE
|
|
WITH tx AS (SELECT FLOOR(5*RAND(0)) i1 FROM t1 j1 JOIN t1 j2 ON j1.b=j2.a)
|
|
(SELECT 1 FROM t1 d1 JOIN t1 d2 ON d1.a=-d2.a JOIN tx ON d2.b=i1) UNION
|
|
SELECT 2 FROM tx;
|
|
|
|
# Only first CTE reference evaluated.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE
|
|
WITH tx AS (SELECT FLOOR(5*RAND(0)) i1 FROM t1 j1 JOIN t1 j2 ON j1.b=j2.a)
|
|
SELECT 2 FROM tx UNION
|
|
(SELECT 1 FROM t1 d1 JOIN t1 d2 ON d1.a=-d2.a JOIN tx ON d2.b=i1);
|
|
|
|
# Both CTE references evaluated.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE
|
|
WITH tx AS (SELECT FLOOR(5*RAND(0)) i1 FROM t1 j1 JOIN t1 j2 ON j1.b=j2.a)
|
|
SELECT 2 FROM tx UNION
|
|
(SELECT 1 FROM t1 d1 JOIN t1 d2 ON d1.a=d2.a JOIN tx ON d2.b=i1);
|
|
|
|
# No CTE reference evaluated.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE
|
|
WITH tx AS (SELECT FLOOR(5*RAND(0)) i1 FROM t1 j1 JOIN t1 j2 ON j1.b=j2.a)
|
|
(SELECT 1 FROM t1 d1 JOIN t1 d2 ON d1.a=10*d2.a JOIN tx ON d2.b=i1) UNION
|
|
(SELECT 1 FROM t1 d1 JOIN t1 d2 ON d1.a=-d2.a JOIN tx ON d2.b=i1);
|
|
|
|
# CTE executed in subquery only.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE
|
|
WITH x1 AS (SELECT MAX(a) AS m1 FROM t1 GROUP BY b)
|
|
SELECT * FROM x1 y1 WHERE
|
|
y1.m1 = (SELECT MAX(m1) FROM x1) AND ABS(y1.m1) = (SELECT MIN(m1) FROM x1);
|
|
|
|
--echo #
|
|
--echo # Bug#34051681 mysqld asan crash at ExplainMaterializeAccessPath
|
|
--echo #
|
|
# CTE in subquery never executed.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE
|
|
WITH x1 AS (SELECT MAX(a) AS m1 FROM t1 GROUP BY b)
|
|
SELECT * FROM t1 y1 LEFT JOIN t1 y2 ON y1.a=-y2.a
|
|
WHERE y1.b+y2.b = (SELECT MAX(m1) FROM x1);
|
|
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # Bug #34135465 EXPLAIN ANALYZE: 'Aggregate using temporary table'
|
|
--echo # shows wrong number of rows
|
|
--echo #
|
|
|
|
CREATE TABLE t1(i INT, j INT);
|
|
INSERT INTO t1 VALUES (0,0),(0,1),(0,2),(1,0),(1,1),(1,2),(2,0),(2,1),(2,2);
|
|
ANALYZE TABLE t1;
|
|
|
|
# Check that we get the right row count for "Aggregate using temporary table".
|
|
# We aggregate three rows in a temporary table even if we just read two of them.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE INSERT INTO t1 SELECT 4, SUM(i) k1 FROM t1 GROUP BY j LIMIT 2;
|
|
|
|
# Check that we get the right row count for "Materialize CTE".
|
|
# We materialize three rows even if we just read two of them.
|
|
--replace_regex $elide_time
|
|
EXPLAIN ANALYZE WITH cte AS (SELECT SUM(i) k1 FROM t1 GROUP BY j)
|
|
SELECT * FROM cte LIMIT 2;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#34527697: EXPLAIN and EXPLAIN ANALYZE disagree on
|
|
--echo # covering index scans
|
|
--echo #
|
|
|
|
CREATE TABLE t(
|
|
x INT,
|
|
y INT DEFAULT (x),
|
|
z VARCHAR(128) DEFAULT (REPEAT('z', 128)), KEY (x));
|
|
INSERT INTO t(x) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
|
|
INSERT INTO t SELECT * FROM t;
|
|
INSERT INTO t SELECT * FROM t;
|
|
INSERT INTO t SELECT * FROM t;
|
|
INSERT INTO t SELECT * FROM t;
|
|
ANALYZE TABLE t;
|
|
|
|
--replace_regex $elide_costs
|
|
EXPLAIN FORMAT=TREE SELECT 1 FROM t;
|
|
--replace_regex $elide_costs_and_time
|
|
EXPLAIN ANALYZE SELECT 1 FROM t;
|
|
|
|
--replace_regex $elide_costs
|
|
EXPLAIN FORMAT=TREE SELECT 1 FROM t WHERE x = 4;
|
|
--replace_regex $elide_costs_and_time
|
|
EXPLAIN ANALYZE SELECT 1 FROM t WHERE x = 4;
|
|
|
|
--replace_regex $elide_costs
|
|
EXPLAIN FORMAT=TREE SELECT 1 FROM t WHERE x > 4;
|
|
--replace_regex $elide_costs_and_time
|
|
EXPLAIN ANALYZE SELECT 1 FROM t WHERE x > 4;
|
|
|
|
--replace_regex $elide_costs
|
|
EXPLAIN FORMAT=TREE SELECT x FROM t ORDER BY MOD(x, 2);
|
|
--replace_regex $elide_costs_and_time
|
|
EXPLAIN ANALYZE SELECT x FROM t ORDER BY MOD(x, 2);
|
|
|
|
--replace_regex $elide_costs
|
|
EXPLAIN FORMAT=TREE SELECT t1.x FROM t t1, t t2 WHERE t1.x < t2.y;
|
|
--replace_regex $elide_costs_and_time
|
|
EXPLAIN ANALYZE SELECT t1.x FROM t t1, t t2 WHERE t1.x < t2.y;
|
|
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#35537921 Contribution by Tencent:
|
|
--echo # explain format=tree lost the subquery in the hash join
|
|
--echo #
|
|
|
|
SET optimizer_switch=default;
|
|
|
|
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
|
|
|
|
--replace_regex $elide_costs
|
|
EXPLAIN FORMAT=TREE
|
|
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
|
|
EXPLAIN FORMAT=TREE
|
|
SELECT * FROM t1 x1 LEFT JOIN t1 x2 ON x2.a<
|
|
(SELECT MIN(x3.a) FROM t1 x3 WHERE x1.a=x3.a);
|
|
|
|
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;
|
|
|
|
--replace_regex $elide_costs_and_rows
|
|
EXPLAIN FORMAT=TREE SELECT LAST_VALUE((SELECT x1.a FROM t1))
|
|
OVER (PARTITION BY b) FROM t1 x1;
|
|
|
|
DROP TABLE t1;
|