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

Recursive CTE crashes or gets "table full" errors

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.2
    • 10.2.3
    • Optimizer - CTE
    • None
    • Centos 7

    Description

      Using the following simple table and dataset from the extensive CTE examples here: https://inviqa.com/blog/graphs-database-sql-meets-social-network

      DROP TABLE IF EXISTS `edges`;
      CREATE TABLE `edges` (
        `a` int(10) unsigned NOT NULL,
        `b` int(10) unsigned NOT NULL,
        PRIMARY KEY (`a`,`b`),
        KEY `b` (`b`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      INSERT INTO `edges` VALUES (1,3),(2,1),(2,4),(3,4),(3,5),(3,6),(4,7),(5,1),(5,6),(6,1);
       
      DROP TABLE IF EXISTS `edges2`;
      CREATE VIEW edges2 (a, b) AS SELECT a, b FROM edges   UNION ALL   SELECT b, a FROM edges;
      

      When trying any of the examples the server either crashes hard, or the query runs but aborts with ERROR 1114 (HY000): The table '/tmp/#sql_4b24_1' is full.

      Some of the queries I ran are:

      Causes a "table full":

      WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
      ( SELECT a, b, 1 AS distance,
          a || '.' || b || '.' AS path_string
        FROM edges
       
        UNION ALL
       
        SELECT tc.a, e.b, tc.distance + 1,
        tc.path_string || e.b || '.' AS path_string
        FROM edges AS e
          JOIN transitive_closure AS tc
            ON e.a = tc.b
        WHERE tc.path_string NOT LIKE '%' || e.b || '.%'
      )
      SELECT * FROM transitive_closure
      ORDER BY a, b, distance;
      

      WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
      ( SELECT a, b, 1 AS distance,
               a || '.' || b || '.' AS path_string
        FROM edges
       WHERE a = 1 -- source
       
        UNION ALL
       
        SELECT tc.a, e.b, tc.distance + 1,
               tc.path_string || e.b || '.' AS path_string
        FROM edges AS e
        JOIN transitive_closure AS tc ON e.a = tc.b
       WHERE tc.path_string NOT LIKE '%' || e.b || '.%'
      )
        SELECT * FROM transitive_closure
         WHERE b=6 -- destination
      ORDER BY a, b, distance;
      

      Causes a hard server crash (suspect due to CTE against view):

      WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
      ( SELECT  a, b, 1 AS distance,
                a || '.' || b || '.' AS path_string
        FROM edges2
       
        UNION ALL
       
        SELECT tc.a, e.b, tc.distance + 1,
        tc.path_string || e.b || '.' AS path_string
        FROM edges2 AS e
          JOIN transitive_closure AS tc ON e.a = tc.b
        WHERE tc.path_string NOT LIKE '%' || e.b || '.%'
      )
      SELECT * FROM transitive_closure
      ORDER BY a, b, distance;
      

      WITH RECURSIVE transitive_closure(a, b, distance, path_string)
      AS
      ( SELECT a, b, 1 AS distance,
               a || '.' || b || '.' AS path_string
        FROM edges2
       
        UNION ALL
       
        SELECT tc.a, e.b, tc.distance + 1,
               tc.path_string || e.b || '.' AS path_string
        FROM edges2 AS e
        JOIN transitive_closure AS tc ON e.a = tc.b
       WHERE tc.path_string NOT LIKE '%' || e.b || '.%'
      )
      SELECT a, b, min(distance) AS dist FROM transitive_closure
      --WHERE a = 1 AND b=6
      GROUP BY a, b
      ORDER BY a, dist, b;
      

      I expect all the queries to behave as per the examples given in the article.

      Attachments

        Activity

          People

            igor Igor Babaev
            Ellerbrockr richardeaxon
            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.