[MDEV-21424] Dead loop in Mariadb version of mysql Created: 2020-01-06  Updated: 2020-12-13  Resolved: 2020-02-11

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.5.0
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Yongheng Chen Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback


 Description   

With the following SQL queries, mysql gets stuck in MariaDB-server but not in MySQL-server and the CPU is occupied 100 %.

CREATE TEMPORARY TABLE v0 ( v1 INT UNIQUE ) ;
 
WITH RECURSIVE v0 ( v1 ) AS ( SELECT -128 UNION SELECT v1 + 33 FROM v0 ) SELECT 'x' , -1 , 54703121.000000 FROM v0 AS v3 , v0 AS v2 ,v0 NATURAL JOIN v0 AS v5 NATURAL JOIN v0 AS v4 ;



 Comments   
Comment by Elena Stepanova [ 2020-01-12 ]

Do you expect v0 inside the CTE definition to refer to the temporary table? If so, why do you expect that, is it somehow specified by standard?
I think what it's actually doing is this (no table creation is required):

WITH RECURSIVE vx ( v1 ) AS ( SELECT -128 UNION SELECT v1 + 33 FROM vx ) select * from vx;

So, it runs until max_recursive_iterations is exceeded, which is 4294967295 by default in MariaDB.

Which version of MySQL did you try and what does it return? 8.0.18 crashes for me while trying to execute it (a debug build, at least).

Generated at Thu Feb 08 09:07:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.