Are you aware of CROSS APPLY? We added it a while back [1], albeit with limitations [2] and I think it does what you need. I believe Oracle also supports it.
I would be cautious about making TABLE implicitly LATERAL (as you say Oracle does) or adopting any PostgreSQL extensions. If there are many ways to do something, it potentially makes the product confusing. There is a case for adding "compatibility modes" but we should be cautious about making these the default behavior. Julian [1] https://issues.apache.org/jira/browse/CALCITE-1472 [2] https://issues.apache.org/jira/browse/CALCITE-1490 On Tue, Sep 12, 2017 at 8:11 AM, Vladimir Sitnikov <[email protected]> wrote: > I've found a discussion on the PostgreSQL dev list: > 1) https://www.postgresql.org/message-id/21317.1385046473%40sss.pgh.pa.us > Tom Lane >>SQL99 has single-argument UNNEST() but not TABLE(), so why'd they add > TABLE() later, and why'd they make it a strict subset of what UNNEST() can > do? > > 2) https://www.postgresql.org/message-id/29437.1386035763%40sss.pgh.pa.us > Noah Misch <noah(at)leadboat(dot)com> writes: >> That's how I read it, too. My hypothesis is that the standard adopted > TABLE() > to rubber-stamp Oracle's traditional name for UNNEST(). > > 3) finally PostgreSQL settled on "rows from" > https://www.postgresql.org/message-id/27951.1386300353%40sss.pgh.pa.us > That "rows from" allows left correlation (it is implicitly lateral) > > test 9.6=> select * from generate_series(1,2) a(v), rows > from(generate_series(a.v+10, a.v+11)) as b(v) > ; > v | v > ---+---- > 1 | 11 > 1 | 12 > 2 | 12 > 2 | 13 > (4 rows) > > "table functions" from within "unnest" do not work in PostgreSQL: > > test 9.6=> select * from generate_series(1,2) a(v), > unnest(generate_series(a.v+10, a.v+11)) as b(v) > ; > ERROR: function unnest(integer) does not exist > LINE 1: select * from generate_series(1,2) a(v), unnest(generate_ser... > ^ > HINT: No function matches the given name and argument types. You might > need to add explicit type casts. > > > Vladimir > > вт, 12 сент. 2017 г. в 17:33, Vladimir Sitnikov <[email protected] >>: > >> Hi, >> >> org.apache.calcite.test.JdbcTest#testUnnestArrayColumn says >> "Per SQL std, UNNEST is implicitly LATERAL" >> >> Currently lateral table functions are cumbersome to write: >> select * from main m, lateral (select ... from >> table(tableFunction(m.col))).. >> >> I know table(...) in Oracle is implicitly lateral since Oracle 9i (since >> ~2003?) even though "lateral" support was first officially published only >> in Oracle 12.1.0.1. >> >> >> I wonder if we can make "table(...)" implicitly lateral as well, so the >> syntax will become >> select * from main m, table(tableFunction(m.col)) ... >> >> >> PS. I've no idea how to check if that matches the spec or not. >> >> Vladimir >>
