[Commits] e305054: MDEV-10058: Suspicious EXPLAIN output for a derived table + WITH + joined tabl

Sergei Petrunia psergey at askmonty.org
Wed Sep 7 12:22:41 EEST 2016


revision-id: e305054f3119a403cfc4a8a69a5f3d947ee23532
parent(s): 4c39f75c73dfd9fa7e593ecfb5fa6e4272848d48
committer: Sergei Petrunia
branch nick: 10.2
timestamp: 2016-09-07 12:22:41 +0300
message:

MDEV-10058: Suspicious EXPLAIN output for a derived table + WITH + joined tabl

Add a testcase

---
 mysql-test/r/cte_nonrecursive.result |   16 ++++++++++++++++
 mysql-test/t/cte_nonrecursive.test   |   14 ++++++++++++++
 2 files changed, 30 insertions(+)

diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result
index 9ecc098..690355c 100644
--- a/mysql-test/r/cte_nonrecursive.result
+++ b/mysql-test/r/cte_nonrecursive.result
@@ -864,3 +864,19 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 DROP TABLE t1;
+#
+# MDEV-10058: Suspicious EXPLAIN output for a derived table + WITH + joined table	
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+INSERT INTO t2 VALUES (1),(2),(3);
+INSERT INTO t3 VALUES (1),(2),(3);
+EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't2 NATURAL JOIN t3))' at line 1
+explain SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT * FROM t2 NATURAL JOIN t3) AS d1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+DROP TABLE t1,t2,t3;
diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test
index caa8fee..435364b 100644
--- a/mysql-test/t/cte_nonrecursive.test
+++ b/mysql-test/t/cte_nonrecursive.test
@@ -544,3 +544,17 @@ INSERT INTO t1 VALUES (1),(2),(3);
 SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT 1) AS t1;
 EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT 1) AS t1;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-10058: Suspicious EXPLAIN output for a derived table + WITH + joined table	
+--echo #
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+INSERT INTO t2 VALUES (1),(2),(3);
+INSERT INTO t3 VALUES (1),(2),(3);
+--error ER_PARSE_ERROR
+EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3));
+explain SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT * FROM t2 NATURAL JOIN t3) AS d1;
+DROP TABLE t1,t2,t3;


More information about the commits mailing list