diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2018-04-19 15:23:21 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2018-04-19 15:23:21 +0300 |
commit | d71a8855eef34e5792b4939ee5e88e6625080d91 (patch) | |
tree | ed4d5b9916395132fa56675eb58a9229d0672bbe /mysql-test/main/cte_recursive.test | |
parent | 419385dbf10453b17a370fd9e5bd934d09e0b440 (diff) | |
parent | 66c14d3a8d31e877ede75d23f96dc61a4aa12971 (diff) | |
download | mariadb-git-d71a8855eef34e5792b4939ee5e88e6625080d91.tar.gz |
Merge 10.2 to 10.3
Temporarily disable main.cte_recursive due to hang in
an added test related to MDEV-15575.
Diffstat (limited to 'mysql-test/main/cte_recursive.test')
-rw-r--r-- | mysql-test/main/cte_recursive.test | 113 |
1 files changed, 113 insertions, 0 deletions
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 7ed55a1daaa..1d4e328081b 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -2098,6 +2098,119 @@ WITH RECURSIVE cte AS SELECT @c:=@c+1 FROM cte WHERE @c<3) SELECT * FROM cte; +--echo # +--echo # MDEV-15575: using recursive cte with big_tables enabled +--echo # + +set big_tables=1; + +with recursive qn as +(select 123 as a union all select 1+a from qn where a<130) +select * from qn; + +set big_tables=default; + +--echo # +--echo # MDEV-15571: using recursive cte with big_tables enabled +--echo # + +set big_tables=1; + +--error ER_DATA_OUT_OF_RANGE +with recursive qn as +( + select 1 as a from dual + union all + select a*2000 from qn where a<10000000000000000000 +) +select * from qn; + +set big_tables=default; + +--echo # +--echo # MDEV-15556: using recursive cte with big_tables enabled +--echo # when recursive tables are accessed by key +--echo # + +SET big_tables=1; + +CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int); +INSERT INTO t1 VALUES + (1, "A", 2, 3), (2, "LA", 4, 5), (4, "LLA", 6, 7), + (6, "LLLA", NULL, NULL), (7, "RLLA", NULL, NULL), (5, "RLA", 8, 9), + (8, "LRLA", NULL, NULL), (9, "RRLA", NULL, NULL), (3, "RA", 10, 11), + (10, "LRA", 12, 13), (11, "RRA", 14, 15), (15, "RRRA", NULL, NULL), + (16, "B", 17, 18), (17, "LB", NULL, NULL), (18, "RB", NULL, NULL); + +CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand(); + +let $q= +WITH RECURSIVE tree_of_a AS + (SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" + UNION ALL + SELECT t2.*, concat(tree_of_a.path,",",t2.id) + FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.leftpar + UNION ALL + SELECT t2.*, concat(tree_of_a.path,",",t2.id) + FROM t2 JOIN tree_of_a ON t2.id=tree_of_a.rightpar) +SELECT * FROM tree_of_a +ORDER BY path; + +eval $q; +eval EXPLAIN $q; + +DROP TABLE t1,t2; + +SET big_tables=0; + +--echo # +--echo # MDEV-15840: recursive tables are accessed by key +--echo # (the same problem as for MDEV-15556) +--echo # + +--source include/have_sequence.inc + +CREATE TABLE t1 (p1 text,k2 int, p2 text, k1 int); +INSERT INTO t1 select seq, seq, seq, seq from seq_1_to_1000; + +DELIMITER |; +CREATE PROCEDURE getNums() +BEGIN +WITH RECURSIVE cte as +( + SELECT * FROM t1 + UNION + SELECT c.* FROM t1 c JOIN cte p ON c.p1 = p.p2 AND c.k2 = p.k1 +) +SELECT * FROM cte LIMIT 10; +END | + +DELIMITER ;| +call getNums(); + +DROP PROCEDURE getNums; +DROP TABLE t1; + +--echo # +--echo # MDEV-15894: aggregate/winfow functions in non-recorsive part +--echo # + +create table t1(b int); +insert into t1 values(10),(20),(10); + +with recursive qn as + (select max(b) as a from t1 union + select a from qn) +select * from qn; + +with recursive qn as + (select rank() over (order by b) as a from t1 union + select a from qn) +select * from qn; + +drop table t1; + +--echo # Start of 10.3 tests --echo # --echo # MDEV-14217 [db crash] Recursive CTE when SELECT includes new field |