Message-ID: <491881ef-f1c7-4a86-b5dd-dcad55a88ab4@postgrespro.ru>
Date: Wed, 8 Nov 2023 22:42:10 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18187: Unexpected error: "variable not found in subplan
 target lists" triggered by JOIN
To: zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org,
 PG Bug reporting form <noreply@postgresql.org>
References: <18187-831da249cbd2ff8e@postgresql.org>
Content-Language: en-US
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: <18187-831da249cbd2ff8e@postgresql.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/491881ef-f1c7-4a86-b5dd-dcad55a88ab4%40postgrespro.ru>
Precedence: bulk

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.jiang@inf.ethz.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