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

Wrong result from query that uses INTERSECT after UNION ALL

    XMLWordPrintable

Details

    Description

      With the following tables:

      create table t1 (a int);
      insert into t1 values (3), (1), (7), (3), (2), (7), (4);
      create table t2 (a int);
      insert into t2 values (4), (5), (9), (1), (8), (9);
      create table t3 (a int);
      insert into t3 values (8), (1), (8), (2), (3), (7), (2);
      

      the query

      select * from t1 where a > 4
      union all 
      select * from t2 where a < 5
      intersect
      select * from t3 where a < 5
      

      returns a wrong result set in 10.4:

      MariaDB [test]> select * from t1 where a > 4
          -> union all
          -> select * from t2 where a < 5
          -> intersect
          -> select * from t3 where a < 5
          -> ;
      +------+
      | a    |
      +------+
      |    7 |
      |    1 |
      +------+
      2 rows in set
      

      The expected result is returned when UNION ALL follows INTERSECT:

      MariaDB [test]> select * from t2 where a < 5
          -> intersect
          -> select * from t3 where a < 5
          -> union all
          -> select * from t1 where a > 4;
      +------+
      | a    |
      +------+
      |    7 |
      |    1 |
      |    7 |
      +------+
      3 rows in set
      

      10.3 is free of this bug:

      MariaDB [test]> select version();
      +-----------------------+
      | version()             |
      +-----------------------+
      | 10.3.14-MariaDB-debug |
      +-----------------------+
      MariaDB [test]> select * from t1 where a > 4
          -> union all
          -> select * from t2 where a < 5
          -> intersect
          -> select * from t3 where a < 5;
      +------+
      | a    |
      +------+
      |    7 |
      |    7 |
      |    1 |
      +------+
      3 rows in set 
      

      Attachments

        Activity

          People

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