Horton

Thread

Commits

  1. Avoid unhelpful internal error for incorrect recursive-WITH queries.

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

    The Post Office <noreply@postgresql.org> — 2024-07-13T09:00:00Z

    The following bug has been logged on the website:

Bug reference:      18536
Logged by:          Alexander Lakhin
Email address:      exclusion@gmail.com
PostgreSQL version: 17beta2
Operating system:   Ubuntu 22.04
Description:        

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;

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

which is seemingly not expected:
        if (cstate->selfrefcount != 1)  /* shouldn't happen */
            elog(ERROR, "missing recursive reference");

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-07-14T15:09:37Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-07-14T17:01:32Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-07-14T17:30:55Z

    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

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

    Aleksander Alekseev <aleksander@timescale.com> — 2024-07-15T12:16:09Z

    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

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

    Aleksander Alekseev <aleksander@timescale.com> — 2024-07-15T12:17:53Z

    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