Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
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
-
-
- Closed
-
Activity
So,
- the non-standard syntax is not accepted anymore
- the standard syntax query doesn't crash and EXPLAIN output looks reasonable.
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)
|
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.
Closing as all issues seem to have been resolved.