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
Date: Tue, 15 Jun 2021 10:05:02 -0400
Message-ID: <355906.1623765902@sss.pgh.pa.us> (raw)
In-Reply-To: <dfb0057d6a8d4b4f8a63330dd621d4b1@opammb0562.comp.optiver.com>
References: <dfb0057d6a8d4b4f8a63330dd621d4b1@opammb0562.comp.optiver.com>
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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, florisvannee@Optiver.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Postgres turns LEFT JOIN into INNER JOIN - incorrect results
In-Reply-To: <355906.1623765902@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox