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

Recursive CTE performance

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Duplicate
    • Affects Version/s: 10.2, 10.3
    • Fix Version/s: N/A
    • Component/s: Optimizer - CTE
    • Labels:
      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

              Assignee:
              Unassigned
              Reporter:
              greenman Ian Gilfillan
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: