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

Floris Van Nee <florisvannee(at)Optiver(dot)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 following reproducible 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