diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2017-11-10 16:12:45 +0200 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2017-11-10 16:12:45 +0200 |
commit | a48aa0cd569eda88bef98ed4abe41b0b570fcd51 (patch) | |
tree | 6008b19aabfe0554432c87614d1022594f08fc5f /mysql-test/r/cte_nonrecursive.result | |
parent | 8409f721ffe2d91b11d3fc03c6872ff57051bbf8 (diff) | |
parent | 386e5d476e9bf8f216c760c9076ae0ecdc99054d (diff) | |
download | mariadb-git-a48aa0cd569eda88bef98ed4abe41b0b570fcd51.tar.gz |
Merge bb-10.2-ext into 10.3
Diffstat (limited to 'mysql-test/r/cte_nonrecursive.result')
-rw-r--r-- | mysql-test/r/cte_nonrecursive.result | 68 |
1 files changed, 68 insertions, 0 deletions
diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 3ad6fb8fabe..d0e42cf4042 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -1079,3 +1079,71 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) NULL UNION RESULT <union1,4> ALL NULL NULL NULL NULL NULL DROP TABLE t1,t2; +# +# MDEV-13780: tower of embedding CTEs with multiple usage of them +# +create table t1 (a int); +insert into t1 values (3), (2), (4), (7), (1), (2), (5); +with cte_e as +( +with cte_o as +( +with cte_i as (select * from t1 where a < 7) +select * from cte_i where a > 1 +) +select * from cte_o as cto_o1 where a < 3 +union +select * from cte_o as cto_o2 where a > 4 +) +select * from cte_e as cte_e1 where a > 1 +union +select * from cte_e as cte_e2; +a +2 +5 +explain extended with cte_e as +( +with cte_o as +( +with cte_i as (select * from t1 where a < 7) +select * from cte_i where a > 1 +) +select * from cte_o as cto_o1 where a < 3 +union +select * from cte_o as cto_o2 where a > 4 +) +select * from cte_e as cte_e1 where a > 1 +union +select * from cte_e as cte_e2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 14 100.00 Using where +2 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 Using where +5 UNION t1 ALL NULL NULL NULL NULL 7 100.00 Using where +NULL UNION RESULT <union2,5> ALL NULL NULL NULL NULL NULL NULL +6 UNION <derived9> ALL NULL NULL NULL NULL 14 100.00 +9 DERIVED t1 ALL NULL NULL NULL NULL 7 100.00 Using where +12 UNION t1 ALL NULL NULL NULL NULL 7 100.00 Using where +NULL UNION RESULT <union9,12> ALL NULL NULL NULL NULL NULL NULL +NULL UNION RESULT <union1,6> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 with cte_e as (with cte_o as (with cte_i as (/* select#4 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 7)/* select#3 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1)/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 3 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1 union /* select#5 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 and `test`.`t1`.`a` > 1 and `test`.`t1`.`a` < 7 and `test`.`t1`.`a` > 1)/* select#1 */ select `cte_e1`.`a` AS `a` from `cte_e` `cte_e1` where `cte_e1`.`a` > 1 union /* select#6 */ select `cte_e2`.`a` AS `a` from `cte_e` `cte_e2` +drop table t1; +# +# MDEV-13753: embedded CTE in a VIEW created in prepared statement +# +SET @sql_query = " + CREATE OR REPLACE VIEW cte_test AS + WITH cte1 AS ( SELECT 1 as a from dual ) + , cte2 AS ( SELECT * FROM cte1 ) + SELECT * FROM cte2; +"; +PREPARE stmt FROM @sql_query; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +SHOW CREATE VIEW cte_test; +View Create View character_set_client collation_connection +cte_test CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `cte_test` AS with cte1 as (select 1 AS `a`), cte2 as (select `cte1`.`a` AS `a` from `cte1`)select `cte2`.`a` AS `a` from `cte2` latin1 latin1_swedish_ci +SELECT * FROM cte_test; +a +1 +DROP VIEW cte_test; |