diff options
| author | Sergey Petrunya <psergey@askmonty.org> | 2013-10-16 13:38:42 +0400 |
|---|---|---|
| committer | Sergey Petrunya <psergey@askmonty.org> | 2013-10-16 13:38:42 +0400 |
| commit | e5d13c1567cd5600f163aedfc940b3ba6b7703af (patch) | |
| tree | e7ddb60207533508db6f4c509e8921e398769eed /mysql-test | |
| parent | ce069b1f6b3847309b06e548adf23fa0c7238336 (diff) | |
| parent | e62e1e8a3b82f037cb3c76986327b36647288a2d (diff) | |
| download | mariadb-git-e5d13c1567cd5600f163aedfc940b3ba6b7703af.tar.gz | |
Merge 10.0-base -> 10.0
Diffstat (limited to 'mysql-test')
38 files changed, 5265 insertions, 126 deletions
diff --git a/mysql-test/disabled.def b/mysql-test/disabled.def index 6d84b8ce0f4..e5fa24786e1 100644 --- a/mysql-test/disabled.def +++ b/mysql-test/disabled.def @@ -16,7 +16,7 @@ read_many_rows_innodb : Bug#11748886 2010-11-15 mattiasj report already exist archive-big : Bug#11817185 2011-03-10 Anitha Disabled since this leads to timeout on Solaris Sparc log_tables-big : Bug#11756699 2010-11-15 mattiasj report already exists mysql_embedded : Bug#12561297 2011-05-14 Anitha Dependent on PB2 changes - eventum#41836 +#show_explain : Psergey: random timeout in range-checked-for-each record query. ssl_crl_clients_valid : broken upstream ssl_crl : broken upstream ssl_crl_clrpath : broken upstream -show_explain : Psergey: random timeout in range-checked-for-each record query. diff --git a/mysql-test/include/explain_non_select.inc b/mysql-test/include/explain_non_select.inc new file mode 100644 index 00000000000..a0f86e744b0 --- /dev/null +++ b/mysql-test/include/explain_non_select.inc @@ -0,0 +1,833 @@ +# This file is a collection of regression and coverage tests +# for WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE. + +-- disable_query_log +-- disable_result_log +# SET GLOBAL innodb_stats_persistent=0; +-- enable_result_log +-- enable_query_log + +# set end_markers_in_json=on; + +--echo #1 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +--let $query = UPDATE t1 SET a = 10 WHERE a < 10 +--let $select = SELECT * FROM t1 WHERE a < 10 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #2 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +--let $query = DELETE FROM t1 WHERE a < 10 +--let $select = SELECT * FROM t1 WHERE a < 10 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #3 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +--let $query = DELETE FROM t1 USING t1 WHERE a = 1 +--let $select = SELECT * FROM t1 WHERE a = 1 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #4 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +--let $query = UPDATE t1, t2 SET t1.a = 10 WHERE t1.a = 1 +--let $select = SELECT * FROM t1, t2 WHERE t1.a = 1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #5 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +--let $query = UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1 +--let $select = SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a = 1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #6 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +--let $query = UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3) +--let $select = SELECT * FROM t1 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3) +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #7 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +--let $query = UPDATE t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3) +--let $select = SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3) +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #7 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +--let $query = UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) +--let $select = SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #8 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +--let $query = UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10 +--let $select = SELECT * FROM t1 t11, (SELECT * FROM t2) t12 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #9 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +--let $query = UPDATE t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10 +--let $select = SELECT * FROM t1 t11, (SELECT 1 FROM DUAL) t12 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #10 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +--let $query = UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1 +--let $select = SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a > 1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #11 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +--let $query = DELETE FROM t1 WHERE a > 1 LIMIT 1 +--let $select = SELECT * FROM t1 WHERE a > 1 LIMIT 1 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #12 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +--let $query = DELETE FROM t1 WHERE 0 +--let $select = SELECT * FROM t1 WHERE 0 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #13 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +--let $query = DELETE FROM t1 USING t1 WHERE 0 +--let $select = SELECT * FROM t1 WHERE 0 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #14 +CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b)); +INSERT INTO t1 VALUES (3, 3), (7, 7); +--let $query = DELETE FROM t1 WHERE a = 3 +--let $select = SELECT * FROM t1 WHERE a = 3 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #15 +CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b)); +INSERT INTO t1 VALUES (3, 3), (7, 7); +--let $query = DELETE FROM t1 WHERE a < 3 +--let $select = SELECT * FROM t1 WHERE a < 3 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #16 +CREATE TABLE t1 ( a int PRIMARY KEY ); +--let $query = DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a +--let $select = SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a +--source include/explain_utils.inc +INSERT INTO t1 VALUES (1), (2), (3); +--let $query = DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a +--let $select = SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #17 +CREATE TABLE t1(a INT PRIMARY KEY); +INSERT INTO t1 VALUES (4),(3),(1),(2); +--let $query = DELETE FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1 +--let $select = SELECT * FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #18 +CREATE TABLE t1 (a DATE, b TIME, c INT, KEY c(c), KEY b(b), KEY a(a)); +INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), (); +UPDATE t1 SET a = c, b = c; +--let $query = DELETE FROM t1 ORDER BY a ASC, b ASC LIMIT 1 +--let $select = SELECT * FROM t1 ORDER BY a ASC, b ASC LIMIT 1 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #19 +CREATE TABLE t1 (a1 INT NOT NULL, b1 INT NOT NULL); +CREATE TABLE t2 (a2 INT NOT NULL, b2 INT NOT NULL, PRIMARY KEY (a2,b2)); +CREATE TABLE t3 (a3 INT NOT NULL, b3 INT NOT NULL, PRIMARY KEY (a3,b3)); +INSERT INTO t1 VALUES (1,1), (2,1), (1,3); +INSERT INTO t2 VALUES (1,1), (2,2), (3,3); +INSERT INTO t3 VALUES (1,1), (2,1), (1,3); +--let $query = DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3 +--let $select = SELECT * FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3 +--source include/explain_utils.inc +DROP TABLE t1, t2, t3; + +--echo #20 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (1), (2), (3); +--let $query = UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2) +--let $select = SELECT * FROM t1 WHERE a IN (SELECT a FROM t2) +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #21 +CREATE TABLE t1 (a1 INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +CREATE TABLE t2 (a2 VARCHAR(10)); +INSERT INTO t2 VALUES (1), (2), (3), (4), (5); +SET @save_optimizer_switch= @@optimizer_switch; +--disable_query_log +if (`select locate('semijoin', @@optimizer_switch) > 0`) +{ + SET @@optimizer_switch= 'semijoin=off'; +} +--enable_query_log +--let $query = DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) +--let $select = SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) +--source include/explain_utils.inc +SET @@optimizer_switch= @save_optimizer_switch; +TRUNCATE t1; +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +--let $query = DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) +--let $select = SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #22 +CREATE TABLE t1 (i INT, j INT); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +--let $query = UPDATE t1 SET i = 10 +--let $select = SELECT * FROM t1 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #23 +CREATE TABLE t1 (i INT, j INT); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +--let $query = DELETE FROM t1 +--let $select = SELECT * FROM t1 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #24 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; +INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; +--let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--let $no_rows = 1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #25 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (i INT); +--let $query = INSERT INTO t2 SELECT * FROM t1 +--let $select = SELECT * FROM t1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #26 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (i INT); +--let $query = REPLACE INTO t2 SELECT * FROM t1 +--let $select = SELECT * FROM t1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #27 +CREATE TABLE t1 (i INT); +--let $query = INSERT INTO t1 SET i = 10 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #28 +CREATE TABLE t1 (i INT); +--let $query = REPLACE INTO t1 SET i = 10 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #29 +CREATE TABLE t1 (a INT, i INT PRIMARY KEY); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +--let $query = DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +--let $select = SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #30 +CREATE TABLE t1(a INT, i CHAR(2), INDEX(i(1))); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +--let $query = DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +--let $select = SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #31 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; +--let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #32 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; +INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; +--let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--let $no_rows = 1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #33 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c)); +INSERT INTO t2 SELECT i, i, i, i FROM t1; +--let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #34 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) + ENGINE=HEAP; +INSERT INTO t2 SELECT i, i, i, i FROM t1; +--let $query = DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #35 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35),(36),(37),(38),(39), + (40),(41),(42); +CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2)); +INSERT INTO t2 (key1, key2) SELECT i, i FROM t1; +--let $query = DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1 +--let $select = SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #36 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, i INT PRIMARY KEY); +INSERT INTO t2 (i) SELECT i FROM t1; +--let $query = DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5 +--let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #37 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); +INSERT INTO t2 SELECT i, i, i FROM t1; +--let $query = DELETE FROM t2 ORDER BY a, b DESC LIMIT 5 +--let $select = SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #38 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b)); +INSERT INTO t2 (a, b) SELECT i, i FROM t1; +INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2; +--let $query = DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5 +--let $select = SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5 +--let $no_rows = 1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #39 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, i INT PRIMARY KEY); +INSERT INTO t2 (i) SELECT i FROM t1; +--let $query = UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +--let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +--let $no_rows = 1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #40 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1))); +INSERT INTO t2 (i) SELECT i FROM t1; +--let $query = UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +--let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #41 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; +--let $query = UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 +--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #42 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; +INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; +--let $query = UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 +--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--let $no_rows = 1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #43 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c)); +INSERT INTO t2 SELECT i, i, i, i FROM t1; +--let $query = UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 +--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #44 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) + ENGINE=HEAP; +INSERT INTO t2 SELECT i, i, i, i FROM t1; +--let $query = UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 +--let $select = SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #45 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35),(36),(37),(38),(39), + (40),(41),(42); +CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2)); +INSERT INTO t2 (key1, key2) SELECT i, i FROM t1; +--let $query = UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1 +--let $select = SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #46 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, i INT PRIMARY KEY); +INSERT INTO t2 (i) SELECT i FROM t1; +--let $query = UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5 +--let $select = SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #47 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); +INSERT INTO t2 SELECT i, i, i FROM t1; +--let $query = UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5 +--let $select = SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #48 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), + (20),(21),(22),(23),(24),(25),(26),(27),(28),(29), + (30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b)); +INSERT INTO t2 (a, b) SELECT i, i FROM t1; +INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2; +--let $query = UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5 +--let $select = SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5 +--let $no_rows = 1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #49 +CREATE TABLE t1 ( + pk INT NOT NULL AUTO_INCREMENT, + c1_idx CHAR(1) DEFAULT 'y', + c2 INT, + PRIMARY KEY (pk), + INDEX c1_idx (c1_idx) +); +INSERT INTO t1 VALUES (1,'y',1), (2,'n',2), (3,'y',3), (4,'n',4); +--let $query = UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 +--let $select = SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 +--source include/explain_utils.inc +--let $query = DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 +--let $select = SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #50 +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY); +INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +--let $query = UPDATE t1 SET a=a+10 WHERE a > 34 +--let $select = SELECT * FROM t1 WHERE a > 34 +--source include/explain_utils.inc +DROP TABLE t1; + +--echo #51 +CREATE TABLE t1 (c1 INT, c2 INT, c3 INT); +CREATE TABLE t2 (c1 INT, c2 INT); +INSERT INTO t1 VALUES (1, 1, 10), (2, 2, 20); +--let $query = UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 +--let $select = SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 +--source include/explain_utils.inc +--let $query = UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 10 +--let $select = SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c3 = 10 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #52 +CREATE TABLE t1(f1 INT, f2 INT); +CREATE TABLE t2(f3 INT, f4 INT); +CREATE INDEX IDX ON t2(f3); +INSERT INTO t1 VALUES(1,0),(2,0); +INSERT INTO t2 VALUES(1,1),(2,2); +--let $query = UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) +--let $select = SELECT (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1 +--source include/explain_utils.inc +DROP TABLE t1, t2; + +--echo #55 +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +SET @a = NULL; +EXPLAIN DELETE FROM t1 WHERE (@a:= a); +if (`SELECT @a IS NOT NULL`) { + die Unexpectedly modified user variable; +} +DROP TABLE t1; + +--echo #56 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +--error ER_BAD_FIELD_ERROR + DELETE FROM t1 USING t1 WHERE uknown_column = 12345; +--error ER_BAD_FIELD_ERROR +EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE uknown_column = 12345; +DROP TABLE t1; + +--echo #57 +CREATE TABLE t1(f1 INT); +--error ER_BAD_FIELD_ERROR +EXPLAIN EXTENDED UPDATE t1 SET f2=1 ORDER BY f2; +--error ER_BAD_FIELD_ERROR +UPDATE t1 SET f2=1 ORDER BY f2; +DROP TABLE t1; + +--disable_parsing +--echo #59 +CREATE TABLE t1 ( a INT, KEY( a ) ); +INSERT INTO t1 VALUES (0), (1); +CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12; +SET SESSION sql_safe_updates = 1; +--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE +EXPLAIN EXTENDED UPDATE IGNORE v1 SET a = 1; +--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE +UPDATE IGNORE v1 SET a = 1; +SET SESSION sql_safe_updates = DEFAULT; +DROP TABLE t1; +DROP VIEW v1; +--enable_parsing + +--echo #62 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0), (1); +CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12; +--let $query = UPDATE v1 SET a = 1 WHERE a > 0 +--let $select = SELECT * FROM v1 WHERE a > 0 +--source include/explain_utils.inc +--let $query = UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a +--let $select = SELECT * FROM t1, v1 WHERE t1.a = v1.a +--source include/explain_utils.inc +DROP TABLE t1; +DROP VIEW v1; + +--echo #63 +CREATE TABLE t1 (a INT, PRIMARY KEY(a)); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +CREATE VIEW v1 (a) AS SELECT a FROM t1; +--let $query = DELETE FROM v1 WHERE a < 4 +--let $select = SELECT * FROM v1 WHERE a < 4 +--source include/explain_utils.inc +DROP TABLE t1; +DROP VIEW v1; + +--echo #64 +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a)); +INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10); +CREATE TABLE t2 (x INT); +INSERT INTO t2 VALUES (1), (2), (3), (4); +CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1; +--let $query = DELETE v1 FROM t2, v1 WHERE t2.x = v1.a +--let $select = SELECT * FROM t2, v1 WHERE t2.x = v1.a +--source include/explain_utils.inc +DROP TABLE t1,t2; +DROP VIEW v1; + +--echo #65 +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a)); +INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10); +CREATE TABLE t2 (x INT); +INSERT INTO t2 VALUES (1), (2), (3), (4); +CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1; +--let $query = DELETE v1 FROM t2, v1 WHERE t2.x = v1.a +--let $select = SELECT * FROM t2, v1 WHERE t2.x = v1.a +--source include/explain_utils.inc +DROP TABLE t1,t2; +DROP VIEW v1; + +--echo #66 +CREATE TABLE t1 (a INT); +CREATE VIEW v1 (x) AS SELECT a FROM t1; +--let $query = INSERT INTO v1 VALUES (10) +--let $select = SELECT NULL +--source include/explain_utils.inc +DROP TABLE t1; +DROP VIEW v1; + +--echo #67 +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +CREATE VIEW v1 (x) AS SELECT b FROM t2; +--let $query = INSERT INTO v1 SELECT * FROM t1 +--let $select = SELECT * FROM t1 +--source include/explain_utils.inc +DROP TABLE t1, t2; +DROP VIEW v1; + +--echo #68 +CREATE TABLE t1 (i INT); +EXPLAIN INSERT DELAYED INTO t1 VALUES (1); +DROP TABLE t1; + +--echo #69 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +--let $query = UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) +--let $select = SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) +--source include/explain_utils.inc +--let $query = UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) +--let $select = SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) +--source include/explain_utils.inc +--let $query = UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) +--let $select = SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) +--source include/explain_utils.inc +DROP TABLE t1,t2; + +--echo #70 +CREATE TABLE t1 (c1 INT KEY); +CREATE TABLE t2 (c2 INT); +CREATE TABLE t3 (c3 INT); +EXPLAIN EXTENDED UPDATE t3 SET c3 = ( + SELECT COUNT(d1.c1) + FROM ( + SELECT a11.c1 FROM t1 AS a11 + STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 + JOIN t1 AS a12 ON a12.c1 = a11.c1 + ) d1 +); + +DROP TABLE t1, t2, t3; + +--disable_parsing +--echo #71 +# +# Bug: after EXPLAIN bulk INSERT...SELECT and bulk INSERT...SELECT +# to a # MyISAM table the SELECT query may fail with the +# "1030: Got error 124 from storage engine" error message. +# +CREATE TABLE t1 (c1 INT NOT NULL, c2 INT NOT NULL, INDEX i1(c1)); +INSERT INTO t1 VALUES (1,0),(2,0),(3,0),(4,0),(5,0),(6,0),(7,0),(8,0); +--disable_query_log +let $1=7; +SET @d=8; +while ($1) { + eval INSERT INTO t1 SELECT c1 + @d, c2 + @d FROM t1; + eval SET @d = @d*2; + dec $1; +} +--enable_query_log +CREATE TABLE t2 LIKE t1; + +# replace "rows" column for InnoDB +--replace_column 9 X +EXPLAIN INSERT INTO t2 SELECT * FROM t1; +INSERT INTO t2 SELECT * FROM t1; +--disable_result_log +SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1; +--enable_result_log + +DROP TABLE t1, t2; +--enable_parsing + +--echo #73 + +CREATE TABLE t1 (id INT); +CREATE TABLE t2 (id INT); +INSERT INTO t1 VALUES (1), (2); + +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id; + +DROP TABLE t1,t2; + +--echo #74 + +CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); + +--echo # used key is modified & Using temporary + +--let $query = UPDATE t1 SET a=a+1 WHERE a>10 +--let $select = SELECT a t1 FROM t1 WHERE a>10 +--source include/explain_utils.inc + +--echo # used key is modified & Using filesort + +--let $query = UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20 +--let $select = SELECT a t1 FROM t1 WHERE a>10 ORDER BY a+20 +--source include/explain_utils.inc + +DROP TABLE t1; + +--echo # +--echo # Bug #12949629: CLIENT LOSES CONNECTION AFTER EXECUTING A PROCEDURE WITH +--echo # EXPLAIN UPDATE/DEL/INS +--echo # + +CREATE TABLE t1 (i INT); +CREATE TABLE t2 (i INT); + +--delimiter | +CREATE PROCEDURE p1() BEGIN EXPLAIN INSERT INTO t1 VALUES (1);END| +CREATE PROCEDURE p2() BEGIN INSERT INTO t1 VALUES (1);END| +CREATE PROCEDURE p3() BEGIN EXPLAIN INSERT INTO t1 SELECT 1;END| +CREATE PROCEDURE p4() BEGIN INSERT INTO t1 SELECT 1;END| +CREATE PROCEDURE p5() BEGIN EXPLAIN REPLACE INTO t1 VALUES (1);END| +CREATE PROCEDURE p6() BEGIN REPLACE INTO t1 VALUES (1);END| +CREATE PROCEDURE p7() BEGIN EXPLAIN REPLACE INTO t1 SELECT 1;END| +CREATE PROCEDURE p8() BEGIN REPLACE INTO t1 SELECT 1;END| +CREATE PROCEDURE p9() BEGIN EXPLAIN UPDATE t1 SET i = 10;END| +CREATE PROCEDURE p10() BEGIN UPDATE t1 SET i = 10;END| +CREATE PROCEDURE p11() BEGIN EXPLAIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END| +CREATE PROCEDURE p12() BEGIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END| +CREATE PROCEDURE p13() BEGIN EXPLAIN DELETE FROM t1;END| +CREATE PROCEDURE p14() BEGIN DELETE FROM t1;END| +CREATE PROCEDURE p15() BEGIN EXPLAIN DELETE FROM t1 USING t1;END| +CREATE PROCEDURE p16() BEGIN DELETE FROM t1 USING t1;END| +--delimiter ; + +let $i=16; +while($i) { + eval CALL p$i(); + eval DROP PROCEDURE p$i; + dec $i; +} + +DROP TABLE t1, t2; + +--echo # + +-- disable_query_log +-- disable_result_log +# SET GLOBAL innodb_stats_persistent=default; +-- enable_result_log +-- enable_query_log diff --git a/mysql-test/include/explain_utils.inc b/mysql-test/include/explain_utils.inc new file mode 100644 index 00000000000..505798e432a --- /dev/null +++ b/mysql-test/include/explain_utils.inc @@ -0,0 +1,161 @@ +# This file is a collection of utility tests +# for WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE. +# +# Since MTR doesn't have functions, we use this file instead +# including it many times. +# +# Parameters: +# +# $query: INSERT/REPLACE/UPDATE/DELETE query to explain +# NOTE: this file resets this variable +# +# $select: may be empty; the SELECT query similar to $query +# We use it to compare: +# 1) table data before and after EXPLAIN $query evaluation; +# 2) EXPLAIN $query and EXPLAIN $select output and +# handler/filesort statistics +# NOTE: this file resets this variable +# $innodb: take $no_rows parameter into account if not 0; +# $no_rows: filter out "rows" and "filtered" columns of EXPLAIN if not 0; +# it may be necessary for InnoDB tables since InnoDB's table row +# counter can't return precise and repeatable values; +# NOTE: ANALYZE TABLE doesn't help +# NOTE: this file resets this variable + +--echo # +--echo # query: $query +--echo # select: $select +--echo # + +if ($select) { +--disable_query_log +--eval $select INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/before_explain.txt' +--enable_query_log +} + +if ($innodb) { + if ($no_rows) { +--replace_column 9 X + } +} +--eval EXPLAIN $query +if (`SELECT ROW_COUNT() > 0`) { +--echo # Erroneous query: EXPLAIN $query +--die Unexpected ROW_COUNT() <> 0 +} + +FLUSH STATUS; +FLUSH TABLES; +if ($innodb) { + if ($no_rows) { +--replace_column 9 X 10 X + } +} +--eval EXPLAIN EXTENDED $query +if (`SELECT ROW_COUNT() > 0`) { +--echo # Erroneous query: EXPLAIN EXTENDED $query +--die Unexpected ROW_COUNT() <> 0 +} +--echo # Status of EXPLAIN EXTENDED query +--disable_query_log +SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR + Variable_name LIKE 'Handler_read_%' OR + Variable_name = 'Handler_write' OR + Variable_name = 'Handler_update' OR + Variable_name = 'Handler_delete') AND Value <> 0; +--enable_query_log + +if ($json) { +if ($innodb) { + if ($no_rows) { +--replace_regex /"rows": [0-9]+/"rows": "X"/ /"filtered": [0-9.]+/"filtered": "X"/ + } +} +--eval EXPLAIN FORMAT=JSON $query; +if ($validation) { +--disable_query_log +--replace_result $MASTER_MYSOCK MASTER_MYSOCK +--exec $MYSQL -S $MASTER_MYSOCK -u root -r test -e "EXPLAIN FORMAT=JSON $query;" > $MYSQLTEST_VARDIR/tmp/explain.json +--replace_regex /[-]*// /FILE.[\/\\:_\.0-9A-Za-z]*/Validation:/ +--exec python $MYSQL_TEST_DIR/suite/opt_trace/validate_json.py $MYSQLTEST_VARDIR/tmp/explain.json +--remove_file '$MYSQLTEST_VARDIR/tmp/explain.json' +--enable_query_log +} +} + +if ($select) { +FLUSH STATUS; +FLUSH TABLES; +if ($innodb) { + if ($no_rows) { +--replace_column 9 X 10 X + } +} +--eval EXPLAIN EXTENDED $select +--echo # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +--disable_query_log +SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR + Variable_name LIKE 'Handler_read_%' OR + Variable_name = 'Handler_write' OR + Variable_name = 'Handler_update' OR + Variable_name = 'Handler_delete') AND Value <> 0; +--enable_query_log +if ($json) { +if ($innodb) { + if ($no_rows) { +--replace_regex /"rows": [0-9]+/"rows": "X"/ /"filtered": [0-9.]+/"filtered": "X"/ + } +} +--eval EXPLAIN FORMAT=JSON $select; +if ($validation) { +--disable_query_log +--replace_result $MASTER_MYSOCK MASTER_MYSOCK +--exec $MYSQL -S $MASTER_MYSOCK -u root -r test -e "EXPLAIN FORMAT=JSON $select;" > $MYSQLTEST_VARDIR/tmp/explain.json +--replace_regex /[-]*// /FILE.[\/\\:_\.0-9A-Za-z]*/Validation:/ +--exec python $MYSQL_TEST_DIR/suite/opt_trace/validate_json.py $MYSQLTEST_VARDIR/tmp/explain.json +--remove_file '$MYSQLTEST_VARDIR/tmp/explain.json' +--enable_query_log +} +} +} + +--disable_query_log + +if ($select) { +--eval $select INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/after_explain.txt' +--diff_files '$MYSQLTEST_VARDIR/tmp/before_explain.txt' '$MYSQLTEST_VARDIR/tmp/after_explain.txt' +--remove_file '$MYSQLTEST_VARDIR/tmp/before_explain.txt' +--remove_file '$MYSQLTEST_VARDIR/tmp/after_explain.txt' +} + +FLUSH STATUS; +FLUSH TABLES; +if ($select) { +--disable_result_log +--eval $select +--enable_result_log +--echo # Status of "equivalent" SELECT query execution: +SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR + Variable_name LIKE 'Handler_read_%' OR + Variable_name = 'Handler_write' OR + Variable_name = 'Handler_update' OR + Variable_name = 'Handler_delete') AND Value <> 0; +} + +FLUSH STATUS; +FLUSH TABLES; +--eval $query +--echo # Status of testing query execution: +SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR + Variable_name LIKE 'Handler_read_%' OR + Variable_name = 'Handler_write' OR + Variable_name = 'Handler_update' OR + Variable_name = 'Handler_delete') AND Value <> 0; + +--let $query= +--let $select= +--let $no_rows= + +--enable_query_log + +--echo diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result index 22e2ab8d676..f5e7393591c 100644 --- a/mysql-test/r/derived_opt.result +++ b/mysql-test/r/derived_opt.result @@ -109,8 +109,8 @@ count(*) 2 explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A ALL NULL NULL NULL NULL 2 Using where -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.A.E2 1 Using where +1 PRIMARY A ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.A.E2 1 Using where 3 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 2 Using where drop table t1; create table t1 (a int); diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 3438579d97e..c0daabc148f 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -386,7 +386,7 @@ materialized derived in merged derived explain extended select * from (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) zz; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where 3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2) @@ -429,8 +429,8 @@ join (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) z on x.f1 = z.f1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where -1 SIMPLE <derived5> ref key0 key0 5 tt.f1 2 100.00 +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where +1 PRIMARY <derived5> ref key0 key0 5 tt.f1 2 100.00 5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort 3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort Warnings: @@ -522,7 +522,7 @@ materialized view in merged derived explain extended select * from ( select * from v1 where f1 < 7) tt; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where 3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort Warnings: Note 1003 select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`v1` where (`v1`.`f1` < 7) @@ -568,8 +568,8 @@ f1 f11 join of above two explain extended select * from v6 join v7 on f2=f1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t2 ALL NULL NULL NULL NULL 11 100.00 Using where -1 SIMPLE <derived5> ref key0 key0 5 test.t2.f2 2 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 11 100.00 Using where +1 PRIMARY <derived5> ref key0 key0 5 test.t2.f2 2 100.00 5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort Warnings: Note 1003 select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22`,`v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`t2` join `test`.`v1` where ((`v1`.`f1` = `test`.`t2`.`f2`) and (`test`.`t2`.`f2` < 7) and (`test`.`t2`.`f2` in (2,3))) @@ -1220,7 +1220,7 @@ Note 1003 select `test`.`t1`.`b` AS `b` from `test`.`t1` where 0 EXPLAIN EXTENDED SELECT * FROM (SELECT b FROM v2 WHERE b = 0) t WHERE b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort Warnings: Note 1003 select `v2`.`b` AS `b` from `test`.`v2` where 0 diff --git a/mysql-test/r/explain_non_select.result b/mysql-test/r/explain_non_select.result new file mode 100644 index 00000000000..00ca481147b --- /dev/null +++ b/mysql-test/r/explain_non_select.result @@ -0,0 +1,221 @@ +drop table if exists t0, t1; +create table t0 (a int) engine=myisam; +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); +# +# Tests for single-table DELETE +# +explain select * from t0 where a=3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 8 Using where +explain delete from t0 where a=3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 8 Using where +# DELETE without WHERE is a special case: +explain delete from t0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL 8 Deleting all rows +create table t1 (a int, b int, filler char(100), key(a), key(b)); +insert into t1 +select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler' +from t0 A, t0 B, t0 C; +# This should use an index, possible_keys=NULL because there is no WHERE +explain delete from t1 order by a limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL a 5 NULL 2 +# This should use range, possible_keys={a,b} +explain delete from t1 where a<20 and b < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,b a 5 NULL 1 Using where +# This should use ALL + filesort +explain delete from t1 order by a+1 limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 512 Using filesort +# This should use range + using filesort +explain delete from t1 where a<20 order by b limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 1 Using where; Using filesort +# Try some subqueries: +explain delete from t1 where a < (select max(a) from t0); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range a a 5 NULL 1 Using where +2 SUBQUERY t0 ALL NULL NULL NULL NULL 8 +explain delete from t1 where a < (select max(a) from t0 where a < t1.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 512 Using where +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 8 Using where +# +# Tests for multi-table DELETE +# +explain delete t1 from t0, t1 where t0.a = t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 8 Using where +1 SIMPLE t1 ref a a 5 test.t0.a 4 +drop table t0, t1; +# ################################################################### +# ## EXPLAIN UPDATE tests +# ################################################################### +create table t0 (a int) engine=myisam; +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); +explain update t0 set a=3 where a=4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 8 Using where +create table t1 (a int, b int, filler char(100), key(a), key(b)); +insert into t1 +select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler' +from t0 A, t0 B, t0 C; +explain update t1 set a=a+1 where 3>4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +explain update t1 set a=a+1 where a=3 and a=4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +# This should use an index, possible_keys=NULL because there is no WHERE +explain update t1 set a=a+1 order by a limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 512 Using filesort +# This should use range, possible_keys={a,b} +explain update t1 set filler='fooo' where a<20 and b < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a,b a 5 NULL 1 Using where +# This should use ALL + filesort +explain update t1 set filler='fooo' order by a+1 limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 512 Using filesort +# This should use range + using filesort +explain update t1 set filler='fooo' where a<20 order by b limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 1 Using where; Using filesort +# Try some subqueries: +explain update t1 set filler='fooo' where a < (select max(a) from t0); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range a a 5 NULL 1 Using where +2 SUBQUERY t0 ALL NULL NULL NULL NULL 8 +explain update t1 set filler='fooo' where a < (select max(a) from t0 where a < t1.b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 512 Using where +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 8 Using where +# +# Tests for multi-table UPDATE +# +explain update t0, t1 set t1.a=t1.a+1 where t0.a = t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 8 Using where +1 SIMPLE t1 ref a a 5 test.t0.a 4 +drop table t0, t1; +# +# Try DELETE ... RETURNING ... +# +create table t0 (a int); +insert into t0 values (1),(2),(3),(4); +explain delete from t0 where a=1 returning a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 4 Using where +explain delete from t0 returning a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 4 +drop table t0; +# +# MDEV-5070 - EXPLAIN INSERT ... SELECT crashes on 10.0-base-explain-slowquerylog +# +create table t0 (a int); +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); +create table t1 (a int); +explain insert into t1 select * from t0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 8 +explain replace into t1 select * from t0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 8 +drop table t0, t1; +# +# MDEV-5067: Valgrind warnings (Invalid read) in QPF_table_access::print_explain +# +CREATE TABLE t1 (i INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (7),(0),(9); +SELECT * FROM t1 INNER JOIN ( SELECT DISTINCT * FROM t1 ) AS sq ON (sq.i = t1.i); +i i +7 7 +0 0 +9 9 +DROP TABLE t1; +# +# MDEV-5093, MDEV-5094: EXPLAIN PARTITIONS and EXPLAIN EXTENDED do not +# work for EXPLAIN UPDATE. +# +create table t1 (i int); +explain partitions update t1 set i = 3; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 0 +create table t2 (a int, b int) partition by hash(a) partitions 5; +insert into t2 values (0,0),(1,1),(2,2),(3,3),(4,4); +explain partitions update t2 set b=3 where a in (3,4); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 2 Using where +explain partitions delete from t2 where a in (3,4); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 p3,p4 ALL NULL NULL NULL NULL 2 Using where +explain extended update t2 set b=3 where a in (3,4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +explain extended delete from t2 where a in (3,4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where +drop table t1,t2; +# +# Check the special case where partition pruning removed all partitions +# +create table t1 (a int, b int) +partition by range (a) ( +partition p0 values less than (10), +partition p1 values less than (20), +partition p2 values less than (30) +); +insert into t1 values (9,9), (19,19), (29,29); +explain partitions select * from t1 where a in (32,33); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain partitions delete from t1 where a in (32,33); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning +explain partitions update t1 set b=12345 where a in (32,33); +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning +drop table t1; +# +# Tests for EXPLAIN INSERT ... VALUES +# +create table t1 (a int, key(a)); +explain insert into t1 values (1),(2),(3); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +insert into t1 values (1),(2),(3); +create table t2 (a int, b int); +explain insert into t2 values +(10, 1+(select max(a) from t1)), +(11, 1+(select max(a+1) from t1)); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t2 ALL NULL NULL NULL NULL NULL NULL +3 SUBQUERY t1 index NULL a 5 NULL 3 Using index +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +drop table t1,t2; +# +# MDEV-5122: "Commands out of sync", "Malformed packet" or client hang up on unique key violation +# +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +drop function if exists f1; +create table t1 (a int, unique(a)); +create function f1(x int) +returns int +begin +insert into t1 values(x),(x); +return 10; +end| +select f1(100); +ERROR 23000: Duplicate entry '100' for key 'a' +select 'OK'; +OK +OK +drop function f1; +drop table t1; diff --git a/mysql-test/r/explain_slowquerylog.result b/mysql-test/r/explain_slowquerylog.result new file mode 100644 index 00000000000..9d25cf06275 --- /dev/null +++ b/mysql-test/r/explain_slowquerylog.result @@ -0,0 +1,41 @@ +drop table if exists t0,t1; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +explain select * from t0 where a < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +# +# MDEV-5045: Server crashes in QPF_query::print_explain with log_slow_verbosity='query_plan,explain' +# +set autocommit=1; +drop table t0; +# +# MDEV-5047 virtual THD::~THD(): Assertion `status_var.memory_used == 0' fails on disconnect +# +ALTER TABLE nonexisting ENABLE KEYS; +ERROR 42S02: Table 'test.nonexisting' doesn't exist +SHOW WARNINGS; +Level Code Message +Error 1146 Table 'test.nonexisting' doesn't exist +SELECT 1; +1 +1 +# +# MDEV-5060 Server crashes on EXPLAIN EXTENDED or EXPLAIN PARTITIONS with explain in slow_log +# +EXPLAIN PARTITIONS SELECT 1 ; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +# +# MDEV-5106: Server crashes in Explain_union::print_explain on ER_TOO_BIG_SELECT with explain in slow log +# +CREATE TABLE t1 (i INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +SET max_join_size = 10; +( SELECT ta.* FROM t1 ta, t1 tb ) UNION ( SELECT * FROM t1 ); +ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay +SELECT 'Server still alive?' as 'Yes'; +Yes +Server still alive? +DROP TABLE t1; diff --git a/mysql-test/r/grant_explain_non_select.result b/mysql-test/r/grant_explain_non_select.result new file mode 100644 index 00000000000..85b0ae5c4b4 --- /dev/null +++ b/mysql-test/r/grant_explain_non_select.result @@ -0,0 +1,178 @@ +CREATE DATABASE privtest_db; +CREATE TABLE privtest_db.t1 (a INT); +CREATE TABLE privtest_db.t2 (a INT); +INSERT INTO privtest_db.t2 VALUES (1), (2), (3); +GRANT USAGE ON *.* TO 'privtest'@'localhost'; +GRANT SELECT ON privtest_db.t2 TO 'privtest'@'localhost'; +USE privtest_db; +EXPLAIN INSERT INTO t1 VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +INSERT INTO t1 VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +INSERT INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +GRANT INSERT ON privtest_db.t1 TO 'privtest'@'localhost'; +EXPLAIN INSERT INTO t1 VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +INSERT INTO t1 VALUES (10); +EXPLAIN INSERT INTO t1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +INSERT INTO t1 SELECT * FROM t2; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +EXPLAIN REPLACE INTO t1 VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 VALUES (10); +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT, DELETE command denied to user 'privtest'@'localhost' for table 't1' +GRANT INSERT ON privtest_db.t1 TO 'privtest'@'localhost'; +EXPLAIN REPLACE INTO t1 VALUES (10); +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 VALUES (10); +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +REVOKE INSERT ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT DELETE ON privtest_db.t1 TO 'privtest'@'localhost'; +EXPLAIN REPLACE INTO t1 VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 VALUES (10); +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +REPLACE INTO t1 SELECT * FROM t2; +ERROR 42000: INSERT command denied to user 'privtest'@'localhost' for table 't1' +GRANT INSERT, DELETE ON privtest_db.t1 TO 'privtest'@'localhost'; +EXPLAIN REPLACE INTO t1 VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +REPLACE INTO t1 VALUES (10); +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +REPLACE INTO t1 SELECT * FROM t2; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +EXPLAIN UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +GRANT UPDATE ON privtest_db.t1 TO 'privtest'@'localhost'; +EXPLAIN UPDATE t1 SET a = a + 1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +UPDATE t1 SET a = a + 1; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +REVOKE UPDATE ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT SELECT ON privtest_db.t1 TO 'privtest'@'localhost'; +EXPLAIN UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1 SET a = a + 1; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +ERROR 42000: UPDATE command denied to user 'privtest'@'localhost' for table 't1' +GRANT UPDATE, SELECT ON privtest_db.t1 TO 'privtest'@'localhost'; +EXPLAIN UPDATE t1 SET a = a + 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 +UPDATE t1 SET a = a + 1; +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +EXPLAIN DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +GRANT DELETE ON privtest_db.t1 TO 'privtest'@'localhost'; +EXPLAIN DELETE FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +DELETE FROM t1 WHERE a = 10; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for column 'a' in table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: SELECT command denied to user 'privtest'@'localhost' for table 't1' +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT SELECT ON privtest_db.t1 TO 'privtest'@'localhost'; +EXPLAIN DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 WHERE a = 10; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +ERROR 42000: DELETE command denied to user 'privtest'@'localhost' for table 't1' +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT DELETE, SELECT ON privtest_db.t1 TO 'privtest'@'localhost'; +EXPLAIN DELETE FROM t1 WHERE a = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +DELETE FROM t1 WHERE a = 10; +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using where +DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +CREATE VIEW privtest_db.v1 (a) AS SELECT a FROM privtest_db.t1; +GRANT SELECT, INSERT, UPDATE, DELETE ON privtest_db.v1 TO 'privtest'@'localhost'; +EXPLAIN SELECT * FROM v1; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +SELECT * FROM v1; +a +11 +4 +4 +11 +4 +4 +EXPLAIN INSERT INTO v1 VALUES (10); +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v1 VALUES (10); +EXPLAIN INSERT INTO v1 SELECT * FROM t2; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +INSERT INTO v1 SELECT * FROM t2; +EXPLAIN REPLACE INTO v1 VALUES (10); +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v1 VALUES (10); +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +REPLACE INTO v1 SELECT * FROM t2; +EXPLAIN UPDATE v1 SET a = a + 1; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1 SET a = a + 1; +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; +EXPLAIN DELETE FROM v1 WHERE a = 10; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 WHERE a = 10; +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table +DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; +DROP USER 'privtest'@localhost; +USE test; +DROP DATABASE privtest_db; diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result index a51798a5883..5dbe01eef4f 100644 --- a/mysql-test/r/limit_rows_examined.result +++ b/mysql-test/r/limit_rows_examined.result @@ -439,7 +439,7 @@ from (select * from t1 where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0)) as tmp LIMIT ROWS EXAMINED 11; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where select * from (select * from t1 diff --git a/mysql-test/r/myisam_explain_non_select_all.result b/mysql-test/r/myisam_explain_non_select_all.result new file mode 100644 index 00000000000..86e3ffbff6c --- /dev/null +++ b/mysql-test/r/myisam_explain_non_select_all.result @@ -0,0 +1,2907 @@ +set @save_storage_engine= @@session.default_storage_engine; +set session default_storage_engine = MyISAM; +#1 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +# +# query: UPDATE t1 SET a = 10 WHERE a < 10 +# select: SELECT * FROM t1 WHERE a < 10 +# +EXPLAIN UPDATE t1 SET a = 10 WHERE a < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a < 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 10) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 4 +Handler_update 3 + +DROP TABLE t1; +#2 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +# +# query: DELETE FROM t1 WHERE a < 10 +# select: SELECT * FROM t1 WHERE a < 10 +# +EXPLAIN DELETE FROM t1 WHERE a < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE a < 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 10) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 3 +Handler_read_rnd_next 4 + +DROP TABLE t1; +#3 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +# +# query: DELETE FROM t1 USING t1 WHERE a = 1 +# select: SELECT * FROM t1 WHERE a = 1 +# +EXPLAIN DELETE FROM t1 USING t1 WHERE a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE a = 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 1) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 1 +Handler_read_rnd_next 4 + +DROP TABLE t1; +#4 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +# +# query: UPDATE t1, t2 SET t1.a = 10 WHERE t1.a = 1 +# select: SELECT * FROM t1, t2 WHERE t1.a = 1 +# +EXPLAIN UPDATE t1, t2 SET t1.a = 10 WHERE t1.a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1, t2 SET t1.a = 10 WHERE t1.a = 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE t1.a = 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = 1) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 8 +Handler_update 1 + +DROP TABLE t1, t2; +#5 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +# +# query: UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1 +# select: SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a = 1 +# +EXPLAIN UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t11 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a = 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t11 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2` where (`test`.`t11`.`a` = 1) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 12 +Handler_update 1 + +DROP TABLE t1, t2; +#6 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +# +# query: UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3) +# select: SELECT * FROM t1 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3) +# +EXPLAIN UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +Handler_read_rnd_next 1 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`b` < 3) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 5 +Handler_update 3 + +DROP TABLE t1, t2; +#7 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +# +# query: UPDATE t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3) +# select: SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3) +# +EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3)) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 7 +Handler_update 2 + +DROP TABLE t1, t2; +#7 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +# +# query: UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) +# select: SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) +# +EXPLAIN UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) join `test`.`t2` where ((`test`.`t2`.`b` < 3)) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 12 +# Status of testing query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 16 +Handler_update 2 + +DROP TABLE t1, t2; +#8 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +# +# query: UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10 +# select: SELECT * FROM t1 t11, (SELECT * FROM t2) t12 +# +EXPLAIN UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t11 ALL NULL NULL NULL NULL 3 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t11 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd 3 +Handler_read_rnd_deleted 1 +Handler_read_rnd_next 24 +Handler_update 3 + +DROP TABLE t1, t2; +#9 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +# +# query: UPDATE t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10 +# select: SELECT * FROM t1 t11, (SELECT 1 FROM DUAL) t12 +# +EXPLAIN UPDATE t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 1 +1 PRIMARY t11 ALL NULL NULL NULL NULL 3 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +# Status of EXPLAIN EXTENDED query +Variable_name Value +Handler_read_rnd_next 1 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT 1 FROM DUAL) t12; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 +1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select `test`.`t11`.`a` AS `a`,1 AS `1` from `test`.`t1` `t11` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +Handler_read_rnd_next 1 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 5 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd 3 +Handler_read_rnd_next 9 +Handler_update 3 + +DROP TABLE t1, t2; +#10 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +# +# query: UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1 +# select: SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a > 1 +# +EXPLAIN UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t11 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a > 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t11 ALL NULL NULL NULL NULL 3 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2` where (`test`.`t11`.`a` > 1) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 16 +Handler_update 2 + +DROP TABLE t1, t2; +#11 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +# +# query: DELETE FROM t1 WHERE a > 1 LIMIT 1 +# select: SELECT * FROM t1 WHERE a > 1 LIMIT 1 +# +EXPLAIN DELETE FROM t1 WHERE a > 1 LIMIT 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE a > 1 LIMIT 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > 1 LIMIT 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 1) limit 1 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 2 +# Status of testing query execution: +Variable_name Value +Handler_delete 1 +Handler_read_rnd_next 2 + +DROP TABLE t1; +#12 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +# +# query: DELETE FROM t1 WHERE 0 +# select: SELECT * FROM t1 WHERE 0 +# +EXPLAIN DELETE FROM t1 WHERE 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +# Status of testing query execution: +Variable_name Value + +DROP TABLE t1; +#13 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +# +# query: DELETE FROM t1 USING t1 WHERE 0 +# select: SELECT * FROM t1 WHERE 0 +# +EXPLAIN DELETE FROM t1 USING t1 WHERE 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +# Status of testing query execution: +Variable_name Value + +DROP TABLE t1; +#14 +CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b)); +INSERT INTO t1 VALUES (3, 3), (7, 7); +# +# query: DELETE FROM t1 WHERE a = 3 +# select: SELECT * FROM t1 WHERE a = 3 +# +EXPLAIN DELETE FROM t1 WHERE a = 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 1 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE a = 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 5 NULL 1 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 const a a 5 const 1 100.00 +Warnings: +Note 1003 select 3 AS `a`,3 AS `b` from `test`.`t1` where 1 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +Handler_read_key 1 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +# Status of testing query execution: +Variable_name Value +Handler_delete 1 +Handler_read_key 1 + +DROP TABLE t1; +#15 +CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b)); +INSERT INTO t1 VALUES (3, 3), (7, 7); +# +# query: DELETE FROM t1 WHERE a < 3 +# select: SELECT * FROM t1 WHERE a < 3 +# +EXPLAIN DELETE FROM t1 WHERE a < 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 1 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE a < 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 5 NULL 1 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a a 5 NULL 1 100.00 Using index condition +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` < 3) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 + +DROP TABLE t1; +#16 +CREATE TABLE t1 ( a int PRIMARY KEY ); +# +# query: DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a +# select: SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a +# +EXPLAIN DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 select NULL AS `a` from `test`.`t1` where 0 order by NULL +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +Handler_read_rnd_next 1 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 1 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 + +INSERT INTO t1 VALUES (1), (2), (3); +# +# query: DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a +# select: SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a +# +EXPLAIN DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 0) order by `test`.`t1`.`a` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +Handler_read_next 3 +# Status of testing query execution: +Variable_name Value +Handler_delete 3 +Handler_read_key 1 +Handler_read_next 3 + +DROP TABLE t1; +#17 +CREATE TABLE t1(a INT PRIMARY KEY); +INSERT INTO t1 VALUES (4),(3),(1),(2); +# +# query: DELETE FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1 +# select: SELECT * FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1 +# +EXPLAIN DELETE FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (@a:=`test`.`t1`.`a`) order by `test`.`t1`.`a` limit 1 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +# Status of testing query execution: +Variable_name Value +Handler_delete 1 +Handler_read_first 1 + +DROP TABLE t1; +#18 +CREATE TABLE t1 (a DATE, b TIME, c INT, KEY c(c), KEY b(b), KEY a(a)); +INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), (); +UPDATE t1 SET a = c, b = c; +# +# query: DELETE FROM t1 ORDER BY a ASC, b ASC LIMIT 1 +# select: SELECT * FROM t1 ORDER BY a ASC, b ASC LIMIT 1 +# +EXPLAIN DELETE FROM t1 ORDER BY a ASC, b ASC LIMIT 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 ORDER BY a ASC, b ASC LIMIT 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 ORDER BY a ASC, b ASC LIMIT 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` limit 1 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 11 +Sort_rows 1 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_delete 1 +Handler_read_rnd 1 +Handler_read_rnd_next 11 +Sort_rows 10 +Sort_scan 1 + +DROP TABLE t1; +#19 +CREATE TABLE t1 (a1 INT NOT NULL, b1 INT NOT NULL); +CREATE TABLE t2 (a2 INT NOT NULL, b2 INT NOT NULL, PRIMARY KEY (a2,b2)); +CREATE TABLE t3 (a3 INT NOT NULL, b3 INT NOT NULL, PRIMARY KEY (a3,b3)); +INSERT INTO t1 VALUES (1,1), (2,1), (1,3); +INSERT INTO t2 VALUES (1,1), (2,2), (3,3); +INSERT INTO t3 VALUES (1,1), (2,1), (1,3); +# +# query: DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3 +# select: SELECT * FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3 +# +EXPLAIN DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b2,test.t1.b1 1 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 100.00 +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b2,test.t1.b1 1 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 100.00 Using index +1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b2,test.t1.b1 1 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`b3` AS `b3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`a2` = `test`.`t1`.`a1`) and (`test`.`t3`.`a3` = `test`.`t2`.`b2`) and (`test`.`t3`.`b3` = `test`.`t1`.`b1`)) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 6 +Handler_read_next 3 +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 8 +Handler_read_key 6 +Handler_read_next 3 +Handler_read_rnd 5 +Handler_read_rnd_next 4 + +DROP TABLE t1, t2, t3; +#20 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (1), (2), (3); +# +# query: UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2) +# select: SELECT * FROM t1 WHERE a IN (SELECT a FROM t2) +# +EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where 1 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 10 +Handler_update 3 + +DROP TABLE t1, t2; +#21 +CREATE TABLE t1 (a1 INT); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +CREATE TABLE t2 (a2 VARCHAR(10)); +INSERT INTO t2 VALUES (1), (2), (3), (4), (5); +SET @save_optimizer_switch= @@optimizer_switch; +# +# query: DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) +# select: SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) +# +EXPLAIN DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a1` AS `a1` from `test`.`t1` where <expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(select `test`.`t2`.`a2` from `test`.`t2` where ((`test`.`t2`.`a2` > 2) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`a2`))))) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 5 +Handler_read_rnd_next 30 +# Status of testing query execution: +Variable_name Value +Handler_delete 3 +Handler_read_rnd_next 30 + +SET @@optimizer_switch= @save_optimizer_switch; +TRUNCATE t1; +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +# +# query: DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) +# select: SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) +# +EXPLAIN DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a1` AS `a1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`a2` > 2) and (`test`.`t1`.`a1` = `test`.`t2`.`a2`)) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 12 +# Status of testing query execution: +Variable_name Value +Handler_delete 3 +Handler_read_rnd_next 30 + +DROP TABLE t1, t2; +#22 +CREATE TABLE t1 (i INT, j INT); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +# +# query: UPDATE t1 SET i = 10 +# select: SELECT * FROM t1 +# +EXPLAIN UPDATE t1 SET i = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 SET i = 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +Warnings: +Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j` from `test`.`t1` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 6 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 6 +Handler_update 5 + +DROP TABLE t1; +#23 +CREATE TABLE t1 (i INT, j INT); +INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); +# +# query: DELETE FROM t1 +# select: SELECT * FROM t1 +# +EXPLAIN DELETE FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL 5 Deleting all rows +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL 5 NULL Deleting all rows +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 +Warnings: +Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j` from `test`.`t1` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 6 +# Status of testing query execution: +Variable_name Value + +DROP TABLE t1; +#24 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; +INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; +# +# query: DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# +EXPLAIN DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL a 15 NULL 5 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +Handler_read_next 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +Handler_read_first 1 +Handler_read_next 4 + +DROP TABLE t1, t2; +#25 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (i INT); +# +# query: INSERT INTO t2 SELECT * FROM t1 +# select: SELECT * FROM t1 +# +EXPLAIN INSERT INTO t2 SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED INSERT INTO t2 SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 4 +Handler_write 3 + +DROP TABLE t1, t2; +#26 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (i INT); +# +# query: REPLACE INTO t2 SELECT * FROM t1 +# select: SELECT * FROM t1 +# +EXPLAIN REPLACE INTO t2 SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED REPLACE INTO t2 SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 4 +Handler_write 3 + +DROP TABLE t1, t2; +#27 +CREATE TABLE t1 (i INT); +# +# query: INSERT INTO t1 SET i = 10 +# select: +# +EXPLAIN INSERT INTO t1 SET i = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED INSERT INTO t1 SET i = 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL +# Status of EXPLAIN EXTENDED query +Variable_name Value +# Status of testing query execution: +Variable_name Value +Handler_write 1 + +DROP TABLE t1; +#28 +CREATE TABLE t1 (i INT); +# +# query: REPLACE INTO t1 SET i = 10 +# select: +# +EXPLAIN REPLACE INTO t1 SET i = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED REPLACE INTO t1 SET i = 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL +# Status of EXPLAIN EXTENDED query +Variable_name Value +# Status of testing query execution: +Variable_name Value +Handler_write 1 + +DROP TABLE t1; +#29 +CREATE TABLE t1 (a INT, i INT PRIMARY KEY); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +# +# query: DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +# select: SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +# +EXPLAIN DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 8 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 8 100.00 Using index condition +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`i` AS `i` from `test`.`t1` where ((`test`.`t1`.`i` > 10) and (`test`.`t1`.`i` <= 18)) order by `test`.`t1`.`i` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_next 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +Handler_read_key 1 +Handler_read_next 4 + +DROP TABLE t1; +#30 +CREATE TABLE t1(a INT, i CHAR(2), INDEX(i(1))); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +# +# query: DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +# select: SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +# +EXPLAIN DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL i NULL NULL NULL 26 100.00 Using where; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`i` AS `i` from `test`.`t1` where ((`test`.`t1`.`i` > 10) and (`test`.`t1`.`i` <= 18)) order by `test`.`t1`.`i` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 27 +Sort_rows 5 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +Handler_read_rnd 5 +Handler_read_rnd_next 27 +Sort_rows 8 +Sort_scan 1 + +DROP TABLE t1; +#31 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; +# +# query: DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# +EXPLAIN DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 27 +Sort_rows 1 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_delete 1 +Handler_read_rnd 1 +Handler_read_rnd_next 27 +Sort_rows 1 +Sort_scan 1 + +DROP TABLE t1, t2; +#32 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; +INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; +# +# query: DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# +EXPLAIN DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL a 15 NULL 5 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +Handler_read_next 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +Handler_read_first 1 +Handler_read_next 4 + +DROP TABLE t1, t2; +#33 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c)); +INSERT INTO t2 SELECT i, i, i, i FROM t1; +# +# query: DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# +EXPLAIN DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 27 +Sort_rows 1 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_delete 1 +Handler_read_rnd 1 +Handler_read_rnd_next 27 +Sort_rows 1 +Sort_scan 1 + +DROP TABLE t1, t2; +#34 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) +ENGINE=HEAP; +INSERT INTO t2 SELECT i, i, i, i FROM t1; +# +# query: DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# +EXPLAIN DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd 1 +Handler_read_rnd_next 27 +Sort_rows 1 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_delete 1 +Handler_read_rnd 1 +Handler_read_rnd_next 27 +Sort_rows 1 +Sort_scan 1 + +DROP TABLE t1, t2; +#35 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35),(36),(37),(38),(39), +(40),(41),(42); +CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2)); +INSERT INTO t2 (key1, key2) SELECT i, i FROM t1; +# +# query: DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1 +# select: SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1 +# +EXPLAIN DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 Using sort_union(key1,key2); Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort +Warnings: +Note 1003 select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 2 +Handler_read_next 7 +Handler_read_rnd 4 +Sort_range 1 +Sort_rows 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 4 +Handler_read_key 2 +Handler_read_next 7 +Handler_read_rnd 8 +Sort_range 1 +Sort_rows 4 + +DROP TABLE t1, t2; +#36 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, i INT PRIMARY KEY); +INSERT INTO t2 (i) SELECT i FROM t1; +# +# query: DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5 +# select: SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5 +# +EXPLAIN DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` desc limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_prev 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +Handler_read_key 1 +Handler_read_prev 4 + +DROP TABLE t1, t2; +#37 +CREATE TABLE t1 (i INT); +INSERT INTO t1 (i) VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); +INSERT INTO t2 SELECT i, i, i FROM t1; +# +# query: DELETE FROM t2 ORDER BY a, b DESC LIMIT 5 +# select: SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5 +# +EXPLAIN DELETE FROM t2 ORDER BY a, b DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t2 ORDER BY a, b DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 27 +Sort_rows 5 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +Handler_read_rnd 5 +Handler_read_rnd_next 27 +Sort_rows 26 +Sort_scan 1 + +DROP TABLE t1, t2; +#38 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b)); +INSERT INTO t2 (a, b) SELECT i, i FROM t1; +INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2; +# +# query: DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5 +# select: SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5 +# +EXPLAIN DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL a 6 NULL 5 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index NULL a 6 NULL 5 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index NULL a 6 NULL 5 100.00 +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_last 1 +Handler_read_prev 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +Handler_read_last 1 +Handler_read_prev 4 + +DROP TABLE t1, t2; +#39 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, i INT PRIMARY KEY); +INSERT INTO t2 (i) SELECT i FROM t1; +# +# query: UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +# select: SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +# +EXPLAIN UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 Using where; Using buffer +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where; Using buffer +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using index condition +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_next 4 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_next 4 +Handler_read_rnd 5 +Handler_update 5 + +DROP TABLE t1, t2; +#40 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1))); +INSERT INTO t2 (i) SELECT i FROM t1; +# +# query: UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +# select: SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 +# +EXPLAIN UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL i NULL NULL NULL 26 100.00 Using where; Using filesort +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 27 +Sort_rows 5 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd 5 +Handler_read_rnd_next 27 +Handler_update 5 +Sort_rows 5 +Sort_scan 1 + +DROP TABLE t1, t2; +#41 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; +# +# query: UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 +# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# +EXPLAIN UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 27 +Sort_rows 1 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd 1 +Handler_read_rnd_next 27 +Handler_update 1 +Sort_rows 1 +Sort_scan 1 + +DROP TABLE t1, t2; +#42 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); +INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; +INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; +# +# query: UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 +# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# +EXPLAIN UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL a 15 NULL 5 Using where; Using buffer +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where; Using buffer +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +Handler_read_next 4 +# Status of testing query execution: +Variable_name Value +Handler_read_first 1 +Handler_read_next 4 +Handler_read_rnd 5 +Handler_update 5 + +DROP TABLE t1, t2; +#43 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c)); +INSERT INTO t2 SELECT i, i, i, i FROM t1; +# +# query: UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 +# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# +EXPLAIN UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 27 +Sort_rows 1 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd 1 +Handler_read_rnd_next 27 +Sort_rows 1 +Sort_scan 1 + +DROP TABLE t1, t2; +#44 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) +ENGINE=HEAP; +INSERT INTO t2 SELECT i, i, i, i FROM t1; +# +# query: UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 +# select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 +# +EXPLAIN UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd 1 +Handler_read_rnd_next 27 +Sort_rows 1 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd 1 +Handler_read_rnd_next 27 +Sort_rows 1 +Sort_scan 1 + +DROP TABLE t1, t2; +#45 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35),(36),(37),(38),(39), +(40),(41),(42); +CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2)); +INSERT INTO t2 (key1, key2) SELECT i, i FROM t1; +# +# query: UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1 +# select: SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1 +# +EXPLAIN UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 Using sort_union(key1,key2); Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort +Warnings: +Note 1003 select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 2 +Handler_read_next 7 +Handler_read_rnd 4 +Sort_range 1 +Sort_rows 4 +# Status of testing query execution: +Variable_name Value +Handler_read_key 2 +Handler_read_next 7 +Handler_read_rnd 8 +Handler_update 4 +Sort_range 1 +Sort_rows 4 + +DROP TABLE t1, t2; +#46 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2(a INT, i INT PRIMARY KEY); +INSERT INTO t2 (i) SELECT i FROM t1; +# +# query: UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5 +# select: SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5 +# +EXPLAIN UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 Using where; Using buffer +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where; Using buffer +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` desc limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_prev 4 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_prev 4 +Handler_read_rnd 5 +Handler_update 5 + +DROP TABLE t1, t2; +#47 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); +INSERT INTO t2 SELECT i, i, i FROM t1; +# +# query: UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5 +# select: SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5 +# +EXPLAIN UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 27 +Sort_rows 5 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd 5 +Handler_read_rnd_next 27 +Handler_update 4 +Sort_rows 5 +Sort_scan 1 + +DROP TABLE t1, t2; +#48 +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b)); +INSERT INTO t2 (a, b) SELECT i, i FROM t1; +INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2; +# +# query: UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5 +# select: SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5 +# +EXPLAIN UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL a 6 NULL 5 Using buffer +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index NULL a 6 NULL 5 100.00 Using buffer +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 index NULL a 6 NULL 5 100.00 +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_last 1 +Handler_read_prev 4 +# Status of testing query execution: +Variable_name Value +Handler_read_last 1 +Handler_read_prev 4 +Handler_read_rnd 5 +Handler_update 5 + +DROP TABLE t1, t2; +#49 +CREATE TABLE t1 ( +pk INT NOT NULL AUTO_INCREMENT, +c1_idx CHAR(1) DEFAULT 'y', +c2 INT, +PRIMARY KEY (pk), +INDEX c1_idx (c1_idx) +); +INSERT INTO t1 VALUES (1,'y',1), (2,'n',2), (3,'y',3), (4,'n',4); +# +# query: UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 +# select: SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 +# +EXPLAIN UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using index condition; Using where; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_next 2 +Sort_range 1 +Sort_rows 2 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_next 2 +Handler_read_rnd 2 +Handler_update 2 +Sort_range 1 +Sort_rows 2 + +# +# query: DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 +# select: SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 +# +EXPLAIN DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using index condition; Using where; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1_idx` = 'y') order by `test`.`t1`.`pk` desc limit 2 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_next 2 +Sort_range 1 +Sort_rows 2 +# Status of testing query execution: +Variable_name Value +Handler_delete 2 +Handler_read_key 1 +Handler_read_next 2 +Handler_read_rnd 2 +Sort_range 1 +Sort_rows 2 + +DROP TABLE t1; +#50 +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY); +INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +# +# query: UPDATE t1 SET a=a+10 WHERE a > 34 +# select: SELECT * FROM t1 WHERE a > 34 +# +EXPLAIN UPDATE t1 SET a=a+10 WHERE a > 34; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where; Using buffer +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 SET a=a+10 WHERE a > 34; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using buffer +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > 34; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 34) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_next 2 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_next 2 +Handler_read_rnd 2 +Handler_update 2 + +DROP TABLE t1; +#51 +CREATE TABLE t1 (c1 INT, c2 INT, c3 INT); +CREATE TABLE t2 (c1 INT, c2 INT); +INSERT INTO t1 VALUES (1, 1, 10), (2, 2, 20); +# +# query: UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 +# select: SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 +# +EXPLAIN UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +Handler_read_rnd_next 1 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +Warnings: +Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,NULL AS `c1`,NULL AS `c2` from `test`.`t1` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +Handler_read_rnd_next 1 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 4 + +# +# query: UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 10 +# select: SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c3 = 10 +# +EXPLAIN UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +Handler_read_rnd_next 1 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c3 = 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 const row not found +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,NULL AS `c1`,NULL AS `c2` from `test`.`t1` where (`test`.`t1`.`c3` = 10) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +Handler_read_rnd_next 1 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 4 + +DROP TABLE t1, t2; +#52 +CREATE TABLE t1(f1 INT, f2 INT); +CREATE TABLE t2(f3 INT, f4 INT); +CREATE INDEX IDX ON t2(f3); +INSERT INTO t1 VALUES(1,0),(2,0); +INSERT INTO t2 VALUES(1,1),(2,2); +# +# query: UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) +# select: SELECT (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1 +# +EXPLAIN UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL IDX NULL NULL NULL 2 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +2 DEPENDENT SUBQUERY t2 ALL IDX NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +2 DEPENDENT SUBQUERY t2 ALL IDX NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 +Note 1003 select <expr_cache><`test`.`t1`.`f1`>((select max(`test`.`t2`.`f4`) from `test`.`t2` where (`test`.`t2`.`f3` = `test`.`t1`.`f1`))) AS `(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1)` from `test`.`t1` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 2 +Handler_read_rnd_next 9 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 9 +Handler_update 2 + +DROP TABLE t1, t2; +#55 +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1); +SET @a = NULL; +EXPLAIN DELETE FROM t1 WHERE (@a:= a); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where +DROP TABLE t1; +#56 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +DELETE FROM t1 USING t1 WHERE uknown_column = 12345; +ERROR 42S22: Unknown column 'uknown_column' in 'where clause' +EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE uknown_column = 12345; +ERROR 42S22: Unknown column 'uknown_column' in 'where clause' +DROP TABLE t1; +#57 +CREATE TABLE t1(f1 INT); +EXPLAIN EXTENDED UPDATE t1 SET f2=1 ORDER BY f2; +ERROR 42S22: Unknown column 'f2' in 'order clause' +UPDATE t1 SET f2=1 ORDER BY f2; +ERROR 42S22: Unknown column 'f2' in 'order clause' +DROP TABLE t1; +#62 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0), (1); +CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12; +# +# query: UPDATE v1 SET a = 1 WHERE a > 0 +# select: SELECT * FROM v1 WHERE a > 0 +# +EXPLAIN UPDATE v1 SET a = 1 WHERE a > 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t11 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t12 ALL NULL NULL NULL NULL 2 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE v1 SET a = 1 WHERE a > 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM v1 WHERE a > 0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t11`.`a` AS `a`,`test`.`t12`.`a` AS `b` from `test`.`t1` `t11` join `test`.`t1` `t12` where (`test`.`t11`.`a` > 0) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 6 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd 1 +Handler_read_rnd_deleted 1 +Handler_read_rnd_next 8 + +# +# query: UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a +# select: SELECT * FROM t1, v1 WHERE t1.a = v1.a +# +EXPLAIN UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +1 SIMPLE t11 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t12 ALL NULL NULL NULL NULL 2 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where +1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1, v1 WHERE t1.a = v1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 +1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t11`.`a` AS `a`,`test`.`t12`.`a` AS `b` from `test`.`t1` join `test`.`t1` `t11` join `test`.`t1` `t12` where (`test`.`t11`.`a` = `test`.`t1`.`a`) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 9 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd 2 +Handler_read_rnd_deleted 1 +Handler_read_rnd_next 18 +Handler_update 1 + +DROP TABLE t1; +DROP VIEW v1; +#63 +CREATE TABLE t1 (a INT, PRIMARY KEY(a)); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +CREATE VIEW v1 (a) AS SELECT a FROM t1; +# +# query: DELETE FROM v1 WHERE a < 4 +# select: SELECT * FROM v1 WHERE a < 4 +# +EXPLAIN DELETE FROM v1 WHERE a < 4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE FROM v1 WHERE a < 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM v1 WHERE a < 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 4) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +Handler_read_next 3 +# Status of testing query execution: +Variable_name Value +Handler_delete 3 +Handler_read_first 1 +Handler_read_next 3 + +DROP TABLE t1; +DROP VIEW v1; +#64 +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a)); +INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10); +CREATE TABLE t2 (x INT); +INSERT INTO t2 VALUES (1), (2), (3), (4); +CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1; +# +# query: DELETE v1 FROM t2, v1 WHERE t2.x = v1.a +# select: SELECT * FROM t2, v1 WHERE t2.x = v1.a +# +EXPLAIN DELETE v1 FROM t2, v1 WHERE t2.x = v1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE v1 FROM t2, v1 WHERE t2.x = v1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2, v1 WHERE t2.x = v1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00 +Warnings: +Note 1003 select `test`.`t2`.`x` AS `x`,`test`.`t1`.`a` AS `a`,(`test`.`t1`.`b` + 1) AS `c` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`x`) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 4 +Handler_read_rnd_next 5 +# Status of testing query execution: +Variable_name Value +Handler_delete 4 +Handler_read_key 4 +Handler_read_rnd 4 +Handler_read_rnd_next 5 + +DROP TABLE t1,t2; +DROP VIEW v1; +#65 +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a)); +INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10); +CREATE TABLE t2 (x INT); +INSERT INTO t2 VALUES (1), (2), (3), (4); +CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1; +# +# query: DELETE v1 FROM t2, v1 WHERE t2.x = v1.a +# select: SELECT * FROM t2, v1 WHERE t2.x = v1.a +# +EXPLAIN DELETE v1 FROM t2, v1 WHERE t2.x = v1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED DELETE v1 FROM t2, v1 WHERE t2.x = v1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t2, v1 WHERE t2.x = v1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00 +Warnings: +Note 1003 select `test`.`t2`.`x` AS `x`,`test`.`t1`.`a` AS `a`,(`test`.`t1`.`b` + 1) AS `c` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`x`) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 4 +Handler_read_rnd_next 5 +# Status of testing query execution: +Variable_name Value +Handler_delete 4 +Handler_read_key 4 +Handler_read_rnd 4 +Handler_read_rnd_next 5 + +DROP TABLE t1,t2; +DROP VIEW v1; +#66 +CREATE TABLE t1 (a INT); +CREATE VIEW v1 (x) AS SELECT a FROM t1; +# +# query: INSERT INTO v1 VALUES (10) +# select: SELECT NULL +# +EXPLAIN INSERT INTO v1 VALUES (10); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED INSERT INTO v1 VALUES (10); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select NULL AS `NULL` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +# Status of testing query execution: +Variable_name Value +Handler_write 1 + +DROP TABLE t1; +DROP VIEW v1; +#67 +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +CREATE VIEW v1 (x) AS SELECT b FROM t2; +# +# query: INSERT INTO v1 SELECT * FROM t1 +# select: SELECT * FROM t1 +# +EXPLAIN INSERT INTO v1 SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED INSERT INTO v1 SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found +# Status of EXPLAIN EXTENDED query +Variable_name Value +Handler_read_rnd_next 1 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 const row not found +Warnings: +Note 1003 select NULL AS `a` from `test`.`t1` +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +Handler_read_rnd_next 1 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 1 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 1 + +DROP TABLE t1, t2; +DROP VIEW v1; +#68 +CREATE TABLE t1 (i INT); +EXPLAIN INSERT DELAYED INTO t1 VALUES (1); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +DROP TABLE t1; +#69 +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (1), (2), (3); +# +# query: UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) +# select: SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) +# +EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 5 func 2 +3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 5 func 2 100.00 +3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where 1 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 10 +Sort_rows 3 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 8 +Handler_update 1 +Sort_rows 3 +Sort_scan 1 + +# +# query: UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) +# select: SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) +# +EXPLAIN UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 +3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where 1 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 10 +Sort_rows 3 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 10 +Sort_rows 3 +Sort_scan 1 + +# +# query: UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) +# select: SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) +# +EXPLAIN UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3 +4 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 3 +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3 100.00 +4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3 100.00 +4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where 1 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 10 +Sort_rows 3 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 10 +Sort_rows 3 +Sort_scan 1 + +DROP TABLE t1,t2; +#70 +CREATE TABLE t1 (c1 INT KEY); +CREATE TABLE t2 (c2 INT); +CREATE TABLE t3 (c3 INT); +EXPLAIN EXTENDED UPDATE t3 SET c3 = ( +SELECT COUNT(d1.c1) +FROM ( +SELECT a11.c1 FROM t1 AS a11 +STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 +JOIN t1 AS a12 ON a12.c1 = a11.c1 +) d1 +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 0 100.00 +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +DROP TABLE t1, t2, t3; +#73 +CREATE TABLE t1 (id INT); +CREATE TABLE t2 (id INT); +INSERT INTO t1 VALUES (1), (2); +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 0 const row not found; Using temporary; Using filesort +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 +DROP TABLE t1,t2; +#74 +CREATE TABLE t1(a INT PRIMARY KEY) ENGINE=InnoDB; +Warnings: +Warning 1286 Unknown storage engine 'InnoDB' +Warning 1266 Using storage engine MyISAM for table 't1' +INSERT INTO t1 VALUES (1), (2), (3), (4), (5); +# used key is modified & Using temporary +# +# query: UPDATE t1 SET a=a+1 WHERE a>10 +# select: SELECT a t1 FROM t1 WHERE a>10 +# +EXPLAIN UPDATE t1 SET a=a+1 WHERE a>10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using buffer +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 SET a=a+1 WHERE a>10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using buffer +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT a t1 FROM t1 WHERE a>10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 5 20.00 Using where; Using index +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1` from `test`.`t1` where (`test`.`t1`.`a` > 10) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +Handler_read_next 5 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 + +# used key is modified & Using filesort +# +# query: UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20 +# select: SELECT a t1 FROM t1 WHERE a>10 ORDER BY a+20 +# +EXPLAIN UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT a t1 FROM t1 WHERE a>10 ORDER BY a+20; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 5 20.00 Using where; Using index; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`a` AS `t1` from `test`.`t1` where (`test`.`t1`.`a` > 10) order by (`test`.`t1`.`a` + 20) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 6 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 +Sort_range 1 + +DROP TABLE t1; +# +# Bug #12949629: CLIENT LOSES CONNECTION AFTER EXECUTING A PROCEDURE WITH +# EXPLAIN UPDATE/DEL/INS +# +CREATE TABLE t1 (i INT); +CREATE TABLE t2 (i INT); +CREATE PROCEDURE p1() BEGIN EXPLAIN INSERT INTO t1 VALUES (1);END| +CREATE PROCEDURE p2() BEGIN INSERT INTO t1 VALUES (1);END| +CREATE PROCEDURE p3() BEGIN EXPLAIN INSERT INTO t1 SELECT 1;END| +CREATE PROCEDURE p4() BEGIN INSERT INTO t1 SELECT 1;END| +CREATE PROCEDURE p5() BEGIN EXPLAIN REPLACE INTO t1 VALUES (1);END| +CREATE PROCEDURE p6() BEGIN REPLACE INTO t1 VALUES (1);END| +CREATE PROCEDURE p7() BEGIN EXPLAIN REPLACE INTO t1 SELECT 1;END| +CREATE PROCEDURE p8() BEGIN REPLACE INTO t1 SELECT 1;END| +CREATE PROCEDURE p9() BEGIN EXPLAIN UPDATE t1 SET i = 10;END| +CREATE PROCEDURE p10() BEGIN UPDATE t1 SET i = 10;END| +CREATE PROCEDURE p11() BEGIN EXPLAIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END| +CREATE PROCEDURE p12() BEGIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END| +CREATE PROCEDURE p13() BEGIN EXPLAIN DELETE FROM t1;END| +CREATE PROCEDURE p14() BEGIN DELETE FROM t1;END| +CREATE PROCEDURE p15() BEGIN EXPLAIN DELETE FROM t1 USING t1;END| +CREATE PROCEDURE p16() BEGIN DELETE FROM t1 USING t1;END| +CALL p16(); +DROP PROCEDURE p16; +CALL p15(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found +DROP PROCEDURE p15; +CALL p14(); +DROP PROCEDURE p14; +CALL p13(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL 0 Deleting all rows +DROP PROCEDURE p13; +CALL p12(); +DROP PROCEDURE p12; +CALL p11(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +DROP PROCEDURE p11; +CALL p10(); +DROP PROCEDURE p10; +CALL p9(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 0 +DROP PROCEDURE p9; +CALL p8(); +DROP PROCEDURE p8; +CALL p7(); +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 +DROP PROCEDURE p7; +CALL p6(); +DROP PROCEDURE p6; +CALL p5(); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +DROP PROCEDURE p5; +CALL p4(); +DROP PROCEDURE p4; +CALL p3(); +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 +DROP PROCEDURE p3; +CALL p2(); +DROP PROCEDURE p2; +CALL p1(); +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +DROP PROCEDURE p1; +DROP TABLE t1, t2; +# +set default_storage_engine= @save_storage_engine; +set optimizer_switch=default; diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index aa8bb67d58c..08531847c66 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -329,7 +329,7 @@ The following options may be given as the first argument: log if it is open. --log-slow-verbosity=name log-slow-verbosity=[value[,value ...]] where value is one - of 'innodb', 'query_plan' + of 'innodb', 'query_plan', 'explain' --log-tc=name Path to transaction coordinator log (used for transactions that affect more than one storage engine, when binary log is disabled). diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 837e13bf238..ac8771bd90a 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -393,7 +393,7 @@ and not exists (select * from orders where o_custkey = c_custkey) group by cntrycode order by cntrycode; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort +1 PRIMARY customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort 4 DEPENDENT SUBQUERY orders ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index 3 SUBQUERY customer ALL NULL NULL NULL NULL 150 100.00 Using where Warnings: @@ -434,7 +434,7 @@ and not exists (select * from orders where o_custkey = c_custkey) group by cntrycode order by cntrycode; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort +1 PRIMARY customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort 4 DEPENDENT SUBQUERY orders ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index 3 SUBQUERY customer ALL NULL NULL NULL NULL 150 91.00 Using where Warnings: diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index ec4f03ca56a..9bd92d7c7c8 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -396,7 +396,7 @@ and not exists (select * from orders where o_custkey = c_custkey) group by cntrycode order by cntrycode; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort +1 PRIMARY customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort 4 DEPENDENT SUBQUERY orders ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index 3 SUBQUERY customer ALL NULL NULL NULL NULL 150 100.00 Using where Warnings: @@ -437,7 +437,7 @@ and not exists (select * from orders where o_custkey = c_custkey) group by cntrycode order by cntrycode; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort +1 PRIMARY customer ALL NULL NULL NULL NULL 150 100.00 Using where; Using temporary; Using filesort 4 DEPENDENT SUBQUERY orders ref i_o_custkey i_o_custkey 5 dbt3_s001.customer.c_custkey 15 100.00 Using index 3 SUBQUERY customer ALL NULL NULL NULL NULL 150 91.00 Using where Warnings: diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result index be0b1c28b06..7e0ac0b277c 100644 --- a/mysql-test/r/show_explain.result +++ b/mysql-test/r/show_explain.result @@ -165,7 +165,11 @@ 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: Target is not running an EXPLAINable command +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 set debug_dbug=@old_debug; @@ -177,6 +181,8 @@ 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: Target is not running an EXPLAINable command +kill query $thr2; +ERROR 70100: Query execution was interrupted set debug_dbug=@old_debug; # # Attempt SHOW EXPLAIN for an UPDATE @@ -186,22 +192,38 @@ 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: Target is not running an EXPLAINable command +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 show explain for $thr2; -ERROR HY000: Target is not running an EXPLAINable command +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 drop table t2; set debug_dbug=@old_debug; # -# Attempt SHOW EXPLAIN for a DELETE +# Attempt SHOW EXPLAIN for a DELETE (UPD: now works) # 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: Target is not running an EXPLAINable command +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 show explain for $thr2; -ERROR HY000: Target is not running an EXPLAINable command +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 drop table t2; set debug_dbug=@old_debug; # @@ -220,13 +242,13 @@ Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ 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 +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 +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 a SUBQ @@ -327,7 +349,11 @@ 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: Target is not running an EXPLAINable command +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 SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a a 1 2 @@ -381,13 +407,8 @@ set debug_dbug='+d,show_explain_probe_join_exec_end'; SELECT * FROM v1, t2; show explain for $thr2; ERROR HY000: Target is not running an EXPLAINable command -a b -8 4 -8 5 -8 6 -8 7 -8 8 -8 9 +kill query $thr2; +ERROR 70100: Query execution was interrupted set debug_dbug=@old_debug; DROP VIEW v1; DROP TABLE t2, t3; @@ -412,7 +433,10 @@ 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: Target is not running an EXPLAINable command +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 select * from t0 where 1>10 a set debug_dbug=@old_debug; # @@ -424,7 +448,10 @@ 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: Target is not running an EXPLAINable command +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL no matching row in const table +Warnings: +Note 1003 select * from t0,t3 where t3.a=112233 a a set debug_dbug=@old_debug; drop table t3; @@ -529,7 +556,12 @@ 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: Target is not running an EXPLAINable command +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> const distinct_key distinct_key 8 const,const 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) +2 MATERIALIZED t2 index NULL a1 4 NULL 20 Using index +Warnings: +Note 1003 SELECT * FROM t2 WHERE (5, 78) IN (SELECT `a1`, MAX(`a1`) FROM t2 GROUP BY `a1`) pk a1 set debug_dbug=@old_debug; DROP TABLE t2; @@ -635,7 +667,7 @@ 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 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 Warnings: Note 1003 SELECT a + 1 FROM v1 a + 1 @@ -1045,7 +1077,7 @@ show explain for $thr2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 1 PRIMARY t2 ALL NULL NULL NULL NULL 20 -3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Query plan already deleted +3 SUBQUERY t3 ALL NULL NULL NULL NULL 20 Using where Warnings: Note 1003 SELECT max(a+b+c) FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias WHERE EXISTS ( SELECT * FROM t3 WHERE b = c ) OR a <= 10 @@ -1095,6 +1127,45 @@ set names default; # show explain for foo; ERROR HY000: You may only use constant expressions in this statement +# +# MDEV-411: SHOW EXPLAIN: For dependent subquery EXPLAIN produces type=index, key, 'Using where; Using index', +# while SHOW EXPLAIN says type=ALL, no key, 'Range checked for each record' +# +CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (7),(0); +CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t2 VALUES (0),(8); +explain +SELECT SUM(b) FROM ( SELECT * FROM t1 ) AS alias1, t2 +WHERE b <= ANY ( +SELECT a FROM t1 +WHERE a = b + SLEEP(0.2) OR a >= ( SELECT SUM(b) FROM t2 )); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 4 NULL 2 Using index +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT SUBQUERY t1 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1) +4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +set @show_explain_probe_select_id=1; +set debug_dbug='+d,show_explain_probe_join_exec_start'; +SELECT SUM(b) FROM ( SELECT * FROM t1 ) AS alias1, t2 +WHERE b <= ANY ( +SELECT a FROM t1 +WHERE a = b + SLEEP(0.2) OR a >= ( SELECT SUM(b) FROM t2 )); +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL a 4 NULL 2 Using index +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT SUBQUERY t1 ALL a NULL NULL NULL 2 Range checked for each record (index map: 0x1) +4 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1003 SELECT SUM(b) FROM ( SELECT * FROM t1 ) AS alias1, t2 +WHERE b <= ANY ( +SELECT a FROM t1 +WHERE a = b + SLEEP(0.2) OR a >= ( SELECT SUM(b) FROM t2 )) +SUM(b) +0 +set debug_dbug=@old_debug; +DROP TABLE t1,t2; # End drop table t0; set debug_sync='RESET'; diff --git a/mysql-test/r/show_explain_non_select.result b/mysql-test/r/show_explain_non_select.result new file mode 100644 index 00000000000..0bd1e959405 --- /dev/null +++ b/mysql-test/r/show_explain_non_select.result @@ -0,0 +1,44 @@ +drop table if exists t0, t1; +SET @old_debug= @@session.debug; +set debug_sync='RESET'; +create table t0 (a int) engine=myisam; +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); +create table t1 (a int, b int, filler char(100), key(a), key(b)); +insert into t1 +select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler' +from t0 A, t0 B, t0 C; +# +# Test SHOW EXPLAIN for single-table DELETE +# +set debug_dbug='+d,show_explain_probe_delete_exec_start'; +delete from t1 where a<10 and b+1>1000; +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 1 Using where +Warnings: +Note 1003 delete from t1 where a<10 and b+1>1000 +# +# Test SHOW EXPLAIN for multi-table DELETE +# +set @show_explain_probe_select_id=1; +set debug_dbug='+d,show_explain_probe_do_select'; +delete t1 from t1, t0 where t0.a=t1.a and t1.b +1 > 1000; +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 8 Using where +1 SIMPLE t1 ref a a 5 test.t0.a 4 Using where +Warnings: +Note 1003 delete t1 from t1, t0 where t0.a=t1.a and t1.b +1 > 1000 +# +# Test SHOW EXPLAIN for single-table UPDATE +# +set debug_dbug='+d,show_explain_probe_update_exec_start'; +update t1 set filler='filler-data-2' where a<10 and b+1>1000; +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 1 Using where +Warnings: +Note 1003 update t1 set filler='filler-data-2' where a<10 and b+1>1000 +drop table t0,t1; +set debug_dbug=@old_debug; +set debug_sync='RESET'; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 824079c3a59..81e0b2c84c0 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -10,7 +10,7 @@ select (select 2); 2 explain extended select (select 2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select 2 AS `(select 2)` @@ -734,7 +734,7 @@ id 1 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using index +1 SIMPLE t2 ref id id 5 const 1 100.00 Using index Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1) @@ -746,7 +746,7 @@ id 2 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index +1 SIMPLE t2 ref id id 5 const 1 100.00 Using where; Using index Warnings: Note 1249 Select 3 was reduced during optimization Note 1249 Select 2 was reduced during optimization @@ -880,7 +880,7 @@ select 10.5 > ANY (SELECT * from t1); 1 explain extended select (select a+1) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1249 Select 2 was reduced during optimization @@ -4554,7 +4554,7 @@ int_nokey int_key 0 0 EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY C ALL NULL NULL NULL NULL 20 100.00 Using where +1 SIMPLE C ALL NULL NULL NULL NULL 20 100.00 Using where DROP TABLE C; # End of test for bug#45061. # @@ -6112,7 +6112,7 @@ FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3 ) AS alias3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM ( SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 @@ -6157,7 +6157,7 @@ FROM t2 AS c_sq1_alias1 WHERE (c_sq1_alias1.col_int_nokey != @var2 OR c_sq1_alias1.pk != @var3)) ) AS alias3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1 SELECT * FROM ( SELECT sq4_alias1.* FROM t1 AS sq4_alias1 diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 890ba2bb2d7..426e1ba39f9 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -286,7 +286,7 @@ WHERE date < '2012-12-12 12:12:12' ORDER BY mirror_date ASC ) AS calculated_result; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE <derived2> ALL NULL NULL NULL NULL 2 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 2 DERIVED t1 range date date 9 NULL 2 Using index condition; Using where; Rowid-ordered scan; Using filesort SELECT * FROM ( SELECT node_uid, date, mirror_date, @result := 0 AS result @@ -309,7 +309,7 @@ WHERE date < '2012-12-12 12:12:12' ORDER BY mirror_date ASC ) AS calculated_result; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE <derived2> ALL NULL NULL NULL NULL 2 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 2 DERIVED t1 range date date 9 NULL 2 Using index condition; Using where; Using filesort SELECT * FROM ( SELECT node_uid, date, mirror_date, @result := 0 AS result diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 1a33fb0c6a3..24d9f0de35a 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1031,7 +1031,7 @@ update t22 set c = '2005-12-08 15:58:27' where a = 255; explain select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary +1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Start temporary; Using temporary; Using filesort 1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (flat, BNL join) 1 PRIMARY t22 ALL NULL NULL NULL NULL 26 Using where; End temporary; Using join buffer (flat, BNL join) 1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer (flat, BNL join) diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 5bae1453a5e..19d3d25148f 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1041,7 +1041,7 @@ update t22 set c = '2005-12-08 15:58:27' where a = 255; explain select t21.* from t21,t22 where t21.a = t22.a and t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start temporary +1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Start temporary; Using temporary; Using filesort 1 PRIMARY t12 hash_ALL NULL #hash#$hj 4 test.t11.a 8 Using where; Using join buffer (flat, BNLH join) 1 PRIMARY t22 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; End temporary; Using join buffer (incremental, BNLH join) 1 PRIMARY t21 hash_ALL NULL #hash#$hj 4 test.t11.a 26 Using where; Using join buffer (incremental, BNLH join) diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 85abcb0139d..b096d20c331 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -93,12 +93,12 @@ DROP TABLE t1,t2; # EXPLAIN SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1249 Select 2 was reduced during optimization DESCRIBE SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1249 Select 2 was reduced during optimization # None of the below should crash @@ -513,8 +513,8 @@ id select_type table type possible_keys key key_len ref rows Extra 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 -1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1 +1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) DEALLOCATE PREPARE stmt; @@ -551,8 +551,8 @@ id select_type table type possible_keys key key_len ref rows Extra 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 -1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1 +1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) DEALLOCATE PREPARE stmt; @@ -588,8 +588,8 @@ id select_type table type possible_keys key key_len ref rows Extra 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 5 -1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1 +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) DEALLOCATE PREPARE stmt; diff --git a/mysql-test/r/subselect_exists_to_in.result b/mysql-test/r/subselect_exists_to_in.result index 19ae87f473f..348e0a40d95 100644 --- a/mysql-test/r/subselect_exists_to_in.result +++ b/mysql-test/r/subselect_exists_to_in.result @@ -14,7 +14,7 @@ select (select 2); 2 explain extended select (select 2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select 2 AS `(select 2)` @@ -738,7 +738,7 @@ id 1 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using index +1 SIMPLE t2 ref id id 5 const 1 100.00 Using index Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1) @@ -750,7 +750,7 @@ id 2 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index +1 SIMPLE t2 ref id id 5 const 1 100.00 Using where; Using index Warnings: Note 1249 Select 3 was reduced during optimization Note 1249 Select 2 was reduced during optimization @@ -884,7 +884,7 @@ select 10.5 > ANY (SELECT * from t1); 1 explain extended select (select a+1) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1249 Select 2 was reduced during optimization @@ -4560,7 +4560,7 @@ int_nokey int_key 0 0 EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY C ALL NULL NULL NULL NULL 20 100.00 Using where +1 SIMPLE C ALL NULL NULL NULL NULL 20 100.00 Using where DROP TABLE C; # End of test for bug#45061. # @@ -6120,7 +6120,7 @@ FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3 ) AS alias3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM ( SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 @@ -6165,7 +6165,7 @@ FROM t2 AS c_sq1_alias1 WHERE (c_sq1_alias1.col_int_nokey != @var2 OR c_sq1_alias1.pk != @var3)) ) AS alias3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1 SELECT * FROM ( SELECT sq4_alias1.* FROM t1 AS sq4_alias1 diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 627e5b03e32..3cda876a840 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -17,7 +17,7 @@ select (select 2); 2 explain extended select (select 2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select 2 AS `(select 2)` @@ -741,7 +741,7 @@ id 1 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using index +1 SIMPLE t2 ref id id 5 const 1 100.00 Using index Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1) @@ -753,7 +753,7 @@ id 2 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index +1 SIMPLE t2 ref id id 5 const 1 100.00 Using where; Using index Warnings: Note 1249 Select 3 was reduced during optimization Note 1249 Select 2 was reduced during optimization @@ -887,7 +887,7 @@ select 10.5 > ANY (SELECT * from t1); 1 explain extended select (select a+1) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1249 Select 2 was reduced during optimization @@ -4556,7 +4556,7 @@ int_nokey int_key 0 0 EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY C ALL NULL NULL NULL NULL 20 100.00 Using where +1 SIMPLE C ALL NULL NULL NULL NULL 20 100.00 Using where DROP TABLE C; # End of test for bug#45061. # @@ -6111,7 +6111,7 @@ FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3 ) AS alias3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM ( SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 @@ -6156,7 +6156,7 @@ FROM t2 AS c_sq1_alias1 WHERE (c_sq1_alias1.col_int_nokey != @var2 OR c_sq1_alias1.pk != @var3)) ) AS alias3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1 SELECT * FROM ( SELECT sq4_alias1.* FROM t1 AS sq4_alias1 diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 5368198f77a..283ba678ef3 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -13,7 +13,7 @@ select (select 2); 2 explain extended select (select 2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select 2 AS `(select 2)` @@ -737,7 +737,7 @@ id 1 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using index +1 SIMPLE t2 ref id id 5 const 1 100.00 Using index Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1) @@ -749,7 +749,7 @@ id 2 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index +1 SIMPLE t2 ref id id 5 const 1 100.00 Using where; Using index Warnings: Note 1249 Select 3 was reduced during optimization Note 1249 Select 2 was reduced during optimization @@ -883,7 +883,7 @@ select 10.5 > ANY (SELECT * from t1); 1 explain extended select (select a+1) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1249 Select 2 was reduced during optimization @@ -4552,7 +4552,7 @@ int_nokey int_key 0 0 EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY C ALL NULL NULL NULL NULL 20 100.00 Using where +1 SIMPLE C ALL NULL NULL NULL NULL 20 100.00 Using where DROP TABLE C; # End of test for bug#45061. # @@ -6107,7 +6107,7 @@ FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3 ) AS alias3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM ( SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 @@ -6152,7 +6152,7 @@ FROM t2 AS c_sq1_alias1 WHERE (c_sq1_alias1.col_int_nokey != @var2 OR c_sq1_alias1.pk != @var3)) ) AS alias3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1 SELECT * FROM ( SELECT sq4_alias1.* FROM t1 AS sq4_alias1 diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index ad0d2ffe6a6..38af8086471 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -16,7 +16,7 @@ select (select 2); 2 explain extended select (select 2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select 2 AS `(select 2)` @@ -740,7 +740,7 @@ id 1 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using index +1 SIMPLE t2 ref id id 5 const 1 100.00 Using index Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1) @@ -752,7 +752,7 @@ id 2 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index +1 SIMPLE t2 ref id id 5 const 1 100.00 Using where; Using index Warnings: Note 1249 Select 3 was reduced during optimization Note 1249 Select 2 was reduced during optimization @@ -886,7 +886,7 @@ select 10.5 > ANY (SELECT * from t1); 1 explain extended select (select a+1) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1249 Select 2 was reduced during optimization @@ -4560,7 +4560,7 @@ int_nokey int_key 0 0 EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY C ALL NULL NULL NULL NULL 20 100.00 Using where +1 SIMPLE C ALL NULL NULL NULL NULL 20 100.00 Using where DROP TABLE C; # End of test for bug#45061. # @@ -6118,7 +6118,7 @@ FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3 ) AS alias3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM ( SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 @@ -6163,7 +6163,7 @@ FROM t2 AS c_sq1_alias1 WHERE (c_sq1_alias1.col_int_nokey != @var2 OR c_sq1_alias1.pk != @var3)) ) AS alias3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1 SELECT * FROM ( SELECT sq4_alias1.* FROM t1 AS sq4_alias1 diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 39cc060e955..281fab3d1ee 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -13,7 +13,7 @@ select (select 2); 2 explain extended select (select 2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select 2 AS `(select 2)` @@ -737,7 +737,7 @@ id 1 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using index +1 SIMPLE t2 ref id id 5 const 1 100.00 Using index Warnings: Note 1249 Select 2 was reduced during optimization Note 1003 select `test`.`t2`.`id` AS `id` from `test`.`t2` where (`test`.`t2`.`id` = 1) @@ -749,7 +749,7 @@ id 2 EXPLAIN EXTENDED SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2 ref id id 5 const 1 100.00 Using where; Using index +1 SIMPLE t2 ref id id 5 const 1 100.00 Using where; Using index Warnings: Note 1249 Select 3 was reduced during optimization Note 1249 Select 2 was reduced during optimization @@ -883,7 +883,7 @@ select 10.5 > ANY (SELECT * from t1); 1 explain extended select (select a+1) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1249 Select 2 was reduced during optimization @@ -4552,7 +4552,7 @@ int_nokey int_key 0 0 EXPLAIN EXTENDED SELECT * FROM C WHERE `int_key` IN (SELECT `int_nokey`); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY C ALL NULL NULL NULL NULL 20 100.00 Using where +1 SIMPLE C ALL NULL NULL NULL NULL 20 100.00 Using where DROP TABLE C; # End of test for bug#45061. # @@ -6107,7 +6107,7 @@ FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3 ) AS alias3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found 2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM ( SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 @@ -6152,7 +6152,7 @@ FROM t2 AS c_sq1_alias1 WHERE (c_sq1_alias1.col_int_nokey != @var2 OR c_sq1_alias1.pk != @var3)) ) AS alias3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1 SELECT * FROM ( SELECT sq4_alias1.* FROM t1 AS sq4_alias1 diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 2d229db9ec5..51e5c5e54d0 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -941,9 +941,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index DROP TABLE t1, t2, t3; DROP VIEW v2, v3; # End of Bug#49198 diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index a0ebbb3305d..73777dd71c3 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -954,9 +954,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using index -1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index -1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 2 Using index +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.t1field 1 Using index DROP TABLE t1, t2, t3; DROP VIEW v2, v3; # End of Bug#49198 diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index 0b19b6b4eaf..59054607acb 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -119,27 +119,27 @@ t2 where id=f.id); This should use one table: explain select id from v1 where id=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE f const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY f const PRIMARY PRIMARY 4 const 1 Using index This should use one table: explain extended select id from v1 where id in (1,2,3,4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index +1 PRIMARY f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index Warnings: Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2 Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3,4)) This should use facts and a1 tables: explain extended select id from v1 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition -1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index +1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition +1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2 Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where ((`f`.`id` = `a1`.`id`) and (`a1`.`attr1` between 12 and 14)) This should use facts, a2 and its subquery: explain extended select id from v1 where attr2 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using where -1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using index +1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition; Using where +1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using index 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.a2.id 2 100.00 Using index Warnings: Note 1276 Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2 @@ -147,27 +147,27 @@ Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` whe This should use one table: explain select id from v2 where id=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE f const PRIMARY PRIMARY 4 const 1 Using index +1 PRIMARY f const PRIMARY PRIMARY 4 const 1 Using index This should use one table: explain extended select id from v2 where id in (1,2,3,4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index +1 PRIMARY f range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index Warnings: Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2 Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` where (`f`.`id` in (1,2,3,4)) This should use facts and a1 tables: explain extended select id from v2 where attr1 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition -1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index +1 PRIMARY a1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using index condition +1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a1.id 1 100.00 Using index Warnings: Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2 Note 1003 select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t1` `a1` where ((`f`.`id` = `a1`.`id`) and (`a1`.`attr1` between 12 and 14)) This should use facts, a2 and its subquery: explain extended select id from v2 where attr2 between 12 and 14; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition -1 SIMPLE f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index +1 PRIMARY a2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using index condition +1 PRIMARY f eq_ref PRIMARY PRIMARY 4 test.a2.id 1 100.00 Using where; Using index 3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.f.id 2 100.00 Using index Warnings: Note 1276 Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2 diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 53c9267f019..3969d9d4a09 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -119,7 +119,7 @@ c 12 explain extended select c from v5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE <derived3> ALL NULL NULL NULL NULL 5 100.00 +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select (`v2`.`c` + 1) AS `c` from `test`.`v2` @@ -4473,8 +4473,8 @@ f1 f1 1 1 EXPLAIN EXTENDED SELECT * FROM v2 AS a1, v2 AS a2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE <derived3> ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort -1 SIMPLE <derived5> ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) 5 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 3 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: diff --git a/mysql-test/suite/sys_vars/r/log_slow_verbosity_basic.result b/mysql-test/suite/sys_vars/r/log_slow_verbosity_basic.result index 24b805d4d9c..a291e276e4b 100644 --- a/mysql-test/suite/sys_vars/r/log_slow_verbosity_basic.result +++ b/mysql-test/suite/sys_vars/r/log_slow_verbosity_basic.result @@ -37,6 +37,22 @@ set session log_slow_verbosity=3; select @@session.log_slow_verbosity; @@session.log_slow_verbosity innodb,query_plan +set session log_slow_verbosity=4; +select @@session.log_slow_verbosity; +@@session.log_slow_verbosity +explain +set session log_slow_verbosity=5; +select @@session.log_slow_verbosity; +@@session.log_slow_verbosity +innodb,explain +set session log_slow_verbosity=6; +select @@session.log_slow_verbosity; +@@session.log_slow_verbosity +query_plan,explain +set session log_slow_verbosity=7; +select @@session.log_slow_verbosity; +@@session.log_slow_verbosity +innodb,query_plan,explain set session log_slow_verbosity='innodb'; select @@session.log_slow_verbosity; @@session.log_slow_verbosity @@ -49,6 +65,14 @@ set session log_slow_verbosity='innodb,query_plan'; select @@session.log_slow_verbosity; @@session.log_slow_verbosity innodb,query_plan +set session log_slow_verbosity='explain'; +select @@session.log_slow_verbosity; +@@session.log_slow_verbosity +explain +set session log_slow_verbosity='innodb,query_plan,explain'; +select @@session.log_slow_verbosity; +@@session.log_slow_verbosity +innodb,query_plan,explain set session log_slow_verbosity=''; select @@session.log_slow_verbosity; @@session.log_slow_verbosity @@ -59,6 +83,6 @@ set session log_slow_verbosity=1e1; ERROR 42000: Incorrect argument type to variable 'log_slow_verbosity' set session log_slow_verbosity="foo"; ERROR 42000: Variable 'log_slow_verbosity' can't be set to the value of 'foo' -set session log_slow_verbosity=4; -ERROR 42000: Variable 'log_slow_verbosity' can't be set to the value of '4' +set session log_slow_verbosity=8; +ERROR 42000: Variable 'log_slow_verbosity' can't be set to the value of '8' SET @@global.log_slow_verbosity = @start_global_value; diff --git a/mysql-test/suite/sys_vars/t/log_slow_verbosity_basic.test b/mysql-test/suite/sys_vars/t/log_slow_verbosity_basic.test index 9302d5f1210..f45fa5da9c5 100644 --- a/mysql-test/suite/sys_vars/t/log_slow_verbosity_basic.test +++ b/mysql-test/suite/sys_vars/t/log_slow_verbosity_basic.test @@ -29,12 +29,26 @@ set session log_slow_verbosity=2; select @@session.log_slow_verbosity; set session log_slow_verbosity=3; select @@session.log_slow_verbosity; +set session log_slow_verbosity=4; +select @@session.log_slow_verbosity; +set session log_slow_verbosity=5; +select @@session.log_slow_verbosity; +set session log_slow_verbosity=6; +select @@session.log_slow_verbosity; +set session log_slow_verbosity=7; +select @@session.log_slow_verbosity; + + set session log_slow_verbosity='innodb'; select @@session.log_slow_verbosity; set session log_slow_verbosity='query_plan'; select @@session.log_slow_verbosity; set session log_slow_verbosity='innodb,query_plan'; select @@session.log_slow_verbosity; +set session log_slow_verbosity='explain'; +select @@session.log_slow_verbosity; +set session log_slow_verbosity='innodb,query_plan,explain'; +select @@session.log_slow_verbosity; set session log_slow_verbosity=''; select @@session.log_slow_verbosity; @@ -48,6 +62,6 @@ set session log_slow_verbosity=1e1; --error ER_WRONG_VALUE_FOR_VAR set session log_slow_verbosity="foo"; --error ER_WRONG_VALUE_FOR_VAR -set session log_slow_verbosity=4; +set session log_slow_verbosity=8; SET @@global.log_slow_verbosity = @start_global_value; diff --git a/mysql-test/t/explain_non_select.test b/mysql-test/t/explain_non_select.test new file mode 100644 index 00000000000..f05373c312e --- /dev/null +++ b/mysql-test/t/explain_non_select.test @@ -0,0 +1,200 @@ +# +# MariaDB tests for EXPLAIN UPDATE/DELETE. +# +--source include/have_partition.inc + +--disable_warnings +drop table if exists t0, t1; +--enable_warnings + +create table t0 (a int) engine=myisam; +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); + +--echo # +--echo # Tests for single-table DELETE +--echo # + +explain select * from t0 where a=3; +explain delete from t0 where a=3; + +--echo # DELETE without WHERE is a special case: +explain delete from t0; + +create table t1 (a int, b int, filler char(100), key(a), key(b)); +insert into t1 +select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler' +from t0 A, t0 B, t0 C; + +--echo # This should use an index, possible_keys=NULL because there is no WHERE +explain delete from t1 order by a limit 2; + +--echo # This should use range, possible_keys={a,b} +explain delete from t1 where a<20 and b < 10; + +--echo # This should use ALL + filesort +explain delete from t1 order by a+1 limit 2; + +--echo # This should use range + using filesort +explain delete from t1 where a<20 order by b limit 2; + +--echo # Try some subqueries: +explain delete from t1 where a < (select max(a) from t0); +explain delete from t1 where a < (select max(a) from t0 where a < t1.b); + +--echo # +--echo # Tests for multi-table DELETE +--echo # +explain delete t1 from t0, t1 where t0.a = t1.a; +drop table t0, t1; + +--echo # ################################################################### +--echo # ## EXPLAIN UPDATE tests +--echo # ################################################################### +create table t0 (a int) engine=myisam; +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); + +explain update t0 set a=3 where a=4; + +create table t1 (a int, b int, filler char(100), key(a), key(b)); +insert into t1 +select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler' +from t0 A, t0 B, t0 C; + +explain update t1 set a=a+1 where 3>4; +explain update t1 set a=a+1 where a=3 and a=4; + +--echo # This should use an index, possible_keys=NULL because there is no WHERE +explain update t1 set a=a+1 order by a limit 2; + +--echo # This should use range, possible_keys={a,b} +explain update t1 set filler='fooo' where a<20 and b < 10; + +--echo # This should use ALL + filesort +explain update t1 set filler='fooo' order by a+1 limit 2; + +--echo # This should use range + using filesort +explain update t1 set filler='fooo' where a<20 order by b limit 2; + +--echo # Try some subqueries: +explain update t1 set filler='fooo' where a < (select max(a) from t0); +explain update t1 set filler='fooo' where a < (select max(a) from t0 where a < t1.b); + +--echo # +--echo # Tests for multi-table UPDATE +--echo # +explain update t0, t1 set t1.a=t1.a+1 where t0.a = t1.a; + + +drop table t0, t1; + +--echo # +--echo # Try DELETE ... RETURNING ... +--echo # +create table t0 (a int); +insert into t0 values (1),(2),(3),(4); +explain delete from t0 where a=1 returning a; +explain delete from t0 returning a; +drop table t0; + +--echo # +--echo # MDEV-5070 - EXPLAIN INSERT ... SELECT crashes on 10.0-base-explain-slowquerylog +--echo # +create table t0 (a int); +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); +create table t1 (a int); + +explain insert into t1 select * from t0; +explain replace into t1 select * from t0; + +drop table t0, t1; + +--echo # +--echo # MDEV-5067: Valgrind warnings (Invalid read) in QPF_table_access::print_explain +--echo # +CREATE TABLE t1 (i INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (7),(0),(9); + +SELECT * FROM t1 INNER JOIN ( SELECT DISTINCT * FROM t1 ) AS sq ON (sq.i = t1.i); + +DROP TABLE t1; + +--echo # +--echo # MDEV-5093, MDEV-5094: EXPLAIN PARTITIONS and EXPLAIN EXTENDED do not +--echo # work for EXPLAIN UPDATE. +--echo # +create table t1 (i int); +explain partitions update t1 set i = 3; + +create table t2 (a int, b int) partition by hash(a) partitions 5; +insert into t2 values (0,0),(1,1),(2,2),(3,3),(4,4); + +explain partitions update t2 set b=3 where a in (3,4); +explain partitions delete from t2 where a in (3,4); + +explain extended update t2 set b=3 where a in (3,4); +explain extended delete from t2 where a in (3,4); + +drop table t1,t2; + +--echo # +--echo # Check the special case where partition pruning removed all partitions +--echo # + +create table t1 (a int, b int) +partition by range (a) ( + partition p0 values less than (10), + partition p1 values less than (20), + partition p2 values less than (30) +); +insert into t1 values (9,9), (19,19), (29,29); + +explain partitions select * from t1 where a in (32,33); + +explain partitions delete from t1 where a in (32,33); + +explain partitions update t1 set b=12345 where a in (32,33); + +drop table t1; + +--echo # +--echo # Tests for EXPLAIN INSERT ... VALUES +--echo # +create table t1 (a int, key(a)); +explain insert into t1 values (1),(2),(3); +insert into t1 values (1),(2),(3); + +create table t2 (a int, b int); +explain insert into t2 values + (10, 1+(select max(a) from t1)), + (11, 1+(select max(a+1) from t1)); + +drop table t1,t2; + +--echo # +--echo # MDEV-5122: "Commands out of sync", "Malformed packet" or client hang up on unique key violation +--echo # +drop table if exists t1; + +--disable_warnings +drop function if exists f1; +--enable_warnings + +create table t1 (a int, unique(a)); + +delimiter |; +create function f1(x int) + returns int +begin + insert into t1 values(x),(x); + return 10; +end| +delimiter ;| + +--error ER_DUP_ENTRY +select f1(100); +select 'OK'; + +drop function f1; +drop table t1; + + diff --git a/mysql-test/t/explain_slowquerylog-master.opt b/mysql-test/t/explain_slowquerylog-master.opt new file mode 100644 index 00000000000..0a3ad969e79 --- /dev/null +++ b/mysql-test/t/explain_slowquerylog-master.opt @@ -0,0 +1 @@ +--slow-query-log --long-query-time=0.00000 --log-slow-verbosity=query_plan,explain diff --git a/mysql-test/t/explain_slowquerylog.test b/mysql-test/t/explain_slowquerylog.test new file mode 100644 index 00000000000..8c8be555640 --- /dev/null +++ b/mysql-test/t/explain_slowquerylog.test @@ -0,0 +1,49 @@ +# +# This is a test for EXPLAINs being written into slow query log. +# For now, we just run the queries and hope not to crash. +# +# +--disable_warnings +drop table if exists t0,t1; +--enable_warnings + +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +explain select * from t0 where a < 3; + +--echo # +--echo # MDEV-5045: Server crashes in QPF_query::print_explain with log_slow_verbosity='query_plan,explain' +--echo # +set autocommit=1; +drop table t0; + +--echo # +--echo # MDEV-5047 virtual THD::~THD(): Assertion `status_var.memory_used == 0' fails on disconnect +--echo # +--connect (con1,localhost,root,,) +--error ER_NO_SUCH_TABLE +ALTER TABLE nonexisting ENABLE KEYS; +SHOW WARNINGS; +--disconnect con1 +--connection default +SELECT 1; + +--echo # +--echo # MDEV-5060 Server crashes on EXPLAIN EXTENDED or EXPLAIN PARTITIONS with explain in slow_log +--echo # +EXPLAIN PARTITIONS SELECT 1 ; + + +--echo # +--echo # MDEV-5106: Server crashes in Explain_union::print_explain on ER_TOO_BIG_SELECT with explain in slow log +--echo # +CREATE TABLE t1 (i INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +SET max_join_size = 10; +--error ER_TOO_BIG_SELECT +( SELECT ta.* FROM t1 ta, t1 tb ) UNION ( SELECT * FROM t1 ); +SELECT 'Server still alive?' as 'Yes'; + +DROP TABLE t1; diff --git a/mysql-test/t/grant_explain_non_select.test b/mysql-test/t/grant_explain_non_select.test new file mode 100644 index 00000000000..d59a8f3d8ce --- /dev/null +++ b/mysql-test/t/grant_explain_non_select.test @@ -0,0 +1,258 @@ +# +# Privilege-specific tests for WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE +# + +# Grant tests not performed with embedded server +-- source include/not_embedded.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +CREATE DATABASE privtest_db; + +CREATE TABLE privtest_db.t1 (a INT); +CREATE TABLE privtest_db.t2 (a INT); +INSERT INTO privtest_db.t2 VALUES (1), (2), (3); + +GRANT USAGE ON *.* TO 'privtest'@'localhost'; +GRANT SELECT ON privtest_db.t2 TO 'privtest'@'localhost'; + +connect(con1,localhost,privtest,,); +connection con1; + +USE privtest_db; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN INSERT INTO t1 VALUES (10); +--error ER_TABLEACCESS_DENIED_ERROR + INSERT INTO t1 VALUES (10); + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN INSERT INTO t1 SELECT * FROM t2; +--error ER_TABLEACCESS_DENIED_ERROR + INSERT INTO t1 SELECT * FROM t2; + +connection default; +GRANT INSERT ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; + +EXPLAIN INSERT INTO t1 VALUES (10); + INSERT INTO t1 VALUES (10); + +EXPLAIN INSERT INTO t1 SELECT * FROM t2; + INSERT INTO t1 SELECT * FROM t2; + + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +connection con1; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN REPLACE INTO t1 VALUES (10); +--error ER_TABLEACCESS_DENIED_ERROR + REPLACE INTO t1 VALUES (10); + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +--error ER_TABLEACCESS_DENIED_ERROR + REPLACE INTO t1 SELECT * FROM t2; + +connection default; +GRANT INSERT ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN REPLACE INTO t1 VALUES (10); +--error ER_TABLEACCESS_DENIED_ERROR + REPLACE INTO t1 VALUES (10); + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +--error ER_TABLEACCESS_DENIED_ERROR + REPLACE INTO t1 SELECT * FROM t2; + +connection default; +REVOKE INSERT ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT DELETE ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN REPLACE INTO t1 VALUES (10); +--error ER_TABLEACCESS_DENIED_ERROR + REPLACE INTO t1 VALUES (10); + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; +--error ER_TABLEACCESS_DENIED_ERROR + REPLACE INTO t1 SELECT * FROM t2; + +connection default; +GRANT INSERT, DELETE ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; + +EXPLAIN REPLACE INTO t1 VALUES (10); + REPLACE INTO t1 VALUES (10); + +EXPLAIN REPLACE INTO t1 SELECT * FROM t2; + REPLACE INTO t1 SELECT * FROM t2; + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +connection con1; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN UPDATE t1 SET a = a + 1; +--error ER_TABLEACCESS_DENIED_ERROR + UPDATE t1 SET a = a + 1; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +--error ER_TABLEACCESS_DENIED_ERROR + UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; + +connection default; +GRANT UPDATE ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; + +--error ER_COLUMNACCESS_DENIED_ERROR +EXPLAIN UPDATE t1 SET a = a + 1; +--error ER_COLUMNACCESS_DENIED_ERROR + UPDATE t1 SET a = a + 1; + +--error ER_COLUMNACCESS_DENIED_ERROR +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +--error ER_COLUMNACCESS_DENIED_ERROR + UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; + +connection default; +REVOKE UPDATE ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT SELECT ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN UPDATE t1 SET a = a + 1; +--error ER_TABLEACCESS_DENIED_ERROR + UPDATE t1 SET a = a + 1; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; +--error ER_TABLEACCESS_DENIED_ERROR + UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; + +connection default; +GRANT UPDATE, SELECT ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; + +EXPLAIN UPDATE t1 SET a = a + 1; + UPDATE t1 SET a = a + 1; + +EXPLAIN UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; + UPDATE t1, t2 SET t1.a = t1.a + 1 WHERE t1.a = t2.a; + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +connection con1; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN DELETE FROM t1 WHERE a = 10; +--error ER_TABLEACCESS_DENIED_ERROR + DELETE FROM t1 WHERE a = 10; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +--error ER_TABLEACCESS_DENIED_ERROR + DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; + +connection default; +GRANT DELETE ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; + +--error ER_COLUMNACCESS_DENIED_ERROR +EXPLAIN DELETE FROM t1 WHERE a = 10; +--error ER_COLUMNACCESS_DENIED_ERROR + DELETE FROM t1 WHERE a = 10; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +--error ER_TABLEACCESS_DENIED_ERROR + DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT SELECT ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN DELETE FROM t1 WHERE a = 10; +--error ER_TABLEACCESS_DENIED_ERROR + DELETE FROM t1 WHERE a = 10; + +--error ER_TABLEACCESS_DENIED_ERROR +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; +--error ER_TABLEACCESS_DENIED_ERROR + DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +GRANT DELETE, SELECT ON privtest_db.t1 TO 'privtest'@'localhost'; +connection con1; + +EXPLAIN DELETE FROM t1 WHERE a = 10; + DELETE FROM t1 WHERE a = 10; + +EXPLAIN DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; + DELETE FROM t1 USING t1, t2 WHERE t1.a = t2.a; + +# Views + +connection default; +REVOKE ALL PRIVILEGES ON privtest_db.t1 FROM 'privtest'@'localhost'; +CREATE VIEW privtest_db.v1 (a) AS SELECT a FROM privtest_db.t1; +GRANT SELECT, INSERT, UPDATE, DELETE ON privtest_db.v1 TO 'privtest'@'localhost'; +connection con1; + +--error ER_VIEW_NO_EXPLAIN +EXPLAIN SELECT * FROM v1; + SELECT * FROM v1; + +--error ER_VIEW_NO_EXPLAIN +EXPLAIN INSERT INTO v1 VALUES (10); + INSERT INTO v1 VALUES (10); + +--error ER_VIEW_NO_EXPLAIN +EXPLAIN INSERT INTO v1 SELECT * FROM t2; + INSERT INTO v1 SELECT * FROM t2; + +--error ER_VIEW_NO_EXPLAIN +EXPLAIN REPLACE INTO v1 VALUES (10); + REPLACE INTO v1 VALUES (10); + +--error ER_VIEW_NO_EXPLAIN +EXPLAIN REPLACE INTO v1 SELECT * FROM t2; + REPLACE INTO v1 SELECT * FROM t2; + +--error ER_VIEW_NO_EXPLAIN +EXPLAIN UPDATE v1 SET a = a + 1; + UPDATE v1 SET a = a + 1; + +--error ER_VIEW_NO_EXPLAIN +EXPLAIN UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; + UPDATE v1, t2 SET v1.a = v1.a + 1 WHERE v1.a = t2.a; + +--error ER_VIEW_NO_EXPLAIN +EXPLAIN DELETE FROM v1 WHERE a = 10; + DELETE FROM v1 WHERE a = 10; + +--error ER_VIEW_NO_EXPLAIN +EXPLAIN DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; + DELETE FROM v1 USING v1, t2 WHERE v1.a = t2.a; + +connection default; +disconnect con1; + +DROP USER 'privtest'@localhost; +USE test; +DROP DATABASE privtest_db; + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/myisam_explain_non_select_all.test b/mysql-test/t/myisam_explain_non_select_all.test new file mode 100644 index 00000000000..ccd4ebd6297 --- /dev/null +++ b/mysql-test/t/myisam_explain_non_select_all.test @@ -0,0 +1,21 @@ +# +# Run explain_non_select.inc on MyISAM with all of the so-called 6.0 features. +# + +#--source include/have_semijoin.inc +#--source include/have_materialization.inc +#--source include/have_firstmatch.inc +#--source include/have_loosescan.inc +#--source include/have_index_condition_pushdown.inc +#--source include/have_mrr.inc + +#set optimizer_switch='semijoin=on,materialization=on,firstmatch=on,loosescan=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off'; + +set @save_storage_engine= @@session.default_storage_engine; +set session default_storage_engine = MyISAM; +--let $json = 0 +--let $validation = 0 +--source include/explain_non_select.inc +set default_storage_engine= @save_storage_engine; + +set optimizer_switch=default; diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test index 41091d7f5b9..2ac4e214dbc 100644 --- a/mysql-test/t/show_explain.test +++ b/mysql-test/t/show_explain.test @@ -202,7 +202,6 @@ 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_TARGET_NOT_EXPLAINABLE evalp show explain for $thr2; connection con1; reap; @@ -225,7 +224,9 @@ connection default; --source include/wait_condition.inc --error ER_TARGET_NOT_EXPLAINABLE evalp show explain for $thr2; +evalp kill query $thr2; connection con1; +--error ER_QUERY_INTERRUPTED reap; set debug_dbug=@old_debug; @@ -238,10 +239,10 @@ 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_TARGET_NOT_EXPLAINABLE +#--error ER_TARGET_NOT_EXPLAINABLE evalp show explain for $thr2; --source include/wait_condition.inc ---error ER_TARGET_NOT_EXPLAINABLE +#--error ER_TARGET_NOT_EXPLAINABLE evalp show explain for $thr2; connection con1; reap; @@ -249,7 +250,7 @@ drop table t2; set debug_dbug=@old_debug; --echo # ---echo # Attempt SHOW EXPLAIN for a DELETE +--echo # Attempt SHOW EXPLAIN for a DELETE (UPD: now works) --echo # create table t2 as select a as a, a as dummy from t0 limit 2; set @show_explain_probe_select_id=2; @@ -257,10 +258,10 @@ 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_TARGET_NOT_EXPLAINABLE +#--error ER_TARGET_NOT_EXPLAINABLE evalp show explain for $thr2; --source include/wait_condition.inc ---error ER_TARGET_NOT_EXPLAINABLE +#--error ER_TARGET_NOT_EXPLAINABLE evalp show explain for $thr2; connection con1; reap; @@ -349,7 +350,7 @@ 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_TARGET_NOT_EXPLAINABLE +#--error ER_TARGET_NOT_EXPLAINABLE evalp show explain for $thr2; connection con1; reap; @@ -400,7 +401,9 @@ connection default; --source include/wait_condition.inc --error ER_TARGET_NOT_EXPLAINABLE evalp show explain for $thr2; +evalp kill query $thr2; connection con1; +--error ER_QUERY_INTERRUPTED reap; set debug_dbug=@old_debug; DROP VIEW v1; @@ -428,7 +431,7 @@ 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_TARGET_NOT_EXPLAINABLE +#--error ER_TARGET_NOT_EXPLAINABLE evalp show explain for $thr2; connection con1; reap; @@ -444,7 +447,7 @@ 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_TARGET_NOT_EXPLAINABLE +# --error ER_TARGET_NOT_EXPLAINABLE evalp show explain for $thr2; connection con1; reap; @@ -540,7 +543,7 @@ 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_TARGET_NOT_EXPLAINABLE +# --error ER_TARGET_NOT_EXPLAINABLE evalp show explain for $thr2; connection con1; reap; @@ -1129,9 +1132,44 @@ set names default; --error ER_SET_CONSTANTS_ONLY show explain for foo; +--echo # +--echo # MDEV-411: SHOW EXPLAIN: For dependent subquery EXPLAIN produces type=index, key, 'Using where; Using index', +--echo # while SHOW EXPLAIN says type=ALL, no key, 'Range checked for each record' +--echo # +CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (7),(0); + +CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t2 VALUES (0),(8); + +explain +SELECT SUM(b) FROM ( SELECT * FROM t1 ) AS alias1, t2 +WHERE b <= ANY ( + SELECT a FROM t1 + WHERE a = b + SLEEP(0.2) OR a >= ( SELECT SUM(b) FROM t2 )); + + +set @show_explain_probe_select_id=1; +set debug_dbug='+d,show_explain_probe_join_exec_start'; + +send +SELECT SUM(b) FROM ( SELECT * FROM t1 ) AS alias1, t2 +WHERE b <= ANY ( + SELECT a FROM t1 + WHERE a = b + SLEEP(0.2) OR a >= ( SELECT SUM(b) FROM t2 )); + +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; + +connection con1; +reap; + +set debug_dbug=@old_debug; +DROP TABLE t1,t2; + --echo # End drop table t0; - connection default; disconnect con1; set debug_sync='RESET'; diff --git a/mysql-test/t/show_explain_non_select.test b/mysql-test/t/show_explain_non_select.test new file mode 100644 index 00000000000..21b16739141 --- /dev/null +++ b/mysql-test/t/show_explain_non_select.test @@ -0,0 +1,78 @@ +# +# SHOW EXPLAIN tests for non-select subqueries +# +--source include/have_debug.inc +--source include/have_innodb.inc +--source include/not_embedded.inc + +--disable_warnings +drop table if exists t0, t1; +--enable_warnings + +SET @old_debug= @@session.debug; +set debug_sync='RESET'; + +# +# Setup two threads and their ids +# +let $thr1=`select connection_id()`; +connect (con2, localhost, root,,); +connection con2; +let $thr2=`select connection_id()`; +connection default; + +# +# Create tables +# +create table t0 (a int) engine=myisam; +insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); + +create table t1 (a int, b int, filler char(100), key(a), key(b)); +insert into t1 +select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler' +from t0 A, t0 B, t0 C; + +let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2; + +--echo # +--echo # Test SHOW EXPLAIN for single-table DELETE +--echo # +connection con2; +set debug_dbug='+d,show_explain_probe_delete_exec_start'; +send delete from t1 where a<10 and b+1>1000; + +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con2; +reap; + +--echo # +--echo # Test SHOW EXPLAIN for multi-table DELETE +--echo # +set @show_explain_probe_select_id=1; +set debug_dbug='+d,show_explain_probe_do_select'; +send delete t1 from t1, t0 where t0.a=t1.a and t1.b +1 > 1000; +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con2; +reap; + +--echo # +--echo # Test SHOW EXPLAIN for single-table UPDATE +--echo # +connection con2; +set debug_dbug='+d,show_explain_probe_update_exec_start'; +send update t1 set filler='filler-data-2' where a<10 and b+1>1000; + +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con2; +reap; + +drop table t0,t1; + +set debug_dbug=@old_debug; +set debug_sync='RESET'; |