0

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?

3

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
;
  • Thanks for your reply. It has solved the problem for us. – Saivishnu S R Mar 27 '19 at 6:16
  • @SaivishnuSR: if that answer solved your problem, then accept the answer, so that the question is marked as resolved – a_horse_with_no_name Jul 7 '19 at 20:00
4

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.

  • A very good point! Thanks! – Kondybas Nov 1 '20 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.