BUG #18536: Using WITH inside WITH RECURSIVE triggers a "shouldn't happen" error

PG Bug reporting form <noreply@postgresql.org> writes:

The following query:
WITH RECURSIVE t(n) AS (
WITH t1 AS (SELECT 1 FROM t) SELECT 1
UNION
SELECT 1 FROM t1)
SELECT * FROM t;

That should throw an error, certainly: it's not a valid recursive
structure. (Since the inner WITH clause spans the whole
"SELECT 1 UNION SELECT 1 FROM t1" structure, we don't have a top-
level UNION anymore.) But it shouldn't throw this error:

ERROR: XX000: missing recursive reference
LOCATION: checkWellFormedRecursion, parse_cte.c:896

We do get the right behaviors for WITHs that are down inside one
side or the other of the UNION:

WITH RECURSIVE t(n) AS (
(WITH t1 AS (SELECT 1 FROM t) SELECT 1 FROM t1)
UNION
SELECT 1)
SELECT * FROM t;
ERROR: recursive reference to query "t" must not appear within its non-recursive term
LINE 2: (WITH t1 AS (SELECT 1 FROM t) SELECT 1 FROM t1)
^

WITH RECURSIVE t(n) AS (
SELECT 1
UNION
(WITH t1 AS (SELECT 1 FROM t) SELECT 1 FROM t1))
SELECT * FROM t;
n
---
1
(1 row)

I think the case you show should be throwing

ERROR: recursive query "t" does not have the form non-recursive-term UNION [ALL] recursive-term

Will look closer later. Thanks for the report.

regards, tom lane

I wrote:

I think the case you show should be throwing
ERROR: recursive query "t" does not have the form non-recursive-term UNION [ALL] recursive-term

Hmm, that is probably too strong: it will break some queries we've
historically accepted. What we need is just to forbid self-references
within the WITH clause. The code actually does that already, it's
just doing it too late; so we can fix this with a simple re-ordering
of the error checks, as attached.

regards, tom lane

I wrote:

Hmm, that is probably too strong: it will break some queries we've
historically accepted. What we need is just to forbid self-references
within the WITH clause. The code actually does that already, it's
just doing it too late; so we can fix this with a simple re-ordering
of the error checks, as attached.

Oh ...

regression=# WITH RECURSIVE x(n) AS (
select 0 union select 1 order by (select n from x)) select * from x;
ERROR: missing recursive reference

We have to move *all* of those subsidiary-clause checks to before
the tests of the UNION proper.

regards, tom lane

Hi,

triggers an error:
ERROR: XX000: missing recursive reference
LOCATION: checkWellFormedRecursion, parse_cte.c:896

FWIW I couldn't reproduce the reported error on REL_17_STABLE
(b8bf76cbde39). The error I got seems reasonable:

```
46087 (master) =# WITH RECURSIVE t(n) AS (
WITH t1 AS (SELECT 1 FROM t) SELECT 1
UNION
SELECT 1 FROM t1)
SELECT * FROM t;
ERROR: recursive reference to query "t" must not appear within a subquery
LINE 2: WITH t1 AS (SELECT 1 FROM t) SELECT 1
^
```

We should add regression tests though, as v2 does.

--
Best regards,
Aleksander Alekseev

Hi,

triggers an error:
ERROR: XX000: missing recursive reference
LOCATION: checkWellFormedRecursion, parse_cte.c:896

FWIW I couldn't reproduce the reported error on REL_17_STABLE
(b8bf76cbde39). The error I got seems reasonable:

```
46087 (master) =# WITH RECURSIVE t(n) AS (
WITH t1 AS (SELECT 1 FROM t) SELECT 1
UNION
SELECT 1 FROM t1)
SELECT * FROM t;
ERROR: recursive reference to query "t" must not appear within a subquery
LINE 2: WITH t1 AS (SELECT 1 FROM t) SELECT 1
^
```

We should add regression tests though, as v2 does.

Oops. That's because Tom pushed this already (cf588e10f664). Sorry for
the noise.

--
Best regards,
Aleksander Alekseev