Thread: I'm surprised that this worked
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? Maybe it
makes no sense for such a parameterised function to be
part of a cartesian product. Actually, that function
returns a setof record but only ever a single record.
That might be relevant.
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?
Even more impressed with Postgresql than usual. :-)
cheers,
raf
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.
David J.