Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-36539

Non-merged CTEs: reuse the CTE if there's no condition pushdown

Details

    • New Feature
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Optimizer
    • 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

          Activity

            There are no comments yet on this issue.

            People

              Unassigned Unassigned
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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