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
