public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andrei Lepikhov <[email protected]>
To: Andy Fan <[email protected]>
To: Alena Rybakina <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: vignesh C <[email protected]>
Cc: Richard Guo <[email protected]>
Subject: Re: A new strategy for pull-up correlated ANY_SUBLINK
Date: Mon, 1 Jul 2024 16:17:50 +0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKU4AWrwggExWQ6mg7QGT59TVL6BGwBKbVn3ZQmmOAwOF3-w=g@mail.gmail.com>
References: <CAKU4AWoZksNZ4VR-fLTdwmiR91WU8qViDBNQKNwY=7iyo+uV0w@mail.gmail.com>
	<[email protected]>
	<CAKU4AWpp8aANvwU1ivjPtF3AakFjTf1jBm_OFmPSdCqizjBQLw@mail.gmail.com>
	<[email protected]>
	<CAKU4AWrwggExWQ6mg7QGT59TVL6BGwBKbVn3ZQmmOAwOF3-w=g@mail.gmail.com>

On 10/12/23 14:52, Andy Fan wrote:
> Here the sublink can't be pulled up because of its reference to
> the  LHS of left join, the original logic is that no matter the 'b.t in ..'
> returns the true or false,  the rows in LHS will be returned.  If we
> pull it up to LHS, some rows in LHS will be filtered out, which
> breaks its original semantics.
Hi,
I spent some time trying to understand your sentence.
I mean the following case:

SELECT * FROM t1 LEFT JOIN t2
   ON t2.x IN (SELECT y FROM t3 WHERE t1.x=t3.x);

I read [1,2,3], but I am still unsure why it is impossible in the case 
of OUTER JOIN. By setting the LATERAL clause, we forbid any clauses from 
the RTE subquery to bubble up as a top-level clause and filter tuples 
from LHS, am I wrong? Does it need more research or you can show some 
case to support your opinion - why this type of transformation must be 
disallowed?

[1] https://www.postgresql.org/message-id/6531.1218473967%40sss.pgh.pa.us
[2] 
https://www.postgresql.org/message-id/BANLkTikGFtGnAaXVh5%3DntRdN%2B4w%2Br%3DNPuw%40mail.gmail.com
[3] https://www.vldb.org/conf/1992/P091.PDF

-- 
regards, Andrei Lepikhov

view thread (5+ messages)  latest in thread

reply

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: A new strategy for pull-up correlated ANY_SUBLINK
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox