Re: BUG #17703: Recursive query early terminate when subquery result of the recursive term is NULL

On Thu, Dec 1, 2022 at 2:08 AM PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 17703
> Logged by: Jiangshan Liu
> Email address: jiangshan(dot)liu(at)tju(dot)edu(dot)cn
> PostgreSQL version: 15.1
> Operating system: Ubuntu 18.04.6 LTS
> Description:
>
> Hello! I actually found the problem stems from a more complex code, this is
> the example after minimization.
>
> -- create table and insert data
> DROP TABLE IF EXISTS "public"."table1";
> CREATE TABLE "public"."table1" (
> "id" int4 NOT NULL
> );
> INSERT INTO "public"."table1" VALUES (1);
> INSERT INTO "public"."table1" VALUES (2);
> INSERT INTO "public"."table1" VALUES (3);
>
> -- execute recursive query with potential early terminate
> WITH RECURSIVE run(__AUX_CTRL_COL_REC__,__AUX_CTRL_COL_RES__,i,r,id_table1)
> AS (
> (SELECT True, NULL::int, i_0, r_0, id_table1 FROM
> LATERAL (SELECT NULL::int) AS let_id_table1(id_table1),
> LATERAL (SELECT ( 0)::int) AS let_r_0(r_0),
> LATERAL (SELECT ( 1)::int) AS let_i_0(i_0))
> UNION ALL
> SELECT result.* FROM run,
> LATERAL (
> (SELECT if_p_0.* FROM
> LATERAL (SELECT ( i <= 10)::boolean) AS let_p_0(p_0),
> LATERAL (
> SELECT True, NULL::int, i_1, r_1, id_table1_0 FROM
> LATERAL ( SELECT id FROM table1 WHERE id = i ) AS
> let_id_table1_0(id_table1_0),
> LATERAL (SELECT ( r + 1)::int) AS let_r_1(r_1),
> LATERAL (SELECT ( i + 1)::int) AS let_i_1(i_1)
> WHERE p_0
> UNION ALL
> SELECT False, r, NULL::int, NULL::int, NULL::int
> WHERE NOT p_0 OR p_0 IS NULL
> )AS if_p_0
> )
> )AS result
> WHERE run.__AUX_CTRL_COL_REC__
> )
> SELECT run.__AUX_CTRL_COL_RES__ AS test FROM run WHERE NOT
> run.__AUX_CTRL_COL_REC__;
> -- end of example
>
> The result table of recursive query has 1 expected column named "test", but
> it is an unexpected empty table with 0 row.
> test
> ------
> (0 row)
> [...]
>
> This is an unexpected case where the recursive query seems to have
> terminated early. I wanted to figure out the details of the problem, so I
> replaced the final SELECT query of the recursive query from
> SELECT run.__AUX_CTRL_COL_RES__ AS test FROM run WHERE NOT
> run.__AUX_CTRL_COL_REC__;
> to
> SELECT * FROM run;
> and I got the intermediate results of recursive query.
>
> -- intermediate results of recursive query with potential early terminate
> __aux_ctrl_col_rec__ | __aux_ctrl_col_res__ | i | r | id_table1
> ----------------------+----------------------+---+---+-----------
> t | | 1 | 0 |
> t | | 2 | 1 | 1
> t | | 3 | 2 | 2
> t | | 4 | 3 | 3
> (4 rows)
>
> -- intermediate results of normal recursive query
> __aux_ctrl_col_rec__ | __aux_ctrl_col_res__ | i | r | id_table1
> ----------------------+----------------------+----+----+-----------
> t | | 1 | 0 |
> t | | 2 | 1 |
> t | | 3 | 2 |
> t | | 4 | 3 |
> t | | 5 | 4 |
> t | | 6 | 5 |
> t | | 7 | 6 |
> t | | 8 | 7 |
> t | | 9 | 8 |
> t | | 10 | 9 |
> t | | 11 | 10 |
> f | 10 | | |
> (12 rows)
>
> This is all the objective phenomenon I have observed. It looks like during
> the recursive query, the subquery result of
> SELECT id FROM table1 WHERE id = i
> is NULL when i=4 is encountered, leading to an unexpected early termination
> of the recursive query. Is this a bug of recursive query, and how to avoid
> this early termination?
>
>
The result of:
SELECT id FROM table1 WHERE id = 4 is the empty set, not NULL.

Inner joining against the empty set produces the empty set. A recursive
CTE ends when its recursive subquery produces an empty set.

Maybe you want to use a LEFT JOIN LATERAL subquery instead of the implicit
(comma) INNER JOIN LATERAL subquery you've written here.

David J.