revision-id: e34d3184fd02967616bb83904aa3c21977ce6205 (mariadb-10.2.14-51-ge34d318) parent(s): 612850782d6d8bbe44d2b153a045b9a8afc624ef author: Igor Babaev committer: Igor Babaev timestamp: 2018-04-16 10:31:30 -0700 message: MDEV-15556 MariaDB crash with big_tables=1 and CTE This bug manifested itself when the optimizer chose an execution plan with an access of the recursive CTE in a recursive query by key and ARIA/MYISAM temporary tables were used to store recursive tables. The problem appeared due to passing an incorrect parameter to the call of instantiate_tmp_table() in the function With_element::instantiate_tmp_tables(). --- mysql-test/r/cte_recursive.result | 88 ++++++++++++++++++++++++++++++++++++++- mysql-test/t/cte_recursive.test | 66 ++++++++++++++++++++++++++++- sql/sql_cte.cc | 2 +- 3 files changed, 153 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 01443bb..e1a52be 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3081,7 +3081,7 @@ a 130 set big_tables=default; # -# MDEV-1571: Setting user variable in recursive CTE +# MDEV-15571: using recursive cte with big_tables enabled # set big_tables=1; with recursive qn as @@ -3093,3 +3093,89 @@ select a*2000 from qn where a<10000000000000000000 select * from qn; ERROR 22003: BIGINT value is out of range in '`qn`.`a` * 2000' set big_tables=default; +# +# MDEV-15556: using recursive cte with big_tables enabled +# when recursive tables are accessed by key +# +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(); +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; +id name leftpar rightpar path +1 A 2 3 1 +2 LA 4 5 1,2 +4 LLA 6 7 1,2,4 +6 LLLA NULL NULL 1,2,4,6 +7 RLLA NULL NULL 1,2,4,7 +5 RLA 8 9 1,2,5 +8 LRLA NULL NULL 1,2,5,8 +9 RRLA NULL NULL 1,2,5,9 +3 RA 10 11 1,3 +10 LRA 12 13 1,3,10 +11 RRA 14 15 1,3,11 +15 RRRA NULL NULL 1,3,11,15 +EXPLAIN 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; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 15 Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 15 Using where +3 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 Using where +3 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 +4 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 Using where +4 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 +NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL +DROP TABLE t1,t2; +SET big_tables=0; +# +# MDEV-15840: recursive tables are accessed by key +# (the same problem as for MDEV-15556) +# +CREATE TABLE t1 (p1 text,k2 int, p2 text, k1 int); +INSERT INTO t1 select seq, seq, seq, seq from seq_1_to_1000; +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 | +call getNums(); +p1 k2 p2 k1 +1 1 1 1 +2 2 2 2 +3 3 3 3 +4 4 4 4 +5 5 5 5 +6 6 6 6 +7 7 7 7 +8 8 8 8 +9 9 9 9 +10 10 10 10 +DROP PROCEDURE getNums; +DROP TABLE t1; diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 008c8ea..e3032fc 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2111,7 +2111,7 @@ select * from qn; set big_tables=default; --echo # ---echo # MDEV-1571: Setting user variable in recursive CTE +--echo # MDEV-15571: using recursive cte with big_tables enabled --echo # set big_tables=1; @@ -2126,3 +2126,67 @@ with recursive qn as 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; diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index dd46295..6bc833b 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1401,7 +1401,7 @@ bool With_element::instantiate_tmp_tables() { if (!rec_table->is_created() && instantiate_tmp_table(rec_table, - rec_result->tmp_table_param.keyinfo, + rec_table->s->key_info, rec_result->tmp_table_param.start_recinfo, &rec_result->tmp_table_param.recinfo, 0))