[MDEV-10058] Suspicious EXPLAIN output for a derived table + WITH + joined table Created: 2016-05-12  Updated: 2016-09-07  Resolved: 2016-09-07

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.2
Fix Version/s: 10.2.2

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: CTE

Issue Links:
Relates
relates to MDEV-10057 Crash with EXPLAIN + WITH + constant ... Closed

 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.

 Comments   
Comment by Alexander Barkov [ 2016-05-12 ]

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.

Comment by Sergei Petrunia [ 2016-09-04 ]

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)

Comment by Sergei Petrunia [ 2016-09-04 ]

So,

  • the non-standard syntax is not accepted anymore
  • the standard syntax query doesn't crash and EXPLAIN output looks reasonable.
Comment by Sergei Petrunia [ 2016-09-04 ]

Closing as all issues seem to have been resolved.

Generated at Thu Feb 08 07:39:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.