Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.2, 10.3
-
None
Description
it should be a restriction to not use straight_join inside the anchor part of cte
testcase:
CREATE TABLE t1(a int); |
INSERT INTO t1 VALUES(1),(2); |
|
WITH RECURSIVE cte AS |
(SELECT 2 as a FROM t1 UNION ALL |
SELECT 5 FROM t1 straight_join cte) |
SELECT * FROM cte; |
explain WITH RECURSIVE cte AS
|
(SELECT 2 as a FROM t1 UNION ALL
|
SELECT 5 FROM t1 straight_join cte)
|
SELECT * FROM cte
|
--------------
|
|
+------+-----------------+------------+------+---------------+------+---------+------+------+------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+------+---------------+------+---------+------+------+------------------------------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | |
|
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
|
| 3 | RECURSIVE UNION | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
|
| 3 | RECURSIVE UNION | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using join buffer (flat, BNL join) |
|
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
|
+------+-----------------+------------+------+---------------+------+---------+------+------+------------------------------------+
|
5 rows in set (0.001 sec)
|