Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17201

dropped anchor rows with non-null recursion query

    XMLWordPrintable

Details

    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

          People

            igor Igor Babaev
            danblack Daniel Black
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.