From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: zuming.jiang@inf.ethz.ch, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
Subject: Re: BUG #18187: Unexpected error: "variable not found in subplan target lists" triggered by JOIN
Date: Wed, 8 Nov 2023 22:42:10 +0700
Message-ID: <491881ef-f1c7-4a86-b5dd-dcad55a88ab4@postgrespro.ru> (raw)
In-Reply-To: <18187-831da249cbd2ff8e@postgresql.org>
References: <18187-831da249cbd2ff8e@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: a.lepikhov@postgrespro.ru, noreply@postgresql.org
Subject: Re: BUG #18187: Unexpected error: "variable not found in subplan target lists" triggered by JOIN
In-Reply-To: <491881ef-f1c7-4a86-b5dd-dcad55a88ab4@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox