Re: WIP patch for LATERAL subqueries

From: pgsql-hackers-owner(at)postgresql(dot)org
[mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Monday, August 06, 2012 3:28 AM
To: pgsql-hackers(at)postgreSQL(dot)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.