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

Recursive CTE Hangs

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Duplicate
    • Affects Version/s: 10.2.14
    • Fix Version/s: 10.2.15
    • Component/s: Optimizer - CTE
    • Labels:
      None
    • Environment:
      CentOS VMware virtual machine running MariaDB 10.2.14
      Windows 10 physical machine running MariaDB 10.2.9

      Description

      I am attempting to use the recursive common table expressions to explode a bill of materials, however I am running into a problem where the MariaDB server hangs and eventually fills up the disk if left unchecked, even though there are not any circular references in this expression.

      See the following example:

      1. Table to hold the bom

      DROP TABLE IF EXISTS bomtest;
      CREATE TABLE bomtest (
      	`Id` int(11) not null AUTO_INCREMENT,
          `Parent` varchar(15) not null,
          `Child` varchar(15) not null,
          PRIMARY KEY (`Id`)
      ) ENGINE = InnoDB;
      

      2. Add some dummy entries that represent a part/child product structure

      INSERT INTO bomtest (Parent, Child)
      VALUES
      ('123', '456'),
      ('456', '789'),
      ('321', '654'),
      ('654', '987');
      

      3. Attempt to run a recursive common table expression with a path using cast to avoid data truncation. In this query the Path column is created as TEXT because of the CAST to CHAR(513), it never seems to complete and eventually fills the disk.

      WITH RECURSIVE cte AS (
      	SELECT b.Parent, b.Child, CAST(CONCAT(b.Child,',') AS CHAR(513)) Path FROM bomtest b LEFT OUTER JOIN bomtest bc ON b.Child = bc.Parent WHERE bc.Id IS NULL
          UNION ALL
          SELECT c.Parent, c.Child, CONCAT(p.Path,c.Child,',') Path FROM bomtest c INNER JOIN cte p ON c.Child = p.Parent
      )
      SELECT * FROM cte ORDER BY Path;
      

      4. Attempt to run a recursive common table expression with a path using cast to avoid data truncation. This query the Path column is created as VARCHAR(512) because of the CAST to CHAR(512) and DOES complete.

      WITH RECURSIVE cte AS (
      	SELECT b.Parent, b.Child, CAST(CONCAT(b.Child,',') AS CHAR(512)) Path FROM bomtest b LEFT OUTER JOIN bomtest bc ON b.Child = bc.Parent WHERE bc.Id IS NULL
          UNION ALL
          SELECT c.Parent, c.Child, CONCAT(p.Path,c.Child,',') Path FROM bomtest c INNER JOIN cte p ON c.Child = p.Parent
      )
      SELECT * FROM cte ORDER BY Path;
      

      In this particular example the query just continues executing, even though there aren't any circular references. As I mention in the reproduction steps, I believe I have narrowed down part of the issue to the datatype that the Path column eventually becomes. When you CAST the path to anything over CHAR(512) the column type switches from VARCHAR to TEXT, once that happens the never completes. However if you cast to CHAR(512) and below you get the expected result and the Path column is VARCHAR.

      In my particular use case, the column must be text as the path gets very long and is key to not only detecting circular references, but also drawing the indented version of the bill of materials.

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            brycejlowe Bryce Lowe
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: