Cycle detected while executing recursive query

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;

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;

Answer

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