From: Tom Lane <tgl@sss.pgh.pa.us>
To: Floris Van Nee <florisvannee@Optiver.com>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: Postgres turns LEFT JOIN into INNER JOIN - incorrect results
In-reply-to: <dfb0057d6a8d4b4f8a63330dd621d4b1@opammb0562.comp.optiver.com>
References: <dfb0057d6a8d4b4f8a63330dd621d4b1@opammb0562.comp.optiver.com>
Comments: In-reply-to Floris Van Nee <florisvannee@Optiver.com>
	message dated "Tue, 15 Jun 2021 13:06:27 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <355905.1623765902.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 15 Jun 2021 10:05:02 -0400
Message-ID: <355906.1623765902@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/355906.1623765902%40sss.pgh.pa.us>
Precedence: bulk

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 following reproducible examp=
le. 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 =3D _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