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

Floris Van Nee <florisvannee@Optiver.com> writes:
> We've ran into a situation in which Postgres returns an incorrect query result. I've managed to narrow it down to the
followingreproducible example. I've encountered it on 12.4, but it reproduces on HEAD. 

I don't think this is incorrect.  I was stumped at first too, but then
I tried changing this:

> CREATE OR REPLACE FUNCTION test_internal(_a text)
> RETURNS TABLE(_a text)

to

CREATE OR REPLACE FUNCTION test_internal(_a text)
RETURNS TABLE(__a text)

and the issue went away.  After that it became pretty clear what
is happening: in

> 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$
> ;

the unqualified "_a" in the WHERE clause is taken to refer to the output
column of "test_internal(_a) t1", not the outer function's parameter
as you're supposing.  Given that interpretation, it's valid to
strength-reduce the join.

Short answer: too many "_a"s.

            regards, tom lane