revision-id: 6858d5346c0823019aea4b501e8e48490708745c (mariadb-10.2.16-146-g6858d53) parent(s): 65474d92f59af209c0e96699f8bf55d6ca0238d6 author: Igor Babaev committer: Igor Babaev timestamp: 2018-09-17 23:15:56 -0700 message: MDEV-17201 dropped anchor rows with non-null recursion query The function st_select_lex_unit::exec_recursive() missed resetting of select_limit_cnt and offset_limit_cnt before execution of union parts. As a result recursive CTEs specified by UNIONs whose SELECTs contained LIMIT/OFFSET could return wrong sets of records. --- mysql-test/r/cte_recursive.result | 48 +++++++++++++++++++++++++++++++++++++ mysql-test/t/cte_recursive.test | 50 +++++++++++++++++++++++++++++++++++++++ sql/sql_union.cc | 1 + 3 files changed, 99 insertions(+) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 55733be..de23d54 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3393,3 +3393,51 @@ select * from cte1, cte2 where cte1.c1 = 3; c1 c2 drop procedure p; drop table t1,t2; +# +# MDEV-17201: recursive part with LIMIT +# +CREATE TABLE purchases ( +id int unsigned NOT NULL AUTO_INCREMENT, +pdate date NOT NULL, +quantity int unsigned NOT NULL, +p_id int unsigned NOT NULL, +PRIMARY KEY (id) +); +INSERT INTO purchases(pdate, quantity, p_id) VALUES +('2014-11-01',5 ,1),('2014-11-03', 3 ,1), +('2014-11-01',2 ,2),('2014-11-03', 4 ,2); +CREATE TABLE expired ( +edate date NOT NULL, +quantity int unsigned NOT NULL, +p_id int unsigned NOT NULL, +PRIMARY KEY (edate,p_id) +); +INSERT INTO expired VALUES ('2014-11-12', 5 ,1),('2014-11-08', 1 ,2); +WITH RECURSIVE expired_map AS ( +SELECT edate AS expired_date, +CAST(NULL AS date) AS purchase_date, +0 AS quantity, +e.p_id, +(SELECT MAX(id)+1 FROM purchases p +WHERE pdate <= edate AND p.p_id =p_id) AS purchase_processed, +quantity AS unresolved +FROM expired e +UNION +( SELECT expired_date, +pdate, +IF(p.quantity < m.unresolved, p.quantity, m.unresolved), +p.p_id, +p.id, +IF(p.quantity < m.unresolved, m.unresolved - p.quantity, 0) +FROM purchases p JOIN expired_map m ON p.p_id = m.p_id +WHERE p.id < m.purchase_processed AND m.unresolved > 0 +ORDER BY p.id DESC +LIMIT 1 +) +) +SELECT * FROM expired_map; +expired_date purchase_date quantity p_id purchase_processed unresolved +2014-11-12 NULL 0 1 5 5 +2014-11-08 NULL 0 2 5 1 +2014-11-08 2014-11-03 1 2 4 0 +DROP TABLE purchases, expired; diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index e3a9349..acaf95b 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2390,3 +2390,53 @@ eval $q3; drop procedure p; drop table t1,t2; + +--echo # +--echo # MDEV-17201: recursive part with LIMIT +--echo # + +CREATE TABLE purchases ( + id int unsigned NOT NULL AUTO_INCREMENT, + pdate date NOT NULL, + quantity int unsigned NOT NULL, + p_id int unsigned NOT NULL, + PRIMARY KEY (id) +); +INSERT INTO purchases(pdate, quantity, p_id) VALUES + ('2014-11-01',5 ,1),('2014-11-03', 3 ,1), + ('2014-11-01',2 ,2),('2014-11-03', 4 ,2); + +CREATE TABLE expired ( + edate date NOT NULL, + quantity int unsigned NOT NULL, + p_id int unsigned NOT NULL, + PRIMARY KEY (edate,p_id) +); + +INSERT INTO expired VALUES ('2014-11-12', 5 ,1),('2014-11-08', 1 ,2); + +WITH RECURSIVE expired_map AS ( + SELECT edate AS expired_date, + CAST(NULL AS date) AS purchase_date, + 0 AS quantity, + e.p_id, + (SELECT MAX(id)+1 FROM purchases p + WHERE pdate <= edate AND p.p_id =p_id) AS purchase_processed, + quantity AS unresolved + FROM expired e + UNION + ( SELECT expired_date, + pdate, + IF(p.quantity < m.unresolved, p.quantity, m.unresolved), + p.p_id, + p.id, + IF(p.quantity < m.unresolved, m.unresolved - p.quantity, 0) + FROM purchases p JOIN expired_map m ON p.p_id = m.p_id + WHERE p.id < m.purchase_processed AND m.unresolved > 0 + ORDER BY p.id DESC + LIMIT 1 + ) +) +SELECT * FROM expired_map; + +DROP TABLE purchases, expired; diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 419ccf4..44879f6 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1274,6 +1274,7 @@ bool st_select_lex_unit::exec_recursive() for (st_select_lex *sl= start ; sl != end; sl= sl->next_select()) { thd->lex->current_select= sl; + set_limit(sl); sl->join->exec(); saved_error= sl->join->error; if (!saved_error)