# Server hangs after the query with recursive CTE

XMLWordPrintable

#### Details

• Bug
• Status: Closed
• Critical
• Resolution: Fixed
• 10.2, 10.3
• 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),` ` 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)` ``` ```

#### People

Igor Babaev
Alice Sherepa