From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Hans Buschmann <buschmann@nidsa.net>, Richard Guo <guofenglinux@gmail.com>,
        Pg Hackers <pgsql-hackers@lists.postgresql.org>,
        "Finnerty,
    Jim" <jfinnert@amazon.com>
Subject: Re: Making Vars outer-join aware
In-reply-to: <CAKFQuwbnG8J+xDaS310r0Nszk9-e5DBQzfqUN6HFdvUO17BKSw@mail.gmail.com>
References: <830269.1656693747@sss.pgh.pa.us> <CAMbWs49KZO2VvMqo+1MQfaX4cun7vhF=5RgVdpA0gjJcj77HFQ@mail.gmail.com> <1903934.1657031040@sss.pgh.pa.us> <553080.1657481916@sss.pgh.pa.us> <CAMbWs4-0cdVpgY0c2uULBCX2vPjQt3Fx3=XFZh-Mn=UWmxhG-A@mail.gmail.com> <1515100.1657633061@sss.pgh.pa.us> <CAMbWs4_va6UWcaiPSZpeRPFzpA=UKU+4zKkxqdfrcKoKA5nKWA@mail.gmail.com> <2217146.1657721363@sss.pgh.pa.us> <2055912.1659383493@sss.pgh.pa.us> <CAMbWs480igxj6hTbvD0WEnzT4XJ8hJXg71EVJO_BcQqN90MQuA@mail.gmail.com> <1188905.1660666103@sss.pgh.pa.us> <1405792.1660677844@sss.pgh.pa.us> <1538543.1660683428@sss.pgh.pa.us> <CAMbWs498oYx4pjzOsjxu0wG0D73e2+OFAqkbPotcSJDKzm-s4Q@mail.gmail.com> <2741731.1660848346@sss.pgh.pa.us> <CAMbWs4-mvPPCJ1W6iK6dD5HiNwoJdi6mZp=-7mE8N9Sh+cd0tQ@mail.gmail.com> <CAMbWs4_WNcvQGiFMLJanj3h6WMzFi6P-=bifmnuXUcg4PSXQgA@mail.gmail.com> <1035990.1667261942@sss.pgh.pa.us> <2901865.1667685211@sss.pgh.pa.us> <CAMbWs4_pH7w7voY0AejUM4RiKq-Xuf+sXmoF8PcQwzNrsagEXA@mail.gmail.com> <CAMbWs!
 4_gXgGdkkOraUPEoZD_Q3xTW7FTxfomW+fxPN2Kr-YSXg@mail.gmail.com> <4019771.1672155080@sss.pgh.pa.us> <CAMbWs4_JDDdXvEBWtTao9ZTwOGdDCU7X-=bF7yi9G1u_-b0Dfw@mail.gmail.com> <323812.1672241806@sss.pgh.pa.us> <8c3a9f69b5d44373bdd35e640e59305c@nidsa.net> <2043562.1674506286@sss.pgh.pa.us> <2291988.1674588687@sss.pgh.pa.us> <CAKFQuwbnG8J+xDaS310r0Nszk9-e5DBQzfqUN6HFdvUO17BKSw@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Tue, 24 Jan 2023 12:47:53 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2315628.1674591942.1@sss.pgh.pa.us>
Date: Tue, 24 Jan 2023 15:25:42 -0500
Message-ID: <2315629.1674591942@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2315629.1674591942%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Jan 24, 2023 at 12:31 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> select ... from t1 left join t2 on (t1.x = t2.y and t1.x = 1);
>> 
>> If we turn the generic equivclass.c logic loose on these clauses,
>> it will deduce t2.y = 1, which is good, and then apply t2.y = 1 at
>> the scan of t2, which is even better (since we might be able to turn
>> that into an indexscan qual).  However, it will also try to apply
>> t1.x = 1 at the scan of t1, and that's just wrong, because that
>> will eliminate t1 rows that should come through with null extension.

> Is there a particular comment or README where that last conclusion is
> explained so that it makes sense.

Hm?  It's a LEFT JOIN, so it must not eliminate any rows from t1.
A row that doesn't have t1.x = 1 will appear in the output with
null columns for t2 ... but it must still appear, so we cannot
filter on t1.x = 1 in the scan of t1.

			regards, tom lane