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

Unioning two recursive queries

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2, 10.3.1
    • Fix Version/s: 10.2.10
    • Component/s: Optimizer - CTE
    • Labels:
      None
    • Environment:
      CentOS Linux release 7.4.1708 (Core)
      10.3.1-MariaDB

      Description

      I have two seperate recursive queries that run up and down a tree of nodes to collect information.
      For visualization you can compare this to a binary tree, pick a random node in the tree, for this node you get all parents (grandparents etc.) and get all children (grandchildren etc.)
      For this I run two queries, one for upwards and one for downwards.
      When I want to show the results for this, I can either show one or show the other, but when trying to UNION the results the server stops responding. The only way to use the server again after using this query is to reset the mysql server using 'mysqld service restart'.
      'SELECT * FROM temp' shows the correct results for that table.

      All code used:

      WITH recursive temp as (
         SELECT current_template, current_iteration, parent_template, parent_iteration, tkey, val
         FROM templatevalues
         WHERE tkey='linnumber' AND val='1' AND guid='ffb7e0cd-5dff-4b83-be88-ebf66d5557be' -- this is the starting point
         UNION ALL
         SELECT c.current_template, c.current_iteration, c.parent_template, c.parent_iteration, c.tkey, c.val
         FROM templatevalues c
         JOIN temp p ON p.parent_template = c.current_template AND p.parent_iteration = c.current_iteration AND guid='ffb7e0cd-5dff-4b83-be88-ebf66d5557be' -- this is the recursion
      ),
      temp2 as (
         SELECT current_template, current_iteration, parent_template, parent_iteration, tkey, val
         FROM templatevalues
         WHERE tkey='linnumber' AND val='1' AND guid='ffb7e0cd-5dff-4b83-be88-ebf66d5557be' -- this is the starting point
         UNION ALL
         SELECT c.current_template, c.current_iteration, c.parent_template, c.parent_iteration, c.tkey, c.val
         FROM templatevalues c
         JOIN temp2 p ON c.parent_template = p.current_template AND c.parent_iteration = p.current_iteration AND guid='ffb7e0cd-5dff-4b83-be88-ebf66d5557be' -- this is the recursion
      )
      SELECT *
      FROM temp UNION ALL (SELECT * FROM temp2);
      

      I included a file with a dump of a few lines of the database I used. Use this to test on other environments. Even with these two lines in the table it still times out, which should not happen.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              fjf Duncan Kampert
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: