Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.9, 10.2(EOL)
Description
CREATE TABLE `purchases` ( |
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`pdate` date NOT NULL, |
`quantity` int(10) unsigned NOT NULL, |
`p_id` int(10) unsigned NOT NULL, |
PRIMARY KEY (`id`) |
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; |
|
|
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(10) unsigned NOT NULL, |
`p_id` int(10) unsigned NOT NULL, |
PRIMARY KEY (`edate`,`p_id`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
|
|
insert into expired values ('2014-11-12', 5 ,1),('2014-11-08', 1 ,2); |
Query with empty recursive component:
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 > 3000 |
)
|
)
|
select * from expired_map ; |
correctly show the two expired fields:
+--------------+---------------+----------+------+--------------------+------------+
|
| expired_date | purchase_date | quantity | p_id | purchase_processed | unresolved |
|
+--------------+---------------+----------+------+--------------------+------------+
|
| 2014-11-08 | NULL | 0 | 2 | 5 | 1 |
|
| 2014-11-12 | NULL | 0 | 1 | 5 | 5 |
|
+--------------+---------------+----------+------+--------------------+------------+
|
|
however:
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 ; |
This drops the second row from the anchor query (p_id=1)
+--------------+---------------+----------+------+--------------------+------------+
|
| expired_date | purchase_date | quantity | p_id | purchase_processed | unresolved |
|
+--------------+---------------+----------+------+--------------------+------------+
|
| 2014-11-08 | NULL | 0 | 2 | 5 | 1 |
|
| 2014-11-08 | 2014-11-03 | 1 | 2 | 4 | 0 |
|
+--------------+---------------+----------+------+--------------------+------------+
|
Attachments
Activity
There need to be another reset of the limit because even with the one fix so far, there should be at least one recursive result with p_id=1. Removing the LIMIT clause from the union parts does generate a more complete result.
Daniel,
I don't understand what is your argument.
Please explain why the result set in the test case must be different.
If I take out the LIMIT clause on the test...
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
|
index acaf95beda7..fee643fa470 100644
|
--- a/mysql-test/t/cte_recursive.test
|
+++ b/mysql-test/t/cte_recursive.test
|
@@ -2434,7 +2434,6 @@ WITH RECURSIVE expired_map AS (
|
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;
|
The test result is:
CURRENT_TEST: main.cte_recursive
|
--- /home/dan/repos/mariadb-server-10.2/mysql-test/r/cte_recursive.result 2018-09-19 09:51:51.740905063 +1000
|
+++ /home/dan/repos/mariadb-server-10.2/mysql-test/r/cte_recursive.reject 2018-09-26 09:27:49.259689500 +1000
|
@@ -3432,12 +3432,15 @@
|
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-12 2014-11-01 5 1 1 0
|
+2014-11-12 2014-11-03 3 1 2 2
|
+2014-11-08 2014-11-01 1 2 3 0
|
2014-11-08 2014-11-03 1 2 4 0
|
+2014-11-12 2014-11-01 2 1 1 0
|
DROP TABLE purchases, expired;
|
|
|
mysqltest: Result length mismatch
|
All these extra rows match p_pid=1
So with a LIMIT 1 clause, the following two rows (from above, the last is eliminated by the limit i think) with p_pid=1 should appear in the output:
expired_date purchase_date qty p_id purchase_processed unresolve
|
+2014-11-12 2014-11-03 3 1 2 2
|
+2014-11-12 2014-11-01 5 1 1 0
|
Daniel,
Here is how the rows are generated if LIMIT is not used:
MariaDB [test]> set max_recursive_iterations = 1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [test]> 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 ) ) SELECT * FROM expired_map;
|
+--------------+---------------+----------+------+--------------------+------------+
|
| expired_date | purchase_date | quantity | p_id | purchase_processed | unresolved |
|
+--------------+---------------+----------+------+--------------------+------------+
|
| 2014-11-08 | NULL | 0 | 2 | 5 | 1 |
|
| 2014-11-12 | NULL | 0 | 1 | 5 | 5 |
|
| 2014-11-08 | 2014-11-01 | 1 | 2 | 3 | 0 |
|
| 2014-11-08 | 2014-11-03 | 1 | 2 | 4 | 0 |
|
| 2014-11-12 | 2014-11-01 | 5 | 1 | 1 | 0 |
|
| 2014-11-12 | 2014-11-03 | 3 | 1 | 2 | 2 |
|
+--------------+---------------+----------+------+--------------------+------------+
|
|
|
MariaDB [test]> set max_recursive_iterations = 2;
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [test]> 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 ) ) SELECT * FROM expired_map;
|
+--------------+---------------+----------+------+--------------------+------------+
|
| expired_date | purchase_date | quantity | p_id | purchase_processed | unresolved |
|
+--------------+---------------+----------+------+--------------------+------------+
|
| 2014-11-08 | NULL | 0 | 2 | 5 | 1 |
|
| 2014-11-12 | NULL | 0 | 1 | 5 | 5 |
|
| 2014-11-08 | 2014-11-01 | 1 | 2 | 3 | 0 |
|
| 2014-11-08 | 2014-11-03 | 1 | 2 | 4 | 0 |
|
| 2014-11-12 | 2014-11-01 | 5 | 1 | 1 | 0 |
|
| 2014-11-12 | 2014-11-03 | 3 | 1 | 2 | 2 |
|
| 2014-11-12 | 2014-11-01 | 2 | 1 | 1 | 0 |
|
+--------------+---------------+----------+------+--------------------+------------+
|
The second iteration produces only 1 row with p_id=1.
If LIMIT is set to 1 then only 1 row from the first iteration is produced and this row has p_id=2. This row cannot produce anything else.
I final understand. My error. Thanks for the detail.
As I understand it now:
After the first recursive query, the only results that are passed to the next iteration are the results of that first recursive query.
In this test case, two initial anchor rows doesn't mean that each exists individually as a point from which recursion starts, both rows together are the one starting point.
Ready to close. Mea Culpa.
The bug is reproducible in 10.2 as well (with the same test case).