[MDEV-17201] dropped anchor rows with non-null recursion query Created: 2018-09-15  Updated: 2018-09-26  Resolved: 2018-09-26

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.3.9, 10.2
Fix Version/s: 10.2.18

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: cte, wrong_result


 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 |
 
+--------------+---------------+----------+------+--------------------+------------+



 Comments   
Comment by Igor Babaev [ 2018-09-17 ]

The bug is reproducible in 10.2 as well (with the same test case).

Comment by Igor Babaev [ 2018-09-18 ]

A fix for this bug was pushed into 10.2

Comment by Daniel Black [ 2018-09-18 ]

Thanks for the fix igor

Comment by Daniel Black [ 2018-09-19 ]

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.

Comment by Igor Babaev [ 2018-09-25 ]

Daniel,
I don't understand what is your argument.
Please explain why the result set in the test case must be different.

Comment by Daniel Black [ 2018-09-25 ]

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

Comment by Igor Babaev [ 2018-09-26 ]

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.

Comment by Daniel Black [ 2018-09-26 ]

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.

Generated at Thu Feb 08 08:34:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.