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

Incorrect result (missing row) with UNION DISTINCT in anchor parts

    XMLWordPrintable

Details

    Description

       
      create table t1(a int);
      insert into t1 values(1),(2);
      insert into t1 values(1),(2);
       
      set @c=0, @d=0;
      WITH RECURSIVE qn AS
      (
      select 1,0 as col from t1
      union distinct
      select 1,0 from t1
      union all
      select 3, 0*(@c:=@c+1) from qn where @c<1
      union all
      select 3, 0*(@d:=@d+1) from qn where @d<1
      )
      select * from qn;
      

      MariaDB [test]> create table t1(a int);
      Query OK, 0 rows affected (0.35 sec)
       
      MariaDB [test]> insert into t1 values(1),(2);
      Query OK, 2 rows affected (0.15 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into t1 values(1),(2);
      Query OK, 2 rows affected (0.06 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> set @c=0, @d=0;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> WITH RECURSIVE qn AS
          -> (
          -> select 1,0 as col from t1
          -> union distinct
          -> select 1,0 from t1
          -> union all
          -> select 3, 0*(@c:=@c+1) from qn where @c<1
          -> union all
          -> select 3, 0*(@d:=@d+1) from qn where @d<1
          -> )
          -> select * from qn;
      +---+-----+
      | 1 | col |
      +---+-----+
      | 1 |   0 |
      | 3 |   0 |
      +---+-----+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> select 1,0 as col from t1         # expected result
          -> union distinct
          -> select 1,0 from t1
          -> union all
          -> select 3, 0 
          -> union all
          -> select 3, 0;
      +---+-----+
      | 1 | col |
      +---+-----+
      | 1 |   0 |
      | 3 |   0 |
      | 3 |   0 |
      +---+-----+
      3 rows in set (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

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