Please note while @Kondybas' answer will produce expected results on correct data sets it will NOT restrict the number of iterations.
All rows will still be read but only 2 of them will be returned. That means:
- Drop in performance
- You will NOT avoid infinite loops that way!
If there is a loop in the tree of
id links in the table data due to some mistake such query can last forever (which might event corrupt the entire server by flooding the disk with endless temporary result data - checked in MariaDB 10.3.15.)
So you have to put a restricting condition inside the
WITH clause as follows:
WITH RECURSIVE cte AS
( SELECT 1 AS lvl -- UDV initialization
, A.AFI -- initial portion
FROM M AS A
WHERE A.pid = 1
SELECT cte.lvl + 1 AS lvl -- UDV increment on each iteration
, z.AFI -- recursive portion
FROM cte -- referring to the already gathered recursion
JOIN M AS z ON z.pid = cte.id
WHERE cte.lvl < 2 -- *** the correct condition ***
P.S. I guess it is also a good idea to set max_recursive_iterations to some reachable value like 10000 to prevent MariaDB from getting unresponsive.