[MDEV-10058] Suspicious EXPLAIN output for a derived table + WITH + joined table - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2(EOL)
Fix Version/s: 10.2.2
Component/s: Optimizer - CTE
Labels:
- CTE

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

MDEV-10057 Crash with EXPLAIN + WITH + constant query

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2016-05-12 10:40

Alexander Barkov made changes - 2016-05-12 10:40

Field	Original Value	New Value
Assignee		Igor Babaev [ igor ]

Alexander Barkov made changes - 2016-05-12 10:41

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.

Alexander Barkov added a comment - 2016-05-12 10:44 - 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~~.

Alexander Barkov added a comment - 2016-05-12 10:44 - 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 .

Elena Stepanova made changes - 2016-05-12 10:45

Labels

CTE

Alexander Barkov made changes - 2016-05-12 10:49

Link

This issue relates to ~~MDEV-10057~~ [ ~~MDEV-10057~~ ]

Elena Stepanova made changes - 2016-05-14 09:30

Component/s		Optimizer - CTE [ 13513 ]
Component/s	Optimizer [ 10200 ]

Sergei Petrunia added a comment - 2016-09-04 10:39

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)

Sergei Petrunia added a comment - 2016-09-04 10:40

So,

the non-standard syntax is not accepted anymore
the standard syntax query doesn't crash and EXPLAIN output looks reasonable.

Sergei Petrunia added a comment - 2016-09-04 10:40 So, the non-standard syntax is not accepted anymore the standard syntax query doesn't crash and EXPLAIN output looks reasonable.

Sergei Petrunia added a comment - 2016-09-04 10:44

Closing as all issues seem to have been resolved.

Sergei Petrunia added a comment - 2016-09-04 10:44 Closing as all issues seem to have been resolved.

Sergei Petrunia made changes - 2016-09-04 10:44

Fix Version/s		10.2.2 [ 22013 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Alexander Barkov made changes - 2016-09-07 06:27

Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Sergei Petrunia made changes - 2016-09-07 09:31

Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:42

Workflow

MariaDB v3 [ 75651 ]

MariaDB v4 [ 150421 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2016-05-12 10:40

Updated:: 2016-09-07 09:31

Resolved:: 2016-09-07 09:31

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration