Thread: Postgres turns LEFT JOIN into INNER JOIN - incorrect results

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)