Re: BUG #18187: Unexpected error: "variable not found in subplan target lists" triggered by JOIN

On 8/11/2023 00:04, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18187
> Logged by: Zuming Jiang
> Email address: zuming(dot)jiang(at)inf(dot)ethz(dot)ch
> PostgreSQL version: 16.0
> Operating system: Ubuntu 20.04
> Description:
>
> My fuzzer finds a bug in Postgres 17devel, which triggers an unexpected
> error.
>
> --- Set up database ---
> create table t2 (vkey int4, c9 text, primary key(vkey));
> create view t4 as select 1 as c_2, 1 as c_3;
> create view t5 as
> select
> 1 as c_0,
> case when '1' ~<=~ ref_0.c9 then 1 else 1 end as c_3
> from
> ((t2 as ref_0
> inner join t2 as ref_1
> on (ref_0.vkey = ref_1.vkey))
> right outer join t2 as ref_2
> on (ref_1.vkey = ref_2.vkey ));
> ------------------------
>
> The fuzzer generates a test case:
>
> --- Test case ---
> select
> ref_5.c_3
> from
> (((select ref_1.c_3 as c_0 from t4 as ref_1) as subq_0
> right outer join t5 as ref_5
> on (subq_0.c_0 = ref_5.c_0))
> right outer join t4 as ref_6
> on (subq_0.c_0 = ref_6.c_2));
> ------------------------
>
> --- Expected behavior ---
> The test case should not trigger any error.
>
> --- Actual behavior ---
> The test case trigger an error:
>
> ERROR: variable not found in subplan target lists
>
> --- Postgres version ---
> Github commit: 3c551ebede46194237f82062b54b92e474b5c743
> Version: PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
>
> --- Platform information ---
> Platform: Ubuntu 20.04
> Kernel: Linux 5.4.0-147-generic
>
Great,
I would like to have such fuzzer at the stage of development ;)
The issue looks like previous one, related to new SJE feature.

Reduced case:
CREATE TABLE t2 (vkey int4, c9 text, primary key(vkey));
SELECT * FROM (
SELECT CASE WHEN '1' = ref_0.c9 THEN 1 ELSE 1 END AS c_3
FROM t2 as ref_0
JOIN t2 AS ref_1
ON ref_0.vkey = ref_1.vkey
RIGHT OUTER JOIN t2 AS ref_2
ON ref_1.vkey = ref_2.vkey) AS t5
RIGHT OUTER JOIN (SELECT 1 AS c_2) AS t4
ON t4.c_2 IS NOT NULL;

The key problem lies in the 'CASE' statement.

--
regards,
Andrei Lepikhov
Postgres Professional