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

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

    XMLWordPrintable

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

          Activity

            People

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