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

NOT IN subquery predicate with recursive reference is ignored

    XMLWordPrintable

Details

    Description

      If in a CTE specification the WHERE condition of a recursive SELECT contains NOT IN subquery predicate with recursive reference then this predicate is completely ignored when the query that uses this CTE is executed.

      This problem can be demonstrated with the following simple test case.

      create table t1 (lp char(4) not null, rp char(4) not null);
      insert into t1 values
         ('p1','p2'), ('p2','p3'), ('p3','p4'), ('p4','p5');
       
      set standard_compliant_cte=0;
       
      with recursive
      reachables(p) as 
      ( 
         select lp from t1 where lp = 'p1'
         union 
         select t1.rp from reachables, t1 
             where t1.lp = reachables.p
      )
      select * from reachables;
       
      with recursive 
      reachables(p) as
      (
         select lp from t1 where lp = 'p1'
         union 
         select t1.rp from reachables, t1 
            where 'p3' not in (select * from reachables) and
                       t1.lp = reachables.p
      )
      select * from reachables;
      

      While for the first query the result set is correct:

      MariaDB [test]> with recursive
          -> reachables(p) as 
          -> ( 
          ->    select lp from t1 where lp = 'p1'
          ->    union 
          ->    select t1.rp from reachables, t1 
          ->        where t1.lp = reachables.p
          -> )
          -> select * from reachables;
      +----+
      | p  |
      +----+
      | p1 |
      | p2 |
      | p3 |
      | p4 |
      | p5 |
      +----+
      

      the output from the second query is not what is expected:

      MariaDB [test]> with recursive 
          -> reachables(p) as
          -> (
          ->    select lp from t1 where lp = 'p1'
          ->    union 
          ->    select t1.rp from reachables, t1 
          ->       where 'p3' not in (select * from reachables) and
          ->                  t1.lp = reachables.p
          -> )
          -> select * from reachables;
      +----+
      | p  |
      +----+
      | p1 |
      | p2 |
      | p3 |
      | p4 |
      | p5 |
      +----+
      

      Expected:

      MariaDB [test]> with recursive 
          -> reachables(p) as
          -> (
          ->    select lp from t1 where lp = 'p1'
          ->    union 
          ->    select t1.rp from reachables, t1 
          ->       where 'p3' not in (select * from reachables) and
          ->                  t1.lp = reachables.p
          -> )
          -> select * from reachables;
      +----+
      | p  |
      +----+
      | p1 |
      | p2 |
      | p3 |
      +----+
      

      Attachments

        Activity

          People

            igor Igor Babaev
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.