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

          Activity

            bar Alexander Barkov added a comment - - edited

            By the way, this syntax is not standard:

            SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3));
            

            <with clause> cannot be followed by a <parenthesized joined table>.
            It can be followed by <query expression> only.

            The correct standard syntax is:

            SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT * FROM t2 NATURAL JOIN t3) AS d1;
            

            And EXPLAIN for this standard query crashes. See MDEV-10057.

            bar Alexander Barkov added a comment - - edited By the way, this syntax is not standard: SELECT * FROM ( WITH a AS ( SELECT * FROM t1) (t2 NATURAL JOIN t3)); <with clause> cannot be followed by a <parenthesized joined table>. It can be followed by <query expression> only. The correct standard syntax is: SELECT * FROM ( WITH a AS ( SELECT * FROM t1) SELECT * FROM t2 NATURAL JOIN t3) AS d1; And EXPLAIN for this standard query crashes. See MDEV-10057 .

            On the current 10.2 tree:

            MariaDB [j8]> EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3));
            ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't2 NATURAL JOIN t3))' at line 1
            

            MariaDB [j8]> explain SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT * FROM t2 NATURAL JOIN t3) AS d1;
            +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
            | 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 rows in set (0.00 sec)
            

            psergei Sergei Petrunia added a comment - On the current 10.2 tree: MariaDB [j8]> EXPLAIN SELECT * FROM (WITH a AS (SELECT * FROM t1) (t2 NATURAL JOIN t3)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 't2 NATURAL JOIN t3))' at line 1 MariaDB [j8]> explain SELECT * FROM (WITH a AS (SELECT * FROM t1) SELECT * FROM t2 NATURAL JOIN t3) AS d1; +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | 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 rows in set (0.00 sec)

            So,

            • the non-standard syntax is not accepted anymore
            • the standard syntax query doesn't crash and EXPLAIN output looks reasonable.
            psergei Sergei Petrunia added a comment - So, the non-standard syntax is not accepted anymore the standard syntax query doesn't crash and EXPLAIN output looks reasonable.

            Closing as all issues seem to have been resolved.

            psergei Sergei Petrunia added a comment - Closing as all issues seem to have been resolved.

            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.