Re: Getting ERROR "subplan "SubPlan 1" was not initialized" in EXISTS subplan when using for list partition.

Em ter., 14 de set. de 2021 às 17:11, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escreveu:

> Zhihong Yu <zyu(at)yugabyte(dot)com> writes:
> > In the fix, isUsedSubplan is used to tell whether any given subplan is
> used.
> > Since only one subplan is used, I wonder if the array can be replaced by
> > specifying the subplan is used.
>
> That doesn't seem particularly more convenient. The point of the bool
> array is to merge the results from examination of (possibly) many
> AlternativeSubPlans.
>
Impressive quick fix, but IMHO I also think it's a bit excessive.

I would like to ask if this alternative fix (attached) would also solve the
problem or not.
Apparently, it passes the proposed test and in regress.

postgres=# create temp table exists_tbl (c1 int, c2 int, c3 int) partition
by list (c1);
CREATE TABLE
postgres=# create temp table exists_tbl_null partition of exists_tbl for
values in (null);
CREATE TABLE
postgres=# create temp table exists_tbl_def partition of exists_tbl default;
CREATE TABLE
postgres=# insert into exists_tbl select x, x/2, x+1 from
generate_series(0,10) x;
INSERT 0 11
postgres=# analyze exists_tbl;
ANALYZE
postgres=# explain (costs off)
postgres-# explain (costs off);
ERROR: syntax error at or near "explain"
LINE 2: explain (costs off);
^
postgres=# explain (costs off)
postgres-# select * from exists_tbl t1
postgres-# where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2)
or c3 < 0);
QUERY PLAN
------------------------------------------------------
Append
-> Seq Scan on exists_tbl_null t1_1
Filter: ((SubPlan 1) OR (c3 < 0))
SubPlan 1
-> Append
-> Seq Scan on exists_tbl_null t2_1
Filter: (t1_1.c1 = c2)
-> Seq Scan on exists_tbl_def t2_2
Filter: (t1_1.c1 = c2)
-> Seq Scan on exists_tbl_def t1_2
Filter: ((hashed SubPlan 2) OR (c3 < 0))
SubPlan 2
-> Append
-> Seq Scan on exists_tbl_null t2_4
-> Seq Scan on exists_tbl_def t2_5
(15 rows)

postgres=# select * from exists_tbl t1
postgres-# where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2)
or c3 < 0);
c1 | c2 | c3
----+----+----
0 | 0 | 1
1 | 0 | 2
2 | 1 | 3
3 | 1 | 4
4 | 2 | 5
5 | 2 | 6
(6 rows)

regards,
Ranier Vilela