# Server hangs after the query with recursive CTE

XMLWordPrintable

#### Details

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 10.2, 10.3
• Fix Version/s:
• Component/s:
• 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), 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

Assignee:
Igor Babaev
Reporter:
Alice Sherepa