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

Query with CTE hangs if contains straight_join

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.2, 10.3
    • N/A
    • Optimizer - CTE
    • 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)
      

      Attachments

        Activity

          People

            alice Alice Sherepa
            alice Alice Sherepa
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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