Cycle detected while executing recursive query

user_16626008 0
Viewed 1066

I am using CTE recursive query to get below output but don't know why it is throwing "cycle detected while executing recursive WITH query". Can anyone please tell me where is wrong with my query?

my query:

WITH
cte (order_id,
     product_id,
     quantity,
     cnt)
AS
    (SELECT order_id,
            product_id,
            1 as quantity,
            1 as cnt
       FROM order_tbl2        
     UNION ALL
     SELECT a.order_id,
            a.product_id,
            b.quantity,
            b.cnt + 1
       FROM order_tbl2 A INNER JOIN cte b ON a.product_id = b.product_id
      WHERE  b.cnt + 1 < a.quantity)

SELECT order_id, product_id, quantity FROM cte;

enter image description here

table/data script:

CREATE TABLE ORDER_TBL2
(
ORDER_PAY   DATE,
ORDER_ID    VARCHAR2(10 BYTE),
PRODUCT_ID  VARCHAR2(10 BYTE),
QUANTITY    NUMBER(5),
PRICE       NUMBER(5)
);

Insert into ORDER_TBL2
 (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
Values
 (TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD1', 'PROD1', 5, 5);
Insert into ORDER_TBL2
 (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
Values
 (TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD2', 'PROD2', 2, 10);
Insert into ORDER_TBL2
 (ORDER_PAY, ORDER_ID, PRODUCT_ID, QUANTITY, PRICE)
Values
 (TO_DATE('5/1/2015', 'MM/DD/YYYY'), 'ORD3', 'PROD3', 3, 25);

COMMIT;
recursion common-table-expression oracle sql
2 Answers

In the recursive member you are currently only joining on a.product_id = b.product_id, rather than a.order_id = b.order_id AND a.product_id = b.product_id; which doesn't directly matter here but would if different orders included the same products, which is likely in the real world.

Your data and query don't actually seem to have cycle though. You appear to be tripping over what seems to be a bug with ANSI joins; adding a cycle clause doesn't reveal any cycling rows, as expected - and makes it work!; and it works with old-style joins:

WITH
    cte (order_id,
         product_id,
         quantity,
         cnt)
    AS
        (SELECT order_id,
                product_id,
                1 as quantity,
                1 as cnt
           FROM order_tbl2        
         UNION ALL
         SELECT a.order_id,
                a.product_id,
                b.quantity,
                b.cnt + 1
           FROM order_tbl2 A, cte b
          WHERE b.cnt + 1 < a.quantity
            AND a.order_id = b.order_id
            AND a.product_id = b.product_id
            )
SELECT order_id, product_id, quantity
  FROM cte;

db<>fiddle

You don't need to join at all though; you can do:

WITH
    cte (order_id,
         product_id,
         quantity,
         cnt)
    AS
        (SELECT order_id,
                product_id,
                quantity,
                1 as cnt
           FROM order_tbl2        
         UNION ALL
         SELECT b.order_id,
                b.product_id,
                b.quantity,
                b.cnt + 1
           FROM cte b
          WHERE  b.cnt < b.quantity)
SELECT order_id, product_id, 1 as quantity
  FROM cte;

which assigns the fixed 1 quantity in the final select, or:

WITH
    cte (order_id,
         product_id,
         real_quantity,
         quantity,
         cnt)
    AS
        (SELECT order_id,
                product_id,
                quantity as real_quantity,
                1 as quantity,
                1 as cnt
           FROM order_tbl2        
         UNION ALL
         SELECT b.order_id,
                b.product_id,
                b.real_quantity,
                b.quantity,
                b.cnt + 1
           FROM cte b
          WHERE  b.cnt < b.real_quantity)
SELECT order_id, product_id, quantity
  FROM cte;

which assigns it inside and needs to track the original quantity as a new alias.

For both of those I've removed the + 1 from the quantity comparison, as that was making it stop too early; with an order by added, they both get:

ORDER_ID PRODUCT_ID QUANTITY
ORD1 PROD1 1
ORD1 PROD1 1
ORD1 PROD1 1
ORD1 PROD1 1
ORD1 PROD1 1
ORD2 PROD2 1
ORD2 PROD2 1
ORD3 PROD3 1
ORD3 PROD3 1
ORD3 PROD3 1

db<>fiddle

user_2663040

I think you are trying to expand the rows. If so, no join is needed:

WITH cte (order_id, product_id, quantity, cnt) AS (
      SELECT order_id, product_id, quantity, 1 as cnt
      FROM order_tbl2        
      UNION ALL
      SELECT order_id, product_id, quantity, cnt + 1
      FROM cte
      WHERE cnt < a.quantity
     )
SELECT *
FROM cte;
user_11440350
Related