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

EXPLAIN EXTENDED shows a wrong operation for query with UNION ALL after INTERSECT

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.3.12
    • 10.3.13
    • Optimizer
    • None

    Description

      Here what's shown in the warnings of the EXPLAIN EXTENDED for the query

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

      when create and populate t1,t2,t3 with the following statements:

      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);
      

      MariaDB [test]> explain extended select * from t2 where a < 5 intersect select * from t3 where a < 5 union all select * from t1 where a > 4; +------+-------------+-------------+------+---------------+------+---------+------+------+----------+-------------+
      | id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +------+-------------+-------------+------+---------------+------+---------+------+------+----------+-------------+
      |    1 | PRIMARY     | t2          | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using where |
      |    2 | INTERSECT   | t3          | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using where |
      |    3 | UNION       | t1          | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using where |
      | NULL | UNIT RESULT | <unit1,2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL |             |
      +------+-------------+-------------+------+---------------+------+---------+------+------+----------+-------------+
      4 rows in set, 1 warning (0.002 sec)
       
      MariaDB [test]> show warnings;
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                                                                                                                 |
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where `test`.`t2`.`a` < 5 intersect /* select#2 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5 union /* select#3 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 |
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      For comparison EXPLAIN EXTENDED for the query

      explain extended select * from t3 where a < 5 union all select * from t1 where a > 4;
      

      shows everything as expected

      MariaDB [test]> explain extended select * from t3 where a < 5 union all select * from t1 where a > 4;
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      |    1 | PRIMARY     | t3    | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using where |
      |    2 | UNION       | t1    | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
      2 rows in set, 1 warning (0.001 sec)
       
      MariaDB [test]> show warnings;
      +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                                                                                   |
      +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5 union all /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 |
      +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Attachments

        Activity

          People

            igor Igor Babaev
            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.