diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2018-12-04 13:18:14 +0200 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2018-12-04 13:18:14 +0200 |
commit | b6f203984bc519a31ac695cbcb6de7f1f638d321 (patch) | |
tree | 5938cd9e0de716d6c5cb67d005a669685e0ce251 /mysql-test/main | |
parent | 95f3c142a4f2fdb088e534a4349bb377d1af3098 (diff) | |
parent | 157d3c3bc109bf13c433d9d150ea0c47291ade0d (diff) | |
download | mariadb-git-b6f203984bc519a31ac695cbcb6de7f1f638d321.tar.gz |
Merge 10.2 into 10.3
Diffstat (limited to 'mysql-test/main')
-rw-r--r-- | mysql-test/main/cte_recursive.result | 48 | ||||
-rw-r--r-- | mysql-test/main/cte_recursive.test | 39 |
2 files changed, 87 insertions, 0 deletions
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index 9e934b19efc..0417cf0a7f0 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -3606,6 +3606,54 @@ Mandelbrot Set .............................................................................. ........................................................................... ........................................................................ +# +# MDEV-17871: EXPLAIN for query with not used recursive cte +# +create table t1 (a int); +insert into t1 values (2), (1), (4), (3); +explain extended +with recursive cte as +(select * from t1 where a=1 union select a+1 from cte where a<3) +select * from cte as t; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 4 100.00 Using where +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 100.00 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 with recursive cte as (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union /* select#3 */ select `cte`.`a` + 1 AS `a+1` from `cte` where `cte`.`a` < 3)/* select#1 */ select `t`.`a` AS `a` from `cte` `t` +with recursive cte as +(select * from t1 where a=1 union select a+1 from cte where a<3) +select * from cte as t; +a +1 +2 +3 +explain extended +with recursive cte as +(select * from t1 where a=1 union select a+1 from cte where a<3) +select * from t1 as t; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 with recursive cte as (/* select#2 */ select `*` AS `*` from `test`.`t1` where `a` = 1 union /* select#3 */ select `a` + 1 AS `a+1` from `cte` where `a` < 3)/* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t1` `t` +with recursive cte as +(select * from t1 where a=1 union select a+1 from cte where a<3) +select * from t1 as t; +a +2 +1 +4 +3 +create table t2 ( i1 int, i2 int); +insert into t2 values (1,1),(2,2); +explain +with recursive cte as +( select * from t1 union select s1.* from t1 as s1, cte where s1.i1 = cte.i2 ) +select * from t1 as t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t ALL NULL NULL NULL NULL 4 +drop table t1,t2; # End of 10.2 tests # # MDEV-14217 [db crash] Recursive CTE when SELECT includes new field diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 79809044c00..0ed9c2d56e3 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -2496,6 +2496,45 @@ SELECT GROUP_CONCAT( GROUP BY Iy ORDER BY Iy; +--echo # +--echo # MDEV-17871: EXPLAIN for query with not used recursive cte +--echo # + +create table t1 (a int); +insert into t1 values (2), (1), (4), (3); + +let $rec_cte = +with recursive cte as + (select * from t1 where a=1 union select a+1 from cte where a<3); + +eval +explain extended +$rec_cte +select * from cte as t; + +eval +$rec_cte +select * from cte as t; + +eval +explain extended +$rec_cte +select * from t1 as t; + +eval +$rec_cte +select * from t1 as t; + +create table t2 ( i1 int, i2 int); +insert into t2 values (1,1),(2,2); + +explain +with recursive cte as + ( select * from t1 union select s1.* from t1 as s1, cte where s1.i1 = cte.i2 ) +select * from t1 as t; + +drop table t1,t2; + --echo # End of 10.2 tests --echo # |