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)