[MDEV-17635] Server hangs after the query with recursive CTE - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.2.19
Component/s: Optimizer - CTE
Labels:
None

Description

Based on the article from https://elephantdolphin.blogspot.com/2018/11/common-table-expressions-shocking.html

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),

           ) ORDER BY Ix SEPARATOR ''

       ) AS 'Mandelbrot Set'

  FROM Zt

 GROUP BY Iy

 ORDER BY Iy;

The problem is with UNION vs UNION ALL:

MariaDB [test]> WITH RECURSIVE

    -> x AS (SELECT 0 as k UNION SELECT k + 1 FROM x WHERE k < 1),

    -> z AS

    ->   ( SELECT  k1 AS cx, k2 AS cy, k1, k2

    ->    FROM (SELECT k as k1 FROM x) x1 JOIN (SELECT k as k2 FROM x) y1

    ->    UNION

    ->    SELECT 1,1,1,1 FROM z)

    -> SELECT * FROM z;

+------+------+------+------+

| cx   | cy   | k1   | k2   |

+------+------+------+------+

|    0 |    0 |    0 |    0 |

|    1 |    0 |    1 |    0 |

|    0 |    1 |    0 |    1 |

|    1 |    1 |    1 |    1 |

+------+------+------+------+

4 rows in set (0.000 sec)

################################################

MariaDB [test]> WITH RECURSIVE

    ->  x AS (SELECT 0 as k UNION ALL SELECT k + 1 FROM x WHERE k < 1),

    ->  z AS

    ->    ( SELECT  k1 AS cx, k2 AS cy, k1, k2

    ->     FROM (SELECT k  AS k1 FROM x) x1 JOIN (SELECT k  AS k2 FROM x) y1

    ->     UNION

    ->     SELECT 1,1,1,1 FROM z)

    ->  SELECT * FROM z;

^CCtrl-C -- query killed. Continuing normally.

ERROR 1317 (70100): Query execution was interrupted

MariaDB [test]> WITH RECURSIVE x(i) AS (

    ->     SELECT CAST(0 AS DECIMAL(13, 10))

    ->      UNION

    ->     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;

+--------------------------------------------------------------------------------------------------------+

| Mandelbrot Set                                                                                         |

+--------------------------------------------------------------------------------------------------------+

|              ....................................................................................      |

|             .......................................................................................    |

|            .........................................................................................   |

|           ...........................................................................................  |

|         ....................................................,,,,,,,,,................................. |

|        ................................................,,,,,,,,,,,,,,,,,,............................. |

|       ..............................................,,,,,,,,,,,,,,,,,,,,,,,,.......................... |

|      ............................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,........................ |

|      ..........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...................... |

|     .........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................... |

|    ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................... |

|   .......................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................. |

|  .......................................,,,,,,,,,,,,,,,,,,,,,,,,--,,,,,,,,,,,,,,,,,,,,................ |

| ......................................,,,,,,,,,,,,,,,,,,,,,,,,,,-+--,,,,,,,,,,,,,,,,,,,............... |

| ....................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----,,,,,,,,,,,,,,,,,,,.............. |

| ...................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,--- -----,,,,,,,,,,,,,,,,,............. |

| .................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---++--++,,,,,,,,,,,,,,,,,,............ |

| ................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----%++---,,,,,,,,,,,,,,,,,............ |

| ..............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%----,,,,,,,,,,,,,,,,,,........... |

| .............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----- %%+----,,,,,,,,,,,,,,,,,,.......... |

| ...........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---%-+%   ----,,,,,,,,,,,,,,,,,,,......... |

| ..........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+ +##  %+%---,,,,,,,,,,,,,,,,,,......... |

| ........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----#      # +---,,,,,,,,,,,,,,,,,,........ |

| .......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------%       %-----,,,,,,,,,,,,,,,,,........ |

| .....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---------+         ------,,,,,,,,,,,,,,,,,....... |

| ....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----------+@       +-----------,,,,,,,,,,,,....... |

| ..................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----@-------++       ++-----------,,,,,,,,,,,,...... |

| .................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--+@% ---+ +@%%@     %%+@+@%------+-,,,,,,,,,,,...... |

| ................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----  # ++%               % @-----++--,,,,,,,,,,,..... |

| ..............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----+    %                  %%++ %+%@-,,,,,,,,,,,..... |

| .............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+#                       #%    ++-,,,,,,,,,,,,.... |

| ............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,------+                             @---,,,,,,,,,,,,.... |

| ..........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------++%                             ---,,,,,,,,,,,,.... |

| .........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+ +                             %+---,,,,,,,,,,,,,... |

| ........,,,,,,,,,,,,,,,,,,,,,--------------------@                                +----,,,,,,,,,,,,... |

| .......,,,,,,,,,,,,,,,,,,,,,,- +-----------------+                                 ----,,,,,,,,,,,,... |

| .......,,,,,,,,,,,,,,,,,,,,,--++------+---------+%                                 +++--,,,,,,,,,,,,.. |

| ......,,,,,,,,,,,,,,,,,,,,,,--%+-----++---------                                     #+-,,,,,,,,,,,,.. |

| .....,,,,,,,,,,,,,,,,,,,,,,----#%++--+@ -+-----+%                                     --,,,,,,,,,,,,.. |

| .....,,,,,,,,,,,,,,,,,,,,,,-----+## ++@ + +----%                                    +--,,,,,,,,,,,,,.. |

| ....,,,,,,,,,,,,,,,,,,,,,,------+@  @     @@++++#                                   +--,,,,,,,,,,,,,.. |

| ....,,,,,,,,,,,,,,,,,,,,,-------%           #++%                                      -,,,,,,,,,,,,,.. |

| ...,,,,,,,,,,,,,,,,,,,,,------++%#           %%@                                     %-,,,,,,,,,,,,,,. |

| ...,,,,,,,,,,,,,,,,,,,--------+               %                                     +--,,,,,,,,,,,,,,. |

| ...,,,,,,,,,,,,,,,,,,-----+--++@              #                                      --,,,,,,,,,,,,,,. |

| ..,,,,,,,,,,,,,,,,,-------%+++%                                                    @--,,,,,,,,,,,,,,,. |

| ..,,,,,,,,,,,-------------+ @#@                                                    ---,,,,,,,,,,,,,,,. |

| ..,,,,,,,,,---@--------@-+%                                                       +---,,,,,,,,,,,,,,,. |

| ..,,,,,------- +-++++-+%%%                                                       +----,,,,,,,,,,,,,,,. |

| ..,,,,,,------%--------++%                                                       +----,,,,,,,,,,,,,,,. |

| ..,,,,,,,,,,--+----------++#                                                       ---,,,,,,,,,,,,,,,. |

| ..,,,,,,,,,,,,------------+@@@%                                                    +--,,,,,,,,,,,,,,,. |

| ..,,,,,,,,,,,,,,,,,------- +++%                                                    %--,,,,,,,,,,,,,,,. |

| ...,,,,,,,,,,,,,,,,,,---------+@              @                                      --,,,,,,,,,,,,,,. |

| ...,,,,,,,,,,,,,,,,,,,,------- #              %@                                    +--,,,,,,,,,,,,,,. |

| ...,,,,,,,,,,,,,,,,,,,,,-------++@           %+                                      %-,,,,,,,,,,,,,,. |

| ....,,,,,,,,,,,,,,,,,,,,,-------            %++%                                     %-,,,,,,,,,,,,,.. |

| ....,,,,,,,,,,,,,,,,,,,,,,------+#  %#   #@ ++++                                    +--,,,,,,,,,,,,,.. |

| .....,,,,,,,,,,,,,,,,,,,,,,-----+ %%++% +@+----+                                    +--,,,,,,,,,,,,,.. |

| .....,,,,,,,,,,,,,,,,,,,,,,,---%+++--+#+--------%                                    #--,,,,,,,,,,,,.. |

| ......,,,,,,,,,,,,,,,,,,,,,,--++-----%%---------                                    @#--,,,,,,,,,,,,.. |

| .......,,,,,,,,,,,,,,,,,,,,,---------------------+@                                +-++,,,,,,,,,,,,... |

| ........,,,,,,,,,,,,,,,,,,,,,--------------------+                                 ----,,,,,,,,,,,,... |

| .........,,,,,,,,,,,,,,,,,,,,----,,,-------------                                #+----,,,,,,,,,,,,... |

| ..........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------+ +                              +---,,,,,,,,,,,,,... |

| ...........,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+%#                           #---,,,,,,,,,,,,.... |

| ............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,------+#                        @   @---,,,,,,,,,,,,.... |

| .............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+#                        +    @--,,,,,,,,,,,,.... |

| ..............,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+%   %+@                 %+-+ +++%-,,,,,,,,,,,..... |

| ................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----% %@++              # %  -----++-,,,,,,,,,,,,..... |

| .................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-- ++ ---+ + +%@     %++++++------%-,,,,,,,,,,,...... |

| ...................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---- -------++       +------------,,,,,,,,,,,,...... |

| ....................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----------+%       +--------,,,,,,,,,,,,,,,....... |

| ......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,--------+#        -----,,,,,,,,,,,,,,,,,,....... |

| .......................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-------+       #----,,,,,,,,,,,,,,,,,,........ |

| .........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,----+%      %#---,,,,,,,,,,,,,,,,,,,........ |

| ..........................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+%+%@  %+%%--,,,,,,,,,,,,,,,,,,......... |

| ............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---+-+%  %----,,,,,,,,,,,,,,,,,,.......... |

| .............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%@+---,,,,,,,,,,,,,,,,,,,.......... |

| ...............................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----+%----,,,,,,,,,,,,,,,,,,........... |

| ................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,-----%+ +--,,,,,,,,,,,,,,,,,............ |

| ..................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,---++----,,,,,,,,,,,,,,,,,............. |

| ...................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,---@-----,,,,,,,,,,,,,,,,,............. |

| .....................................,,,,,,,,,,,,,,,,,,,,,,,,,,,-----,,,,,,,,,,,,,,,,,,,.............. |

|  .....................................,,,,,,,,,,,,,,,,,,,,,,,,,,--%,,,,,,,,,,,,,,,,,,,,............... |

|  .......................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................. |

|   ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................. |

|    ........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,................... |

|     .........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,.................... |

|      ..........................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,...................... |

|       ............................................,,,,,,,,,,,,,,,,,,,,,,,,,,,,........................ |

|        .............................................,,,,,,,,,,,,,,,,,,,,,,,,.......................... |

|         ................................................,,,,,,,,,,,,,,,,,............................. |

|          .....................................................,,,,.................................... |

|           ...........................................................................................  |

|            .........................................................................................   |

|             ......................................................................................     |

|              ....................................................................................      |

|                .................................................................................       |

|                 ..............................................................................         |

|                   ...........................................................................          |

|                    ........................................................................            |

+--------------------------------------------------------------------------------------------------------+

102 rows in set (0.378 sec)

Attachments

Issue Links

duplicates

MDEV-17629 Recursive CTE performance

Closed

links to

https://elephantdolphin.blogspot.com/2018/11/common-table-expressions-shocking.html

Activity

Transition	Time In Source Status	Execution Times

Alice Sherepa made transition - 2018-11-07 10:00

Open

Confirmed

33s

Igor Babaev (Inactive) made transition - 2018-11-07 14:17

Confirmed

In Progress

4h 16m

Igor Babaev (Inactive) made transition - 2018-11-08 06:27

In Progress

Closed

16h 9m

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Alice Sherepa

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2018-11-07 10:00

Updated:: 2018-11-08 12:36

Resolved:: 2018-11-08 06:27

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server