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

Lost rows for query using recursive CTE with recursive reference in subquery

    Details

      Description

      With the following population of the table flights

      create table flights
      (departure varchar(32),
       arrival varchar(32),
       carrier varchar(20),
       flight_number char(7));
       
      insert into flights values
      ('Seattle', 'Frankfurt', 'Lufthansa', 'LH 491'),
      ('Seattle', 'Chicago', 'American', 'AA 2573'),
      ('Seattle', 'Los Angeles', 'Alaska Air', 'AS 410'),
      ('Chicago', 'New York', 'American', 'AA 375'),
      ('Chicago', 'Montreal', 'Air Canada', 'AC 3053'),
      ('Los Angeles', 'New York', 'Delta', 'DL 1197'),
      ('Moscow', 'Tokyo', 'Aeroflot', 'SU 264'),
      ('New York', 'Paris', 'Air France', 'AF 23'),
      ('Frankfurt', 'Moscow', 'Lufthansa', 'LH 1444'),
      ('Tokyo', 'Seattle', 'ANA', 'NH 178'),
      ('Los Angeles', 'Tokyo', 'ANA', 'NH 175'),
      ('Moscow', 'Los Angeles', 'Aeroflot', 'SU 106'),
      ('Montreal', 'Paris', 'Air Canada', 'AC 870'),
      ('Cairo', 'Paris', 'Air France', 'AF 503'),
      ('New York', 'Seattle', 'American', 'AA 45'),
      ('Paris', 'Chicago', 'Air France', 'AF 6734');
      

      the result set of the query

      set statement standard_compliant_cte=0 for
      with recursive destinations (city, legs) as
      ( 
        select a.arrival, 1 from flights a where a.departure='Cairo'
        union
        select b.arrival, r.legs + 1 from destinations r, flights b 
        where r.city=b.departure and b.arrival not in (select city from destinations)
      )
      select * from destinations;
      

      lacks rows with destinations.city='Frankfurt' and destinations.city='Moscow'

      MariaDB [test]> set statement standard_compliant_cte=0 for
          -> with recursive destinations (city, legs) as
          -> ( 
          ->   select a.arrival, 1 from flights a where a.departure='Cairo'
          ->   union
          ->   select b.arrival, r.legs + 1 from destinations r, flights b 
          ->   where r.city=b.departure and b.arrival not in (select city from destinations)
          -> )
          -> select * from destinations;
      +-------------+------+
      | city        | legs |
      +-------------+------+
      | Paris       |    1 |
      | Chicago     |    2 |
      | New York    |    3 |
      | Montreal    |    3 |
      | Seattle     |    4 |
      | Los Angeles |    5 |
      | Tokyo       |    6 |
      +-------------+------+
      

      The following query returns the correct set of cities

      MariaDB [test]> with recursive destinations (city) as
          -> ( select a.arrival from flights a where a.departure='Cairo'
          ->   union
          ->   select b.arrival from destinations r, flights b where r.city=b.departure)
          -> select * from destinations;
      +-------------+
      | city        |
      +-------------+
      | Paris       |
      | Chicago     |
      | New York    |
      | Montreal    |
      | Seattle     |
      | Frankfurt   |
      | Los Angeles |
      | Moscow      |
      | Tokyo       |
      +-------------+
      

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              igor Igor Babaev
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: