Re: LATERAL

On Mon, Sep 7, 2009 at 3:43 AM, Peter Eisentraut<peter_e(at)gmx(dot)net> wrote:
> On Sun, 2009-09-06 at 23:59 -0400, Robert Haas wrote:
>> Based on reading through this discussion, it appears that LATERAL is
>> mostly a bit of syntactic sugar that requests that the parser allow
>> you to reference tables at the same query level.  Assuming that the
>> necessary executor support were present (which it's currently not),
>> it's unclear to me why one couldn't simply allow such references
>> unconditionally.
>
> Because joins can be reordered, whereas LATERAL creates a kind of
> syntactic sequence point for join reordering.  To pick up your example:
>
>> But this doesn't [work]:
>>
>> select g, h from generate_series(1,10) g, generate_series(1,g) h;
>
> You need to constrain the order of the from items in some way so the "g"
> refers to something well-defined.  That's what LATERAL does.

I don't think so. All joins constrain the join order, but none of
them except FULL JOIN constrain it completely, and this is no
exception. Consider this query:

select * from foo f, generate_series(1, 10) g, generate_series(1, g) h
WHERE f.x = g;

There are two legal join orderings here: f {g h} and {f g} h, just as
there would be for a three-way inner join:

select * from foo f, goo g, hoo h WHERE f.x = g.x and g.x = h.x;

I believe that the join-order restrictions imposed by a LATERAL SRF
are identical to those that would be imposed by an inner join against
a table with join clauses referencing the same tables used in the
inputs to the SRF. What is different is that there is only one
possible method of implementing the join, namely, for each outer row,
evaluate the SRF. We can't hash or merge, and we can't swap the inner
and outer rels, but we do still have a choice as to whether to join
against h before or after joining against f.

> You could argue that the parser could infer the references and the
> resultant join ordering restrictions automatically, but perhaps it was
> deemed that an explicit specification would be less error-prone.

Well, the irony is that our current code does already infer the
references - and then disallows them. It seems to me that if we
implement LATERAL(), we're basically going to be conditionalizing
those checks on whether the relevant subexpression has been wrapped in
LATERAL or not. Introducing a new keyword would make sense if it were
possible to write a query that is valid without LATERAL(), but means
something different with LATERAL() - but I'm currently unable to
devise such a scenario. Whether this is for want of creativity or
because none exists I'm not entirely sure. Any ideas?

...Robert