diff options
Diffstat (limited to 'mysql-test/main/cte_nonrecursive.test')
-rw-r--r-- | mysql-test/main/cte_nonrecursive.test | 229 |
1 files changed, 229 insertions, 0 deletions
diff --git a/mysql-test/main/cte_nonrecursive.test b/mysql-test/main/cte_nonrecursive.test index c420a5e0..bebf02ea 100644 --- a/mysql-test/main/cte_nonrecursive.test +++ b/mysql-test/main/cte_nonrecursive.test @@ -1796,4 +1796,233 @@ with data as (select 1 as id) select id into @myid from data; set sql_mode= @save_sql_mode; +--echo # +--echo # MDEV-31995 Bogus error executing PS for query using CTE with renaming of columns +--echo # + +create table t1 (a int, b int); +insert into t1 values (1,1),(1,2),(1,3),(2,1),(2,2); +create table t2 (a int, b int); +insert into t2 values (3,1),(3,2),(3,3),(4,1),(4,2); + +let $q= +with cte (c1,c2) as + (select a as col1, sum(b) as col2 from t1 group by col1) +select * from cte; + +eval $q; + +eval prepare st from "$q"; +execute st; +execute st; +drop prepare st; + +eval create procedure sp() $q; +call sp(); +call sp(); +drop procedure sp; + +let $q= +with cte (c1,c2) as + (select a as col1, sum(b) as col2 from t1 order by col1) +select * from cte; + +eval $q; + +eval prepare st from "$q"; +execute st; +execute st; +drop prepare st; + +eval create procedure sp() $q; +call sp(); +call sp(); +drop procedure sp; + +let $q= +with cte (c1,c2) as + (select a as col1, sum(b) as col2 from t1 where a > 1 group by col1 + union select a as col3, sum(b) as col4 from t2 where b > 2 group by col3), +cte2 (c3, c4) as + (select a as col5, sum(b) as col6 from t1 where a <= 1 group by col5 + union select a as col7, sum(b) as col8 from t2 where b <= 2 group by col7) +select * from cte where c1=1 union select * from cte2 where c3=3; + +eval $q; + +eval prepare st from "$q"; +execute st; +execute st; +drop prepare st; + +eval create procedure sp() $q; +call sp(); +call sp(); +drop procedure sp; + +let $q= +with cte (c1,c2) as (select * from t1) +select cte.c1+1 as col1 , cte.c2 as col2 from cte where cte.c1 > 1 +union +select cte.c1 as col3, cte.c2+1 as col4 from cte where cte.c1 < 0; + +eval $q; + +eval prepare st from "$q"; +execute st; +execute st; +--echo save this to the end to test errors >drop prepare st; + +eval create procedure sp() $q; +call sp(); +call sp(); +drop procedure sp; + +insert into t1 select * from t2; + +let $q= +with cte (c1, c2) + as (select a, sum(b) from t1 where b > 1 group by a having sum(b) < 5) +select * from cte where c1 < 4 and c2 > 1; + +eval $q; + +--echo # Check pushdown conditions in JSON output +--source include/analyze-format.inc +eval explain format=json $q; + +alter table t1 add column c int; + +--error ER_WITH_COL_WRONG_LIST +execute st; + +drop prepare st; +drop table t1,t2; + +--echo Test out recursive CTEs + +create table distances (src char(1), dest char(1), distance int); +create table city_population (city char(1), population int); +INSERT INTO `distances` VALUES ('A','A',0),('B','A',593),('C','A',800), +('D','A',221),('E','A',707),('F','A',869),('G','A',225),('H','A',519), +('A','B',919),('B','B',0),('C','B',440),('D','B',79),('E','B',79), +('F','B',154),('G','B',537),('H','B',220),('A','C',491),('B','C',794), +('C','C',0),('D','C',100),('E','C',350),('F','C',748),('G','C',712), +('H','C',315),('A','D',440),('B','D',256),('C','D',958),('D','D',0), +('E','D',255),('F','D',161),('G','D',63),('H','D',831),('A','E',968), +('B','E',345),('C','E',823),('D','E',81),('E','E',0),('F','E',436), +('G','E',373),('H','E',558),('A','F',670),('B','F',677),('C','F',375), +('D','F',843),('E','F',90),('F','F',0),('G','F',328),('H','F',881), +('A','G',422),('B','G',467),('C','G',67),('D','G',936),('E','G',480), +('F','G',592),('G','G',0),('H','G',819),('A','H',537),('B','H',229), +('C','H',534),('D','H',984),('E','H',319),('F','H',643),('G','H',257), +('H','H',0); +insert into city_population values ('A', 5000), ('B', 6000), ('C', 100000), +('D', 80000), ('E', 7000), ('F', 1000), ('G', 100), ('H', -80000); + +--echo #find the biggest city within 300 kellikams of 'E' +let $q= +with recursive travel (src, path, dest, distance, population) as ( + select city, cast('' as varchar(10)), city, + 0, population + from city_population where city='E' + union all + select src.src, concat(src.path, dst.dest), dst.dest, + src.distance + dst.distance, dstc.population + from travel src + join distances dst on src.dest != dst.dest + join city_population dstc on dst.dest = dstc.city + where dst.src = src.dest and src.distance + dst.distance < 300 + and length(path) < 10 + ) +select * from travel where dest != 'E' order by population desc, distance +limit 1; + +eval $q; + +eval prepare st from "$q"; +execute st; +execute st; +drop prepare st; + +eval create procedure sp() $q; +call sp(); +call sp(); +drop procedure sp; + +drop table distances, city_population; + +--echo # +--echo # MDEV-28615: Multi-table UPDATE over derived table containing +--echo # row that uses subquery with hanging CTE +--echo # + +CREATE TABLE t1 (a int) ENGINE=MYISAM; +INSERT INTO t1 VALUES (3), (7), (1); + +--error ER_OPERAND_COLUMNS +UPDATE + (SELECT (5, (WITH cte AS (SELECT 1) SELECT a FROM t1))) dt + JOIN t1 t + ON t.a=dt.a +SET t.a = 1; + +UPDATE + (SELECT a FROM t1 + WHERE (5, (WITH cte AS (SELECT 1) SELECT a FROM t1 WHERE a > 4)) <= + (5,a)) dt + JOIN t1 t + ON t.a=dt.a +SET t.a = 1; + +SELECT * FROM t1; + +DROP TABLE t1; + +--echo # +--echo # MDEV-31657: CTE with the same name as base table used twice +--echo # in another CTE +--echo # + +create table t (a int); +insert into t values (3), (7), (1); + +let $q1= +with +t as (select * from t), +cte as (select t1.a as t1a, t2.a as t2a from t as t1, t as t2 where t1.a=t2.a) +select * from cte; + +eval $q1; + +create table s (a int); +insert into s values (1), (4), (7); + +let $q2= +with +t as (select * from t), +s as (select a-1 as a from s), +cte as (select t.a as ta, s.a as sa from t, s where t.a=s.a + union + select t.a+1, s.a+1 from t, s where t.a=s.a+1) +select * from cte; + +eval $q2; + +let $q3= +with +t as (select * from t), +cte as (select t.a as ta, s.a as sa from t, s where t.a=s.a + union + select t.a+1, s.a+1 from t, s where t.a=s.a), +s as (select a+10 as a from s) +select * from cte; + +eval $q3; + +drop table t,s; + +--ERROR ER_NO_SUCH_TABLE +eval $q1; + --echo # End of 10.4 tests |