sql: unexpected infinite loop in recursive CTE

[jordanlewis]

The following example from pg_regress infinite loops but shouldn't:

CREATE TABLE department (
	id INTEGER PRIMARY KEY,  -- department ID
	parent_department INTEGER REFERENCES department, -- upper department ID
	name TEXT -- department name
);

INSERT INTO department VALUES (0, NULL, 'ROOT');
INSERT INTO department VALUES (1, 0, 'A');
INSERT INTO department VALUES (2, 1, 'B');
INSERT INTO department VALUES (3, 2, 'C');
INSERT INTO department VALUES (4, 2, 'D');
INSERT INTO department VALUES (5, 0, 'E');
INSERT INTO department VALUES (6, 4, 'F');
INSERT INTO department VALUES (7, 5, 'G');

with recursive q as (
      select * from department
    union all
      (with recursive x as (
           select * from department
         union all
           (select * from q union all select * from x)
        )
       select * from x)
    )
select * from q limit 32;

In Postgres, this instantly returns the result:

jordan=# with recursive q as (
jordan(#       select * from department
jordan(#     union all
jordan(#       (with recursive x as (
jordan(#            select * from department
jordan(#          union all
jordan(#            (select * from q union all select * from x)
jordan(#         )
jordan(#        select * from x)
jordan(#     )
jordan-# select * from q limit 32;
 id | parent_department | name
----+-------------------+------
  0 |                   | ROOT
  1 |                 0 | A
  2 |                 1 | B
  3 |                 2 | C
  4 |                 2 | D
  5 |                 0 | E
  6 |                 4 | F
  7 |                 5 | G
  0 |                   | ROOT
  1 |                 0 | A
  2 |                 1 | B
  3 |                 2 | C
  4 |                 2 | D
  5 |                 0 | E
  6 |                 4 | F
  7 |                 5 | G
  0 |                   | ROOT
  1 |                 0 | A
  2 |                 1 | B
  3 |                 2 | C
  4 |                 2 | D
  5 |                 0 | E
  6 |                 4 | F
  7 |                 5 | G
  0 |                   | ROOT
  1 |                 0 | A
  2 |                 1 | B
  3 |                 2 | C
  4 |                 2 | D
  5 |                 0 | E
  6 |                 4 | F
  7 |                 5 | G
(32 rows)

Jira issue: CRDB-22288