Re: WIP patch for LATERAL subqueries

From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Monday, August 06, 2012 3:28 AM
To: pgsql-hackers@postgreSQL.org
Subject: [HACKERS] WIP patch for LATERAL subqueries

> I've been idly amusing myself by trying to hack up support for
SQL-standard LATERAL subqueries.  I've got something 
> that turns over, more or less:

> regression=# select * from int4_tbl a, lateral (select unique1,unique2
from tenk1 b where a.f1 = unique1) x;
> f1 | unique1 | unique2 
> ----+---------+---------
>  0 |       0 |    9998
> (1 row)

> regression=# explain select * from int4_tbl a, lateral (select
unique1,unique2 from tenk1 b where a.f1 = unique1) x;
>                                    QUERY PLAN

>
----------------------------------------------------------------------------
-------
> Nested Loop  (cost=0.00..42.55 rows=5 width=12)
>   ->  Seq Scan on int4_tbl a  (cost=0.00..1.05 rows=5 width=4)
>   ->  Index Scan using tenk1_unique1 on tenk1 b  (cost=0.00..8.28 rows=1
width=8)
>         Index Cond: (a.f1 = unique1)
> (4 rows)

> but there's a good deal of work left to do, some of which could use some
discussion.

> Feature/semantics issues:

> Currently the patch only implements the syntax called out in the standard,
namely that you can put LATERAL in front of > a <derived table>, which is to
say a parenthesized sub-SELECT in FROM.  It strikes me that it might be
worth allowing 
> LATERAL with a function-in-FROM as well.  So basically
>     LATERAL func(args) <alias>
> would be an allowed abbreviation for
>    LATERAL (SELECT * FROM func(args)) <alias> Since the standard
doesn't have function-in-FROM, it has nothing to say > about whether this is
sane or not.  The argument for this is mainly that SRFs are one of the main
use-cases for LATERAL > (replacing SRF-in-the- SELECT-list usages), so we
might as well make it convenient.  Any opinions pro or con about 
> that?

I have checked Sybase also has similar syntax for functions by other keyword
APPLY. So this should be good way to specify.

> While fooling around in the planner I realized that I have no idea what
outer-level aggregates mean in a LATERAL 
> subquery, and neither does
> Postgres:
> regression=# select 1 from tenk1 a, lateral (select * from int4_tbl b
where f1 = max(a.unique1)) x;
> ERROR:  plan should not reference subplan's variable I don't see anything
prohibiting this in SQL:2008, but ordinarily > this would be taken to be an
outer-level aggregate, and surely that is not sensible in the LATERAL
subquery.  For the 
> moment it seems like a good idea to disallow it, though I am not sure
where is a convenient place to test for such 
> things.  Has anyone got a clue about whether this is well-defined, or is
it simply an oversight in the spec?

I have checked in Oracle and it gives error in such query:
SQL> select * from t1, Lateral (select * from t2 where t2.c2 = max(t1.c1)); 
select * from t1, Lateral (select * from t2 where t2.c2 = max(t1.c1))
      * 
 
ERROR at line 1: 
ORA-00934: group function is not allowed here


With Regards,
Amit Kapila.