On Tue, Sep 22, 2020 at 6:34 PM raf <raf@raf.org> wrote:
Hi,
I just wrote a query that I didn't expect to work but I was pleasantly surprised that it did. It looked something like this:
select a.aaa, c.ccc, d.ddd1, d.ddd2 from tbla a, tblb b, tblc c, funcd(c.id) d where a.something = something and b.something = a.something and c.something = b.something
How does it know which c.id to use for the function without going all cartesian product on me?
Using the comma-separated from syntax doesn't force the planner to perform a full multi-relation cartesian join (though conceptually that is what happens) - it still only joins two relations at a time. After it joins a, b, and c it joins each row of that result with all of the rows produced by evaluating funcd(c.id).
From the SELECT docs for LATERAL:
"When a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set's values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s)."
That said, the planner would be within its rights to indeed evaluate funcd for every single row in tblc - applying c.something=b.something to the final result would still cause those rows from funcd where the attribute something for the given c.id matches the where clause filter to be excluded.
I was sure I'd done something similar once that (sensibly) didn't work, and I needed a loop to call the function in, but I might be thinking of something in an outer join's "on" clause. Does that make sense?
You probably tried it before we added LATERAL to our engine.