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

Recursive CTE performance

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Duplicate
    • 10.2(EOL), 10.3(EOL)
    • N/A
    • Optimizer - CTE
    • None

    Description

      The following CTE runs almost instantly on MySQL, but hangs in MariaDB - tested on 10.3.10 and 10.2.18:

      WITH RECURSIVE x(i) AS (
          SELECT CAST(0 AS DECIMAL(13, 10))
           UNION ALL
          SELECT i + 1
            FROM x
           WHERE i < 101
      ),
      Z(Ix, Iy, Cx, Cy, X, Y, I) AS (
          SELECT Ix, Iy, X, Y, X, Y, 0
            FROM (SELECT CAST(-2.2 + 0.031 * i AS DECIMAL(13, 10)) AS X,
                        i AS Ix FROM x) AS xgen
                 CROSS JOIN (
                     SELECT CAST(-1.5 + 0.031 * i AS DECIMAL(13, 10)) AS Y,
                            i AS iY FROM x
                 ) AS ygen
          UNION ALL
          SELECT Ix, Iy, Cx, Cy,
                 CAST(X * X - Y * Y + Cx AS DECIMAL(13, 10)) AS X,
                 CAST(Y * X * 2 + Cy AS DECIMAL(13, 10)), I + 1
            FROM Z
           WHERE X * X + Y * Y < 16.0
                 AND I < 27
      ),
      Zt (Ix, Iy, I) AS (
          SELECT Ix, Iy, MAX(I) AS I
            FROM Z
           GROUP BY Iy, Ix
           ORDER BY Iy, Ix
      )
      SELECT GROUP_CONCAT(
                 SUBSTRING(
                     ' .,,,-----++++%%%%@@@@#### ',
                     GREATEST(I, 1),
                     1
                 ) ORDER BY Ix SEPARATOR ''
             ) AS 'Mandelbrot Set'
        FROM Zt
       GROUP BY Iy
       ORDER BY Iy;
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              greenman Ian Gilfillan
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.