From: Floris Van Nee <florisvannee@Optiver.com>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Postgres turns LEFT JOIN into INNER JOIN - incorrect results
Date: Tue, 15 Jun 2021 13:06:27 +0000
Message-ID: <dfb0057d6a8d4b4f8a63330dd621d4b1@opammb0562.comp.optiver.com> (raw)
Hi,
We've ran into a situation in which Postgres returns an incorrect query result. I've managed to narrow it down to the following reproducible example. I've encountered it on 12.4, but it reproduces on HEAD.
set random_page_cost=1;
create table t1 as select a::text from generate_series(1, 1000) a;
create index on t1 (a);
analyze t1;
CREATE OR REPLACE FUNCTION test_internal(_a text)
RETURNS TABLE(_a text)
LANGUAGE sql
STABLE PARALLEL SAFE ROWS 1
AS $function$
SELECT
t1.a
FROM t1
WHERE t1.a = _a
$function$
;
CREATE OR REPLACE FUNCTION test(_a text)
RETURNS TABLE(a text)
LANGUAGE sql
STABLE PARALLEL SAFE ROWS 1
AS $function$
SELECT
t2.a
FROM (VALUES ('a')) t2(a)
LEFT JOIN test_internal(_a) t1 ON TRUE
WHERE t2.a = _a
$function$
;
-- this returns 0 rows (incorrect)
SELECT * FROM test('a');
-- this returns 1 row (correct)
SELECT
t2.a
FROM (VALUES ('a')) t2(a)
LEFT JOIN test_internal('a') t1 ON TRUE
WHERE t2.a = 'a';
The two SELECT queries should be identical. The first one calls a function that runs the SELECT, the second one runs the same query but outside of the function.
However, Postgres seems to forget that the query uses a LEFT JOIN. This is the plan that it chooses for both queries. In the first case, it optimizes the whole VALUES part away and just scans relation t1. In the second case, it properly chooses a Nested Loop Left Join node.
postgres=# explain
postgres-# SELECT * FROM test('a')
postgres-# ;
QUERY PLAN
-------------------------------------------------------------------------
Index Only Scan using t1_a_idx on t1 (cost=0.28..2.29 rows=1 width=32)
Index Cond: (a = 'a'::text)
(2 rows)
postgres=#
postgres=# explain
postgres-# SELECT
postgres-# t2.a
postgres-# FROM (VALUES ('a')) t2(a)
postgres-# LEFT JOIN LATERAL test_internal('a') t1 ON TRUE
postgres-# WHERE t2.a = 'a'
postgres-# ;
QUERY PLAN
------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.28..2.31 rows=1 width=32)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Index Only Scan using t1_a_idx on t1 (cost=0.28..2.29 rows=1 width=0)
Index Cond: (a = 'a'::text)
(4 rows)
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: florisvannee@Optiver.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Postgres turns LEFT JOIN into INNER JOIN - incorrect results
In-Reply-To: <dfb0057d6a8d4b4f8a63330dd621d4b1@opammb0562.comp.optiver.com>
* 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