Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware)

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Clause accidentally pushed down ( Possible bug in Making Vars outer-join aware)
Date: 2023-02-22 06:48:45
Message-ID: 0b819232-4b50-f245-1c7d-c8c61bf41827@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,
sqlancer benchmark raised an issue with pushing down clauses in LEFT
JOINs. Example:

DROP TABLE IF EXISTS t1,t2,t3,t4 CASCADE;
CREATE TABLE t1 AS SELECT true AS x FROM generate_series(0,1) x;
CREATE TABLE t2 AS SELECT true AS x FROM generate_series(0,1) x;
CREATE TABLE t3 AS SELECT true AS x FROM generate_series(0,1) x;
CREATE TABLE t4 AS SELECT true AS x FROM generate_series(0,1) x;
ANALYZE;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT ALL t1.x FROM t1, t2
LEFT OUTER JOIN t3
ON t3.x
LEFT OUTER JOIN t4
ON t3.x
WHERE t4.x ISNULL;

We should get zero tuples, right? But we have the explain:
Nested Loop (actual time=0.024..0.028 rows=4 loops=1)
...

REL_15_STABLE works correctly. As I remember, it could be induces by new
machinery, introduced by the 'Making Vars outer-join aware' patch.

Sorry for flood, if you are working on that issue.

--
regards,
Andrey Lepikhov
Postgres Professional

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alexander Lakhin 2023-02-22 08:00:01 Re: BUG #17803: Rule "ALSO INSERT ... SELECT ..." fails to substitute default values
Previous Message PG Bug reporting form 2023-02-22 06:00:01 BUG #17804: Assertion failed in pg_stat after fetching from pg_stat_database and swithing cache->snapshot