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