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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.3.12
    • Fix Version/s: 10.3.13
    • Component/s: Optimizer
    • Labels:
      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

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

              Dates

              • Created:
                Updated:
                Resolved: