Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
Currently, MariaDB will not reuse CTEs that are used multiple times in the query (I am talking only about non-recursive CTEs).
Example:
create table t1 (a int, b int); |
insert into t1 select seq, seq from seq_1_to_10000; |
|
explain
|
with cte1 as (select a,b from t1 order by a limit 10) |
select |
*
|
from cte1 as t1, cte1 as t2 where t1.a=t2.a; |
+------+-------------+------------+------+---------------+------+---------+------+-------+----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+------+---------------+------+---------+------+-------+----------------+
|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 1 | PRIMARY | <derived3> | ref | key0 | key0 | 5 | t1.a | 1 | |
|
| 3 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 10000 | Using filesort |
|
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 10000 | Using filesort |
|
+------+-------------+------------+------+---------------+------+---------+------+-------+----------------+
|
This will use the CTE twice.
One reason one may not want to reuse the CTE is that one use of the CTE uses condition pushdown or Split Materialized optimization while the other does not.
But what if both uses of CTE do not use either? Then, a CTE could be computed only once.
Attachments
Issue Links
- relates to
-
MDEV-35970 streaming window functions
-
- Needs Feedback
-