Limiting Common Table Recursion

Question

I am trying to migrate from Oracle to MariaDB. At the moment, we are facing an issue with the following Oracle code.

 SELECT id,MK, pid,AFI 
   FROM M A 
  WHERE LEVEL <= 2 -- limiting iterations here
  START WITH pid = 1 
CONNECT BY Pid = PRIOR id;

We have used recursive common expression table but we are unable to restrict the number of iterations to 2 (LEVEL <= 2). We don't want to set the system variables like cte_max_recursion_depth. How else can we limit recursion depth?

Answer 1

The mysql/mariadb syntax should be the next:

WITH RECURSIVE cte AS
  ( SELECT 1 AS lvl -- UDV initialization
         , A.id
         , A.MK
         , A.pid
         , A.AFI -- initial portion
      FROM M AS A 
     WHERE A.pid = 1

     UNION

    SELECT cte.lvl + 1 AS lvl -- UDV increment on each iteration
         , z.id
         , z.MK
         , z.pid
         , z.AFI -- recursive portion
      FROM cte -- referring to the already gathered recursion
      JOIN M AS z ON z.pid = cte.id
      WHERE lvl <= 2  -- Supa's amendment
  )
SELECT *
  FROM cte 
-- WHERE lvl <= 2 -- wrong place
;

Answer 2

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:

1. Drop in performance
2. You will NOT avoid infinite loops that way!

If there is a loop in the tree of pid-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.id
         , A.MK
         , A.pid
         , A.AFI -- initial portion
      FROM M AS A 
     WHERE A.pid = 1

     UNION

    SELECT cte.lvl + 1 AS lvl -- UDV increment on each iteration
         , z.id
         , z.MK
         , z.pid
         , 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 ***
  )
SELECT *
  FROM cte 
;

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.