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

Suspicious EXPLAIN output for a derived table + WITH + joined table

Details

    Description

      This script:

      DROP TABLE IF EXISTS t1,t2,t3;
      		 
      CREATE TABLE t1 (a INT);
      		 
      CREATE TABLE t2 (a INT);
      		 
      CREATE TABLE t3 (a INT);
      		 
      INSERT INTO t1 VALUES (1),(2),(3);
      		 
      INSERT INTO t2 VALUES (1),(2),(3);
      		 
      INSERT INTO t3 VALUES (1),(2),(3);
      		 
      EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3));

      returns

      +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
      		 
      |    1 | PRIMARY     | t2    | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                                 |
      		 
      |    1 | PRIMARY     | t3    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer (flat, BNL join) |
      		 
      |    2 | SUBQUERY    | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                                 |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+

      This looks wrong. The table "t1" does not affect the result and should probably be optimized away.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-10057 Crash with EXPLAIN + WITH + constant query

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Assignee Igor Babaev [ igor ]
            bar Alexander Barkov made changes -
            Description This script:
            {code:sql}
            DROP TABLE IF EXISTS t1,t2,t3;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 (a INT);
            CREATE TABLE t3 (a INT);
            INSERT INTO t1 VALUES (1),(2),(3);
            INSERT INTO t2 VALUES (1),(2),(3);
            INSERT INTO t3 VALUES (1),(2),(3);
            EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3));
            {code}
            returns
            {noformat}
            +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
            | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
            +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
            | 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 3 | |
            | 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using join buffer (flat, BNL join) |
            | 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | |
            +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
            {noforma}
            This looks wrong. The table "t1" does not affect the result and should probably be optimized away.
            		This script:
            {code:sql}
            DROP TABLE IF EXISTS t1,t2,t3;
            CREATE TABLE t1 (a INT);
            CREATE TABLE t2 (a INT);
            CREATE TABLE t3 (a INT);
            INSERT INTO t1 VALUES (1),(2),(3);
            INSERT INTO t2 VALUES (1),(2),(3);
            INSERT INTO t3 VALUES (1),(2),(3);
            EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3));
            {code}
            returns
            {noformat}
            +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
            | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
            +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
            | 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 3 | |
            | 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using join buffer (flat, BNL join) |
            | 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 3 | |
            +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
            {noformat}
            This looks wrong. The table "t1" does not affect the result and should probably be optimized away.
            elenst Elena Stepanova made changes -
            Labels CTE
            bar Alexander Barkov made changes -
            elenst Elena Stepanova made changes -
            Component/s Optimizer - CTE [ 13513 ]
            Component/s Optimizer [ 10200 ]
            psergei Sergei Petrunia made changes -
            Fix Version/s 10.2.2 [ 22013 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            psergei Sergei Petrunia made changes -
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 75651 ] MariaDB v4 [ 150421 ]

            People

              igor Igor Babaev (Inactive)
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.