You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Vladimir Sitnikov <si...@gmail.com> on 2017/09/12 14:33:01 UTC
Could table(...) be implicitly lateral?
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
Re: Could table(...) be implicitly lateral?
Posted by Julian Hyde <jh...@apache.org>.
Sounds like a good idea. I am always in favor of more helpful errors.
The SQL standard doesn't say what should happen if you make a mistake,
so we are free to be as helpful as we can!
Since CROSS APPLY is syntactic sugar for LATERAL TABLE, we should
probably give the same messages there.
Please log a JIRA case.
Julian
On Tue, Sep 12, 2017 at 12:43 PM, Vladimir Sitnikov
<si...@gmail.com> wrote:
>>Are you aware of CROSS APPLY?
>
> Ah thanks, it is reasonable.
>
> Just in case: "... unnest(tableFunction(..))" throws an error like
> "function tableFunction does not exist". It was extremely confusing for me.
> Technically speaking, unnest searches for regular functions, thus it
> ignores table functions. table(...) searches for table functions and
> ignores arrays.
>
> Does it make sense to perform an additional check in case of error, so the
> error message becomes "did you mean <<cross apply(tableFunction)>>?" in
> case someone uses "unnest(tablefunction)"?
>
>
> Vladimir
Re: Could table(...) be implicitly lateral?
Posted by Vladimir Sitnikov <si...@gmail.com>.
>Are you aware of CROSS APPLY?
Ah thanks, it is reasonable.
Just in case: "... unnest(tableFunction(..))" throws an error like
"function tableFunction does not exist". It was extremely confusing for me.
Technically speaking, unnest searches for regular functions, thus it
ignores table functions. table(...) searches for table functions and
ignores arrays.
Does it make sense to perform an additional check in case of error, so the
error message becomes "did you mean <<cross apply(tableFunction)>>?" in
case someone uses "unnest(tablefunction)"?
Vladimir
Re: Could table(...) be implicitly lateral?
Posted by Julian Hyde <jh...@apache.org>.
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
<si...@gmail.com> 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 <sitnikov.vladimir@gmail.com
>>:
>
>> 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
>>
Re: Could table(...) be implicitly lateral?
Posted by Vladimir Sitnikov <si...@gmail.com>.
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 <sitnikov.vladimir@gmail.com
>:
> 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
>