You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by Jark Wu <im...@gmail.com> on 2020/10/29 09:25:52 UTC

[DISCUSS] Support Oracle Polymorphic Table Function Syntax

Hi all,

It's great to see Calcite already supports PTF syntax and support the
out-of-box new window syntax.

SELECT *
FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES));

However, some people from the Flink community think that the TABLE()
keyword is a little verbose for users [5].
I have seen the discussion in the previous mailing list [1], and know the
TABLE() keyword is mandatory in SQL standard paper.

But it seems that other databases are not following the standard, I find
that Polymorphic Table Function syntax
 in Oracle is more concise without the TABLE() keywords [2][3], e.g.

SELECT *
FROM skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))

Besides, SQL Server also seems to support the non TABLE() syntax for
table-valued functions [4].
If we can support the Oracle syntax, it would be more consistent with
existing TUMBLE functions
and hence easier to be picked up by users.

The new window TVF syntax can be:

SELECT window_start, count(*)
FROM TUMBLE(Bid, COLUMNS(bidtime), INTERVAL '10' MINUTES))
GROUP BY window_start;

Which is more similar to the existing group window functions:

SELECT TUMBLE_START(bidtime, INTERVAL '10' MINUTES), count(*)
FROM Bid
GROUP BY TUMBLE(bidtime, INTERVAL '10' MINUTES);

I am fairly inexperienced with the parsing and validation logic in Calcite,
so I don't know whether the new syntax can be supported in Calcite.

What do you think?

Best,
Jark

[1]:
https://lists.apache.org/x/thread.html/4a91632b1c780ef9d67311f90fce626582faae7d30a134a768c3d324@%3Cdev.calcite.apache.org%3E
[2]: https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
[3]:
https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-optimization-and-tuning.html#GUID-F8E13DDD-710D-4626-824E-B344849C5AFE
[4]:
https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/
[5]:
http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-145-Support-SQL-windowing-table-valued-function-tp45269p45665.html

Re: [DISCUSS] Support Oracle Polymorphic Table Function Syntax

Posted by Jark Wu <im...@gmail.com>.

I agree with Danny.

I think we can use the parentheses to distinguish table vs. table-function
and use different namespaces for validation?

Bets,
Jark

On Tue, 3 Nov 2020 at 15:25, Danny Chan <da...@apache.org> wrote:

> In current codebase, we actually never allows syntax like
>
> SELECT *
>   FROM TT
>
> because of 2 reasons:
> 1. The parser does not allow table function calls without parenthesis
> 2. SqlConformace.allowNiladicParentheses default returns false
>
> In TableFunctionTest, I try to register the same name table and table
> function in the same schema, and there is no ambiguity.
> So I guess there is no problem here ?
>
>
> Julian Hyde <jh...@apache.org> 于2020年11月3日周二 上午3:02写道：
>
> > Something like that. I guess if TT is a function with zero parameters
> > and also a table, then does TT in the following refer to the function
> > or the table?
> >
> >   SELECT *
> >   FROM TT
> >
> > Note that the TT function doesn't even have to be a table function.
> > Table functions are functions, and so are in the same namespace.
> >
> > Furthermore, we look up tables from the current schema, whereas we
> > look up functions (and table functions) from the path.
> >
> > Julian
> >
> > On Mon, Nov 2, 2020 at 3:55 AM Danny Chan <da...@apache.org> wrote:
> > >
> > > Thanks Julian ~
> > >
> > > > Possibly, but I'd be cautious, because the semantics become ambiguous
> > > if there are name clashes.
> > >
> > > Assumes there is a table named TT and a table function named
> TT(param_a,
> > > param_b), they are under the same namespace, do you mean
> > >
> > > "FROM TT(param_a, param_b)" or "FROM TT(TABLE TT, param_b)"
> > >
> > > become ambiguous ?
> > >
> > >
> > > Julian Hyde <jh...@apache.org> 于2020年10月31日周六 上午3:19写道：
> > >
> > > > > CALCITE-1490 suggests only add to SQL server, but as Oracle,
> > Snowflake
> > > > and
> > > > > BigQuery seems all support the simplified syntax, it might be
> enough
> > to
> > > > > justify adding this support to default syntax?
> > > >
> > > > Possibly, but I'd be cautious, because the semantics become ambiguous
> > > > if there are name clashes.
> > > >
> > > > We should document what should be the behavior if there are name
> > > > clashes, and also research what SQL Server, Oracle, Snowflake and
> > > > BigQuery do.
> > > >
> > > > On Fri, Oct 30, 2020 at 10:55 AM Rui Wang <am...@apache.org>
> > wrote:
> > > > >
> > > > > >Are we planning to support it as a default syntax or as a dialect
> ?
> > Say,
> > > > > >maybe Oracle.
> > > > >
> > > > > CALCITE-1490 suggests only add to SQL server, but as Oracle,
> > Snowflake
> > > > and
> > > > > BigQuery seems all support the simplified syntax, it might be
> enough
> > to
> > > > > justify adding this support to default syntax?
> > > > >
> > > > > >Another idea is that maybe we can use the parentheses to
> distinguish
> > > > > >whether
> > > > > >this is a table or table-function and use different namespace for
> > > > > >validation?
> > > > >
> > > > > The idea sounds good. The implementation might become complicated.
> > > > > Namespaces are only different when looking up a table function or a
> > > > table,
> > > > > others have to be the same.
> > > > >
> > > > >
> > > > > -Rui
> > > > >
> > > > >
> > > > > On Thu, Oct 29, 2020 at 9:11 PM Jark Wu <im...@gmail.com> wrote:
> > > > >
> > > > > > Hi all,
> > > > > >
> > > > > > Yes, there are two separate discussions here.
> > > > > > 1) omit TABLE() keyword for table function calls, i.e.
> CALCITE-1490
> > > > > > 2) omit TABLE keyword for the table parameter in TVF.
> > > > > >
> > > > > > Let's focus on the first discussion. If I understand correctly,
> the
> > > > problem
> > > > > > is
> > > > > >  the namespace conflict of table and table-function.
> > > > > >
> > > > > > I have tested table-function features in SQL Server (as following
> > > > shows),
> > > > > > it seems that the
> > > > > > parentheses are required to invoke a parameterless
> table-function.
> > > > > > There is a similar question in Stackoverflow[1].
> > > > > >
> > > > > > > CREATE FUNCTION udfProductInYear ()
> > > > > >   RETURNS TABLE
> > > > > >   AS
> > > > > >   RETURN
> > > > > >     SELECT * FROM [dbo].[TEST];
> > > > > >
> > > > > > > select * from udfProductInYear;
> > > > > > Parameters were not supplied for the function 'udfProductInYear'.
> > > > > >
> > > > > > > select * from udfProductInYear();
> > > > > > This works.
> > > > > >
> > > > > > > create table udfProductInYear(a int, b VARCHAR);
> > > > > > There is already an object named 'udfProductInYear' in the
> > database.
> > > > > >
> > > > > > From the above error messages, we can see that SQL Server shares
> > the
> > > > same
> > > > > > namespace for table and table-function.
> > > > > > So I think we can do the same thing but only enabled via a new
> > method
> > > > in
> > > > > > SqlConformance.
> > > > > >
> > > > > > Another idea is that maybe we can use the parentheses to
> > distinguish
> > > > > > whether
> > > > > > this is a table or table-function and use different namespace for
> > > > > > validation?
> > > > > >
> > > > > > Best,
> > > > > > Jark
> > > > > >
> > > > > > [1]: https://stackoverflow.com/a/21022682/4915129
> > > > > >
> > > > > >
> > > > > > On Fri, 30 Oct 2020 at 09:48, Danny Chan <da...@apache.org>
> > wrote:
> > > > > >
> > > > > > > > Let's not use TUMBLE (or HOP, or SESSION) as the main
> example.
> > It
> > > > is
> > > > > > > somewhat built-in (i.e. has special treatment in the code).
> Let's
> > > > work
> > > > > > > in terms of, say, the RAMP user-defined function. It is used in
> > > > > > > several tests [1].
> > > > > > >
> > > > > > > We may need to support all the user defined table functions
> with
> > > > TABLE
> > > > > > > keyword left out.
> > > > > > >
> > > > > > > Are we planning to support it as a default syntax or as a
> > dialect ?
> > > > Say,
> > > > > > > maybe Oracle.
> > > > > > >
> > > > > > > Julian Hyde <jh...@apache.org> 于2020年10月30日周五 上午5:11写道：
> > > > > > >
> > > > > > > > I think we can do them separately. And I think we should do
> the
> > > > TABLE
> > > > > > > > first. The biggest problem is with namespaces - if you omit
> > TABLE,
> > > > you
> > > > > > > > have to deal with the possibility that there is a table (or
> > view)
> > > > > > > > called FOO and also a parameterless table function called
> FOO.
> > Not
> > > > > > > > sure how Oracle and SQL Server resolve this.
> > > > > > > >
> > > > > > > > Let's not use TUMBLE (or HOP, or SESSION) as the main
> example.
> > It
> > > > is
> > > > > > > > somewhat built-in (i.e. has special treatment in the code).
> > Let's
> > > > work
> > > > > > > > in terms of, say, the RAMP user-defined function. It is used
> in
> > > > > > > > several tests [1].
> > > > > > > >
> > > > > > > > Julian
> > > > > > > >
> > > > > > > > [1]
> > > > > > > >
> > > > > > >
> > > > > >
> > > >
> >
> https://github.com/apache/calcite/blob/ffc1e3b05e7f920d95c48f7c75fd48372684b8e7/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java#L361
> > > > > > > >
> > > > > > > > On Thu, Oct 29, 2020 at 1:53 PM Rui Wang <
> amaliujia@apache.org
> > >
> > > > wrote:
> > > > > > > > >
> > > > > > > > > In terms of SQL grammar to support omitting TABLE, there
> are
> > > > actually
> > > > > > > two
> > > > > > > > > changes for
> > > > > > > > >
> > > > > > > > > SELECT *
> > > > > > > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL
> > '10'
> > > > > > > > MINUTES));
> > > > > > > > >
> > > > > > > > > You can support omitting the TABLE after the FROM, which
> > makes
> > > > the
> > > > > > > query
> > > > > > > > > become:
> > > > > > > > > SELECT *
> > > > > > > > > FROM TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > > > MINUTES);
> > > > > > > > >
> > > > > > > > > Another is omitting TABLE from the parameter, which is
> > > > > > > > > SELECT *
> > > > > > > > > FROM TUMBLE(Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> MINUTES);
> > > > > > > > >
> > > > > > > > > Do we want to achieve both or just one of these? If for one
> > the
> > > > > > grammar
> > > > > > > > is
> > > > > > > > > too complicated to change but for another the grammar is
> > easier
> > > > to be
> > > > > > > > > changed, are we ok to only have one keyword omitted?
> > > > > > > > >
> > > > > > > > > -Rui
> > > > > > > > >
> > > > > > > > > On Thu, Oct 29, 2020 at 11:28 AM Julian Hyde <
> > jhyde@apache.org>
> > > > > > wrote:
> > > > > > > > >
> > > > > > > > > > Can we drop the word "polymorphic" from the discussion?
> > > > Polymorphic
> > > > > > > > > > table functions are a valid ask, but can be a separate
> > > > discussion.
> > > > > > > > > > This is about calling table functions without the TABLE
> > > > keyword,
> > > > > > > > > > right?
> > > > > > > > > >
> > > > > > > > > > Which is what I said to you four years ago:
> > > > > > > > > >
> > > > > >
> > https://issues.apache.org/jira/browse/CALCITE-1472#comment-15664799
> > > > > > > > > >
> > > > > > > > > > In other words: let's fix
> > > > > > > > > > https://issues.apache.org/jira/browse/CALCITE-1490.
> > > > > > > > > >
> > > > > > > > > > Julian
> > > > > > > > > >
> > > > > > > > > > On Thu, Oct 29, 2020 at 2:26 AM Jark Wu <
> imjark@gmail.com>
> > > > wrote:
> > > > > > > > > > >
> > > > > > > > > > > Hi all,
> > > > > > > > > > >
> > > > > > > > > > > It's great to see Calcite already supports PTF syntax
> and
> > > > support
> > > > > > > the
> > > > > > > > > > > out-of-box new window syntax.
> > > > > > > > > > >
> > > > > > > > > > > SELECT *
> > > > > > > > > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime),
> > INTERVAL
> > > > '10'
> > > > > > > > > > MINUTES));
> > > > > > > > > > >
> > > > > > > > > > > However, some people from the Flink community think
> that
> > the
> > > > > > > TABLE()
> > > > > > > > > > > keyword is a little verbose for users [5].
> > > > > > > > > > > I have seen the discussion in the previous mailing list
> > [1],
> > > > and
> > > > > > > > know the
> > > > > > > > > > > TABLE() keyword is mandatory in SQL standard paper.
> > > > > > > > > > >
> > > > > > > > > > > But it seems that other databases are not following the
> > > > > > standard, I
> > > > > > > > find
> > > > > > > > > > > that Polymorphic Table Function syntax
> > > > > > > > > > >  in Oracle is more concise without the TABLE() keywords
> > > > [2][3],
> > > > > > > e.g.
> > > > > > > > > > >
> > > > > > > > > > > SELECT *
> > > > > > > > > > > FROM skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
> > > > > > > > > > >
> > > > > > > > > > > Besides, SQL Server also seems to support the non
> TABLE()
> > > > syntax
> > > > > > > for
> > > > > > > > > > > table-valued functions [4].
> > > > > > > > > > > If we can support the Oracle syntax, it would be more
> > > > consistent
> > > > > > > with
> > > > > > > > > > > existing TUMBLE functions
> > > > > > > > > > > and hence easier to be picked up by users.
> > > > > > > > > > >
> > > > > > > > > > > The new window TVF syntax can be:
> > > > > > > > > > >
> > > > > > > > > > > SELECT window_start, count(*)
> > > > > > > > > > > FROM TUMBLE(Bid, COLUMNS(bidtime), INTERVAL '10'
> > MINUTES))
> > > > > > > > > > > GROUP BY window_start;
> > > > > > > > > > >
> > > > > > > > > > > Which is more similar to the existing group window
> > functions:
> > > > > > > > > > >
> > > > > > > > > > > SELECT TUMBLE_START(bidtime, INTERVAL '10' MINUTES),
> > count(*)
> > > > > > > > > > > FROM Bid
> > > > > > > > > > > GROUP BY TUMBLE(bidtime, INTERVAL '10' MINUTES);
> > > > > > > > > > >
> > > > > > > > > > > I am fairly inexperienced with the parsing and
> validation
> > > > logic
> > > > > > in
> > > > > > > > > > Calcite,
> > > > > > > > > > > so I don't know whether the new syntax can be supported
> > in
> > > > > > Calcite.
> > > > > > > > > > >
> > > > > > > > > > > What do you think?
> > > > > > > > > > >
> > > > > > > > > > > Best,
> > > > > > > > > > > Jark
> > > > > > > > > > >
> > > > > > > > > > > [1]:
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > >
> >
> https://lists.apache.org/x/thread.html/4a91632b1c780ef9d67311f90fce626582faae7d30a134a768c3d324@%3Cdev.calcite.apache.org%3E
> > > > > > > > > > > [2]:
> > > > > > > > > >
> > > > > >
> > https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
> > > > > > > > > > > [3]:
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > >
> >
> https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-optimization-and-tuning.html#GUID-F8E13DDD-710D-4626-824E-B344849C5AFE
> > > > > > > > > > > [4]:
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > >
> >
> https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/
> > > > > > > > > > > [5]:
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > >
> >
> http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-145-Support-SQL-windowing-table-valued-function-tp45269p45665.html
> > > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > >
> >
>

Re: [DISCUSS] Support Oracle Polymorphic Table Function Syntax

Posted by Danny Chan <da...@apache.org>.

In current codebase, we actually never allows syntax like

SELECT *
  FROM TT

because of 2 reasons:
1. The parser does not allow table function calls without parenthesis
2. SqlConformace.allowNiladicParentheses default returns false

In TableFunctionTest, I try to register the same name table and table
function in the same schema, and there is no ambiguity.
So I guess there is no problem here ?


Julian Hyde <jh...@apache.org> 于2020年11月3日周二 上午3:02写道：

> Something like that. I guess if TT is a function with zero parameters
> and also a table, then does TT in the following refer to the function
> or the table?
>
>   SELECT *
>   FROM TT
>
> Note that the TT function doesn't even have to be a table function.
> Table functions are functions, and so are in the same namespace.
>
> Furthermore, we look up tables from the current schema, whereas we
> look up functions (and table functions) from the path.
>
> Julian
>
> On Mon, Nov 2, 2020 at 3:55 AM Danny Chan <da...@apache.org> wrote:
> >
> > Thanks Julian ~
> >
> > > Possibly, but I'd be cautious, because the semantics become ambiguous
> > if there are name clashes.
> >
> > Assumes there is a table named TT and a table function named TT(param_a,
> > param_b), they are under the same namespace, do you mean
> >
> > "FROM TT(param_a, param_b)" or "FROM TT(TABLE TT, param_b)"
> >
> > become ambiguous ?
> >
> >
> > Julian Hyde <jh...@apache.org> 于2020年10月31日周六 上午3:19写道：
> >
> > > > CALCITE-1490 suggests only add to SQL server, but as Oracle,
> Snowflake
> > > and
> > > > BigQuery seems all support the simplified syntax, it might be enough
> to
> > > > justify adding this support to default syntax?
> > >
> > > Possibly, but I'd be cautious, because the semantics become ambiguous
> > > if there are name clashes.
> > >
> > > We should document what should be the behavior if there are name
> > > clashes, and also research what SQL Server, Oracle, Snowflake and
> > > BigQuery do.
> > >
> > > On Fri, Oct 30, 2020 at 10:55 AM Rui Wang <am...@apache.org>
> wrote:
> > > >
> > > > >Are we planning to support it as a default syntax or as a dialect ?
> Say,
> > > > >maybe Oracle.
> > > >
> > > > CALCITE-1490 suggests only add to SQL server, but as Oracle,
> Snowflake
> > > and
> > > > BigQuery seems all support the simplified syntax, it might be enough
> to
> > > > justify adding this support to default syntax?
> > > >
> > > > >Another idea is that maybe we can use the parentheses to distinguish
> > > > >whether
> > > > >this is a table or table-function and use different namespace for
> > > > >validation?
> > > >
> > > > The idea sounds good. The implementation might become complicated.
> > > > Namespaces are only different when looking up a table function or a
> > > table,
> > > > others have to be the same.
> > > >
> > > >
> > > > -Rui
> > > >
> > > >
> > > > On Thu, Oct 29, 2020 at 9:11 PM Jark Wu <im...@gmail.com> wrote:
> > > >
> > > > > Hi all,
> > > > >
> > > > > Yes, there are two separate discussions here.
> > > > > 1) omit TABLE() keyword for table function calls, i.e. CALCITE-1490
> > > > > 2) omit TABLE keyword for the table parameter in TVF.
> > > > >
> > > > > Let's focus on the first discussion. If I understand correctly, the
> > > problem
> > > > > is
> > > > >  the namespace conflict of table and table-function.
> > > > >
> > > > > I have tested table-function features in SQL Server (as following
> > > shows),
> > > > > it seems that the
> > > > > parentheses are required to invoke a parameterless table-function.
> > > > > There is a similar question in Stackoverflow[1].
> > > > >
> > > > > > CREATE FUNCTION udfProductInYear ()
> > > > >   RETURNS TABLE
> > > > >   AS
> > > > >   RETURN
> > > > >     SELECT * FROM [dbo].[TEST];
> > > > >
> > > > > > select * from udfProductInYear;
> > > > > Parameters were not supplied for the function 'udfProductInYear'.
> > > > >
> > > > > > select * from udfProductInYear();
> > > > > This works.
> > > > >
> > > > > > create table udfProductInYear(a int, b VARCHAR);
> > > > > There is already an object named 'udfProductInYear' in the
> database.
> > > > >
> > > > > From the above error messages, we can see that SQL Server shares
> the
> > > same
> > > > > namespace for table and table-function.
> > > > > So I think we can do the same thing but only enabled via a new
> method
> > > in
> > > > > SqlConformance.
> > > > >
> > > > > Another idea is that maybe we can use the parentheses to
> distinguish
> > > > > whether
> > > > > this is a table or table-function and use different namespace for
> > > > > validation?
> > > > >
> > > > > Best,
> > > > > Jark
> > > > >
> > > > > [1]: https://stackoverflow.com/a/21022682/4915129
> > > > >
> > > > >
> > > > > On Fri, 30 Oct 2020 at 09:48, Danny Chan <da...@apache.org>
> wrote:
> > > > >
> > > > > > > Let's not use TUMBLE (or HOP, or SESSION) as the main example.
> It
> > > is
> > > > > > somewhat built-in (i.e. has special treatment in the code). Let's
> > > work
> > > > > > in terms of, say, the RAMP user-defined function. It is used in
> > > > > > several tests [1].
> > > > > >
> > > > > > We may need to support all the user defined table functions with
> > > TABLE
> > > > > > keyword left out.
> > > > > >
> > > > > > Are we planning to support it as a default syntax or as a
> dialect ?
> > > Say,
> > > > > > maybe Oracle.
> > > > > >
> > > > > > Julian Hyde <jh...@apache.org> 于2020年10月30日周五 上午5:11写道：
> > > > > >
> > > > > > > I think we can do them separately. And I think we should do the
> > > TABLE
> > > > > > > first. The biggest problem is with namespaces - if you omit
> TABLE,
> > > you
> > > > > > > have to deal with the possibility that there is a table (or
> view)
> > > > > > > called FOO and also a parameterless table function called FOO.
> Not
> > > > > > > sure how Oracle and SQL Server resolve this.
> > > > > > >
> > > > > > > Let's not use TUMBLE (or HOP, or SESSION) as the main example.
> It
> > > is
> > > > > > > somewhat built-in (i.e. has special treatment in the code).
> Let's
> > > work
> > > > > > > in terms of, say, the RAMP user-defined function. It is used in
> > > > > > > several tests [1].
> > > > > > >
> > > > > > > Julian
> > > > > > >
> > > > > > > [1]
> > > > > > >
> > > > > >
> > > > >
> > >
> https://github.com/apache/calcite/blob/ffc1e3b05e7f920d95c48f7c75fd48372684b8e7/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java#L361
> > > > > > >
> > > > > > > On Thu, Oct 29, 2020 at 1:53 PM Rui Wang <amaliujia@apache.org
> >
> > > wrote:
> > > > > > > >
> > > > > > > > In terms of SQL grammar to support omitting TABLE, there are
> > > actually
> > > > > > two
> > > > > > > > changes for
> > > > > > > >
> > > > > > > > SELECT *
> > > > > > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL
> '10'
> > > > > > > MINUTES));
> > > > > > > >
> > > > > > > > You can support omitting the TABLE after the FROM, which
> makes
> > > the
> > > > > > query
> > > > > > > > become:
> > > > > > > > SELECT *
> > > > > > > > FROM TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > > MINUTES);
> > > > > > > >
> > > > > > > > Another is omitting TABLE from the parameter, which is
> > > > > > > > SELECT *
> > > > > > > > FROM TUMBLE(Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
> > > > > > > >
> > > > > > > > Do we want to achieve both or just one of these? If for one
> the
> > > > > grammar
> > > > > > > is
> > > > > > > > too complicated to change but for another the grammar is
> easier
> > > to be
> > > > > > > > changed, are we ok to only have one keyword omitted?
> > > > > > > >
> > > > > > > > -Rui
> > > > > > > >
> > > > > > > > On Thu, Oct 29, 2020 at 11:28 AM Julian Hyde <
> jhyde@apache.org>
> > > > > wrote:
> > > > > > > >
> > > > > > > > > Can we drop the word "polymorphic" from the discussion?
> > > Polymorphic
> > > > > > > > > table functions are a valid ask, but can be a separate
> > > discussion.
> > > > > > > > > This is about calling table functions without the TABLE
> > > keyword,
> > > > > > > > > right?
> > > > > > > > >
> > > > > > > > > Which is what I said to you four years ago:
> > > > > > > > >
> > > > >
> https://issues.apache.org/jira/browse/CALCITE-1472#comment-15664799
> > > > > > > > >
> > > > > > > > > In other words: let's fix
> > > > > > > > > https://issues.apache.org/jira/browse/CALCITE-1490.
> > > > > > > > >
> > > > > > > > > Julian
> > > > > > > > >
> > > > > > > > > On Thu, Oct 29, 2020 at 2:26 AM Jark Wu <im...@gmail.com>
> > > wrote:
> > > > > > > > > >
> > > > > > > > > > Hi all,
> > > > > > > > > >
> > > > > > > > > > It's great to see Calcite already supports PTF syntax and
> > > support
> > > > > > the
> > > > > > > > > > out-of-box new window syntax.
> > > > > > > > > >
> > > > > > > > > > SELECT *
> > > > > > > > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime),
> INTERVAL
> > > '10'
> > > > > > > > > MINUTES));
> > > > > > > > > >
> > > > > > > > > > However, some people from the Flink community think that
> the
> > > > > > TABLE()
> > > > > > > > > > keyword is a little verbose for users [5].
> > > > > > > > > > I have seen the discussion in the previous mailing list
> [1],
> > > and
> > > > > > > know the
> > > > > > > > > > TABLE() keyword is mandatory in SQL standard paper.
> > > > > > > > > >
> > > > > > > > > > But it seems that other databases are not following the
> > > > > standard, I
> > > > > > > find
> > > > > > > > > > that Polymorphic Table Function syntax
> > > > > > > > > >  in Oracle is more concise without the TABLE() keywords
> > > [2][3],
> > > > > > e.g.
> > > > > > > > > >
> > > > > > > > > > SELECT *
> > > > > > > > > > FROM skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
> > > > > > > > > >
> > > > > > > > > > Besides, SQL Server also seems to support the non TABLE()
> > > syntax
> > > > > > for
> > > > > > > > > > table-valued functions [4].
> > > > > > > > > > If we can support the Oracle syntax, it would be more
> > > consistent
> > > > > > with
> > > > > > > > > > existing TUMBLE functions
> > > > > > > > > > and hence easier to be picked up by users.
> > > > > > > > > >
> > > > > > > > > > The new window TVF syntax can be:
> > > > > > > > > >
> > > > > > > > > > SELECT window_start, count(*)
> > > > > > > > > > FROM TUMBLE(Bid, COLUMNS(bidtime), INTERVAL '10'
> MINUTES))
> > > > > > > > > > GROUP BY window_start;
> > > > > > > > > >
> > > > > > > > > > Which is more similar to the existing group window
> functions:
> > > > > > > > > >
> > > > > > > > > > SELECT TUMBLE_START(bidtime, INTERVAL '10' MINUTES),
> count(*)
> > > > > > > > > > FROM Bid
> > > > > > > > > > GROUP BY TUMBLE(bidtime, INTERVAL '10' MINUTES);
> > > > > > > > > >
> > > > > > > > > > I am fairly inexperienced with the parsing and validation
> > > logic
> > > > > in
> > > > > > > > > Calcite,
> > > > > > > > > > so I don't know whether the new syntax can be supported
> in
> > > > > Calcite.
> > > > > > > > > >
> > > > > > > > > > What do you think?
> > > > > > > > > >
> > > > > > > > > > Best,
> > > > > > > > > > Jark
> > > > > > > > > >
> > > > > > > > > > [1]:
> > > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > >
> https://lists.apache.org/x/thread.html/4a91632b1c780ef9d67311f90fce626582faae7d30a134a768c3d324@%3Cdev.calcite.apache.org%3E
> > > > > > > > > > [2]:
> > > > > > > > >
> > > > >
> https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
> > > > > > > > > > [3]:
> > > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > >
> https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-optimization-and-tuning.html#GUID-F8E13DDD-710D-4626-824E-B344849C5AFE
> > > > > > > > > > [4]:
> > > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > >
> https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/
> > > > > > > > > > [5]:
> > > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > >
> http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-145-Support-SQL-windowing-table-valued-function-tp45269p45665.html
> > > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > >
>

Re: [DISCUSS] Support Oracle Polymorphic Table Function Syntax

Posted by Julian Hyde <jh...@apache.org>.

Something like that. I guess if TT is a function with zero parameters
and also a table, then does TT in the following refer to the function
or the table?

  SELECT *
  FROM TT

Note that the TT function doesn't even have to be a table function.
Table functions are functions, and so are in the same namespace.

Furthermore, we look up tables from the current schema, whereas we
look up functions (and table functions) from the path.

Julian

On Mon, Nov 2, 2020 at 3:55 AM Danny Chan <da...@apache.org> wrote:
>
> Thanks Julian ~
>
> > Possibly, but I'd be cautious, because the semantics become ambiguous
> if there are name clashes.
>
> Assumes there is a table named TT and a table function named TT(param_a,
> param_b), they are under the same namespace, do you mean
>
> "FROM TT(param_a, param_b)" or "FROM TT(TABLE TT, param_b)"
>
> become ambiguous ?
>
>
> Julian Hyde <jh...@apache.org> 于2020年10月31日周六 上午3:19写道：
>
> > > CALCITE-1490 suggests only add to SQL server, but as Oracle, Snowflake
> > and
> > > BigQuery seems all support the simplified syntax, it might be enough to
> > > justify adding this support to default syntax?
> >
> > Possibly, but I'd be cautious, because the semantics become ambiguous
> > if there are name clashes.
> >
> > We should document what should be the behavior if there are name
> > clashes, and also research what SQL Server, Oracle, Snowflake and
> > BigQuery do.
> >
> > On Fri, Oct 30, 2020 at 10:55 AM Rui Wang <am...@apache.org> wrote:
> > >
> > > >Are we planning to support it as a default syntax or as a dialect ? Say,
> > > >maybe Oracle.
> > >
> > > CALCITE-1490 suggests only add to SQL server, but as Oracle, Snowflake
> > and
> > > BigQuery seems all support the simplified syntax, it might be enough to
> > > justify adding this support to default syntax?
> > >
> > > >Another idea is that maybe we can use the parentheses to distinguish
> > > >whether
> > > >this is a table or table-function and use different namespace for
> > > >validation?
> > >
> > > The idea sounds good. The implementation might become complicated.
> > > Namespaces are only different when looking up a table function or a
> > table,
> > > others have to be the same.
> > >
> > >
> > > -Rui
> > >
> > >
> > > On Thu, Oct 29, 2020 at 9:11 PM Jark Wu <im...@gmail.com> wrote:
> > >
> > > > Hi all,
> > > >
> > > > Yes, there are two separate discussions here.
> > > > 1) omit TABLE() keyword for table function calls, i.e. CALCITE-1490
> > > > 2) omit TABLE keyword for the table parameter in TVF.
> > > >
> > > > Let's focus on the first discussion. If I understand correctly, the
> > problem
> > > > is
> > > >  the namespace conflict of table and table-function.
> > > >
> > > > I have tested table-function features in SQL Server (as following
> > shows),
> > > > it seems that the
> > > > parentheses are required to invoke a parameterless table-function.
> > > > There is a similar question in Stackoverflow[1].
> > > >
> > > > > CREATE FUNCTION udfProductInYear ()
> > > >   RETURNS TABLE
> > > >   AS
> > > >   RETURN
> > > >     SELECT * FROM [dbo].[TEST];
> > > >
> > > > > select * from udfProductInYear;
> > > > Parameters were not supplied for the function 'udfProductInYear'.
> > > >
> > > > > select * from udfProductInYear();
> > > > This works.
> > > >
> > > > > create table udfProductInYear(a int, b VARCHAR);
> > > > There is already an object named 'udfProductInYear' in the database.
> > > >
> > > > From the above error messages, we can see that SQL Server shares the
> > same
> > > > namespace for table and table-function.
> > > > So I think we can do the same thing but only enabled via a new method
> > in
> > > > SqlConformance.
> > > >
> > > > Another idea is that maybe we can use the parentheses to distinguish
> > > > whether
> > > > this is a table or table-function and use different namespace for
> > > > validation?
> > > >
> > > > Best,
> > > > Jark
> > > >
> > > > [1]: https://stackoverflow.com/a/21022682/4915129
> > > >
> > > >
> > > > On Fri, 30 Oct 2020 at 09:48, Danny Chan <da...@apache.org> wrote:
> > > >
> > > > > > Let's not use TUMBLE (or HOP, or SESSION) as the main example. It
> > is
> > > > > somewhat built-in (i.e. has special treatment in the code). Let's
> > work
> > > > > in terms of, say, the RAMP user-defined function. It is used in
> > > > > several tests [1].
> > > > >
> > > > > We may need to support all the user defined table functions with
> > TABLE
> > > > > keyword left out.
> > > > >
> > > > > Are we planning to support it as a default syntax or as a dialect ?
> > Say,
> > > > > maybe Oracle.
> > > > >
> > > > > Julian Hyde <jh...@apache.org> 于2020年10月30日周五 上午5:11写道：
> > > > >
> > > > > > I think we can do them separately. And I think we should do the
> > TABLE
> > > > > > first. The biggest problem is with namespaces - if you omit TABLE,
> > you
> > > > > > have to deal with the possibility that there is a table (or view)
> > > > > > called FOO and also a parameterless table function called FOO. Not
> > > > > > sure how Oracle and SQL Server resolve this.
> > > > > >
> > > > > > Let's not use TUMBLE (or HOP, or SESSION) as the main example. It
> > is
> > > > > > somewhat built-in (i.e. has special treatment in the code). Let's
> > work
> > > > > > in terms of, say, the RAMP user-defined function. It is used in
> > > > > > several tests [1].
> > > > > >
> > > > > > Julian
> > > > > >
> > > > > > [1]
> > > > > >
> > > > >
> > > >
> > https://github.com/apache/calcite/blob/ffc1e3b05e7f920d95c48f7c75fd48372684b8e7/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java#L361
> > > > > >
> > > > > > On Thu, Oct 29, 2020 at 1:53 PM Rui Wang <am...@apache.org>
> > wrote:
> > > > > > >
> > > > > > > In terms of SQL grammar to support omitting TABLE, there are
> > actually
> > > > > two
> > > > > > > changes for
> > > > > > >
> > > > > > > SELECT *
> > > > > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > > > > > MINUTES));
> > > > > > >
> > > > > > > You can support omitting the TABLE after the FROM, which makes
> > the
> > > > > query
> > > > > > > become:
> > > > > > > SELECT *
> > > > > > > FROM TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > MINUTES);
> > > > > > >
> > > > > > > Another is omitting TABLE from the parameter, which is
> > > > > > > SELECT *
> > > > > > > FROM TUMBLE(Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
> > > > > > >
> > > > > > > Do we want to achieve both or just one of these? If for one the
> > > > grammar
> > > > > > is
> > > > > > > too complicated to change but for another the grammar is easier
> > to be
> > > > > > > changed, are we ok to only have one keyword omitted?
> > > > > > >
> > > > > > > -Rui
> > > > > > >
> > > > > > > On Thu, Oct 29, 2020 at 11:28 AM Julian Hyde <jh...@apache.org>
> > > > wrote:
> > > > > > >
> > > > > > > > Can we drop the word "polymorphic" from the discussion?
> > Polymorphic
> > > > > > > > table functions are a valid ask, but can be a separate
> > discussion.
> > > > > > > > This is about calling table functions without the TABLE
> > keyword,
> > > > > > > > right?
> > > > > > > >
> > > > > > > > Which is what I said to you four years ago:
> > > > > > > >
> > > > https://issues.apache.org/jira/browse/CALCITE-1472#comment-15664799
> > > > > > > >
> > > > > > > > In other words: let's fix
> > > > > > > > https://issues.apache.org/jira/browse/CALCITE-1490.
> > > > > > > >
> > > > > > > > Julian
> > > > > > > >
> > > > > > > > On Thu, Oct 29, 2020 at 2:26 AM Jark Wu <im...@gmail.com>
> > wrote:
> > > > > > > > >
> > > > > > > > > Hi all,
> > > > > > > > >
> > > > > > > > > It's great to see Calcite already supports PTF syntax and
> > support
> > > > > the
> > > > > > > > > out-of-box new window syntax.
> > > > > > > > >
> > > > > > > > > SELECT *
> > > > > > > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL
> > '10'
> > > > > > > > MINUTES));
> > > > > > > > >
> > > > > > > > > However, some people from the Flink community think that the
> > > > > TABLE()
> > > > > > > > > keyword is a little verbose for users [5].
> > > > > > > > > I have seen the discussion in the previous mailing list [1],
> > and
> > > > > > know the
> > > > > > > > > TABLE() keyword is mandatory in SQL standard paper.
> > > > > > > > >
> > > > > > > > > But it seems that other databases are not following the
> > > > standard, I
> > > > > > find
> > > > > > > > > that Polymorphic Table Function syntax
> > > > > > > > >  in Oracle is more concise without the TABLE() keywords
> > [2][3],
> > > > > e.g.
> > > > > > > > >
> > > > > > > > > SELECT *
> > > > > > > > > FROM skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
> > > > > > > > >
> > > > > > > > > Besides, SQL Server also seems to support the non TABLE()
> > syntax
> > > > > for
> > > > > > > > > table-valued functions [4].
> > > > > > > > > If we can support the Oracle syntax, it would be more
> > consistent
> > > > > with
> > > > > > > > > existing TUMBLE functions
> > > > > > > > > and hence easier to be picked up by users.
> > > > > > > > >
> > > > > > > > > The new window TVF syntax can be:
> > > > > > > > >
> > > > > > > > > SELECT window_start, count(*)
> > > > > > > > > FROM TUMBLE(Bid, COLUMNS(bidtime), INTERVAL '10' MINUTES))
> > > > > > > > > GROUP BY window_start;
> > > > > > > > >
> > > > > > > > > Which is more similar to the existing group window functions:
> > > > > > > > >
> > > > > > > > > SELECT TUMBLE_START(bidtime, INTERVAL '10' MINUTES), count(*)
> > > > > > > > > FROM Bid
> > > > > > > > > GROUP BY TUMBLE(bidtime, INTERVAL '10' MINUTES);
> > > > > > > > >
> > > > > > > > > I am fairly inexperienced with the parsing and validation
> > logic
> > > > in
> > > > > > > > Calcite,
> > > > > > > > > so I don't know whether the new syntax can be supported in
> > > > Calcite.
> > > > > > > > >
> > > > > > > > > What do you think?
> > > > > > > > >
> > > > > > > > > Best,
> > > > > > > > > Jark
> > > > > > > > >
> > > > > > > > > [1]:
> > > > > > > > >
> > > > > > > >
> > > > > >
> > > > >
> > > >
> > https://lists.apache.org/x/thread.html/4a91632b1c780ef9d67311f90fce626582faae7d30a134a768c3d324@%3Cdev.calcite.apache.org%3E
> > > > > > > > > [2]:
> > > > > > > >
> > > > https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
> > > > > > > > > [3]:
> > > > > > > > >
> > > > > > > >
> > > > > >
> > > > >
> > > >
> > https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-optimization-and-tuning.html#GUID-F8E13DDD-710D-4626-824E-B344849C5AFE
> > > > > > > > > [4]:
> > > > > > > > >
> > > > > > > >
> > > > > >
> > > > >
> > > >
> > https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/
> > > > > > > > > [5]:
> > > > > > > > >
> > > > > > > >
> > > > > >
> > > > >
> > > >
> > http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-145-Support-SQL-windowing-table-valued-function-tp45269p45665.html
> > > > > > > >
> > > > > >
> > > > >
> > > >
> >

Re: [DISCUSS] Support Oracle Polymorphic Table Function Syntax

Posted by Danny Chan <da...@apache.org>.

Thanks Julian ~

> Possibly, but I'd be cautious, because the semantics become ambiguous
if there are name clashes.

Assumes there is a table named TT and a table function named TT(param_a,
param_b), they are under the same namespace, do you mean

"FROM TT(param_a, param_b)" or "FROM TT(TABLE TT, param_b)"

become ambiguous ?


Julian Hyde <jh...@apache.org> 于2020年10月31日周六 上午3:19写道：

> > CALCITE-1490 suggests only add to SQL server, but as Oracle, Snowflake
> and
> > BigQuery seems all support the simplified syntax, it might be enough to
> > justify adding this support to default syntax?
>
> Possibly, but I'd be cautious, because the semantics become ambiguous
> if there are name clashes.
>
> We should document what should be the behavior if there are name
> clashes, and also research what SQL Server, Oracle, Snowflake and
> BigQuery do.
>
> On Fri, Oct 30, 2020 at 10:55 AM Rui Wang <am...@apache.org> wrote:
> >
> > >Are we planning to support it as a default syntax or as a dialect ? Say,
> > >maybe Oracle.
> >
> > CALCITE-1490 suggests only add to SQL server, but as Oracle, Snowflake
> and
> > BigQuery seems all support the simplified syntax, it might be enough to
> > justify adding this support to default syntax?
> >
> > >Another idea is that maybe we can use the parentheses to distinguish
> > >whether
> > >this is a table or table-function and use different namespace for
> > >validation?
> >
> > The idea sounds good. The implementation might become complicated.
> > Namespaces are only different when looking up a table function or a
> table,
> > others have to be the same.
> >
> >
> > -Rui
> >
> >
> > On Thu, Oct 29, 2020 at 9:11 PM Jark Wu <im...@gmail.com> wrote:
> >
> > > Hi all,
> > >
> > > Yes, there are two separate discussions here.
> > > 1) omit TABLE() keyword for table function calls, i.e. CALCITE-1490
> > > 2) omit TABLE keyword for the table parameter in TVF.
> > >
> > > Let's focus on the first discussion. If I understand correctly, the
> problem
> > > is
> > >  the namespace conflict of table and table-function.
> > >
> > > I have tested table-function features in SQL Server (as following
> shows),
> > > it seems that the
> > > parentheses are required to invoke a parameterless table-function.
> > > There is a similar question in Stackoverflow[1].
> > >
> > > > CREATE FUNCTION udfProductInYear ()
> > >   RETURNS TABLE
> > >   AS
> > >   RETURN
> > >     SELECT * FROM [dbo].[TEST];
> > >
> > > > select * from udfProductInYear;
> > > Parameters were not supplied for the function 'udfProductInYear'.
> > >
> > > > select * from udfProductInYear();
> > > This works.
> > >
> > > > create table udfProductInYear(a int, b VARCHAR);
> > > There is already an object named 'udfProductInYear' in the database.
> > >
> > > From the above error messages, we can see that SQL Server shares the
> same
> > > namespace for table and table-function.
> > > So I think we can do the same thing but only enabled via a new method
> in
> > > SqlConformance.
> > >
> > > Another idea is that maybe we can use the parentheses to distinguish
> > > whether
> > > this is a table or table-function and use different namespace for
> > > validation?
> > >
> > > Best,
> > > Jark
> > >
> > > [1]: https://stackoverflow.com/a/21022682/4915129
> > >
> > >
> > > On Fri, 30 Oct 2020 at 09:48, Danny Chan <da...@apache.org> wrote:
> > >
> > > > > Let's not use TUMBLE (or HOP, or SESSION) as the main example. It
> is
> > > > somewhat built-in (i.e. has special treatment in the code). Let's
> work
> > > > in terms of, say, the RAMP user-defined function. It is used in
> > > > several tests [1].
> > > >
> > > > We may need to support all the user defined table functions with
> TABLE
> > > > keyword left out.
> > > >
> > > > Are we planning to support it as a default syntax or as a dialect ?
> Say,
> > > > maybe Oracle.
> > > >
> > > > Julian Hyde <jh...@apache.org> 于2020年10月30日周五 上午5:11写道：
> > > >
> > > > > I think we can do them separately. And I think we should do the
> TABLE
> > > > > first. The biggest problem is with namespaces - if you omit TABLE,
> you
> > > > > have to deal with the possibility that there is a table (or view)
> > > > > called FOO and also a parameterless table function called FOO. Not
> > > > > sure how Oracle and SQL Server resolve this.
> > > > >
> > > > > Let's not use TUMBLE (or HOP, or SESSION) as the main example. It
> is
> > > > > somewhat built-in (i.e. has special treatment in the code). Let's
> work
> > > > > in terms of, say, the RAMP user-defined function. It is used in
> > > > > several tests [1].
> > > > >
> > > > > Julian
> > > > >
> > > > > [1]
> > > > >
> > > >
> > >
> https://github.com/apache/calcite/blob/ffc1e3b05e7f920d95c48f7c75fd48372684b8e7/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java#L361
> > > > >
> > > > > On Thu, Oct 29, 2020 at 1:53 PM Rui Wang <am...@apache.org>
> wrote:
> > > > > >
> > > > > > In terms of SQL grammar to support omitting TABLE, there are
> actually
> > > > two
> > > > > > changes for
> > > > > >
> > > > > > SELECT *
> > > > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > > > > MINUTES));
> > > > > >
> > > > > > You can support omitting the TABLE after the FROM, which makes
> the
> > > > query
> > > > > > become:
> > > > > > SELECT *
> > > > > > FROM TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> MINUTES);
> > > > > >
> > > > > > Another is omitting TABLE from the parameter, which is
> > > > > > SELECT *
> > > > > > FROM TUMBLE(Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
> > > > > >
> > > > > > Do we want to achieve both or just one of these? If for one the
> > > grammar
> > > > > is
> > > > > > too complicated to change but for another the grammar is easier
> to be
> > > > > > changed, are we ok to only have one keyword omitted?
> > > > > >
> > > > > > -Rui
> > > > > >
> > > > > > On Thu, Oct 29, 2020 at 11:28 AM Julian Hyde <jh...@apache.org>
> > > wrote:
> > > > > >
> > > > > > > Can we drop the word "polymorphic" from the discussion?
> Polymorphic
> > > > > > > table functions are a valid ask, but can be a separate
> discussion.
> > > > > > > This is about calling table functions without the TABLE
> keyword,
> > > > > > > right?
> > > > > > >
> > > > > > > Which is what I said to you four years ago:
> > > > > > >
> > > https://issues.apache.org/jira/browse/CALCITE-1472#comment-15664799
> > > > > > >
> > > > > > > In other words: let's fix
> > > > > > > https://issues.apache.org/jira/browse/CALCITE-1490.
> > > > > > >
> > > > > > > Julian
> > > > > > >
> > > > > > > On Thu, Oct 29, 2020 at 2:26 AM Jark Wu <im...@gmail.com>
> wrote:
> > > > > > > >
> > > > > > > > Hi all,
> > > > > > > >
> > > > > > > > It's great to see Calcite already supports PTF syntax and
> support
> > > > the
> > > > > > > > out-of-box new window syntax.
> > > > > > > >
> > > > > > > > SELECT *
> > > > > > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL
> '10'
> > > > > > > MINUTES));
> > > > > > > >
> > > > > > > > However, some people from the Flink community think that the
> > > > TABLE()
> > > > > > > > keyword is a little verbose for users [5].
> > > > > > > > I have seen the discussion in the previous mailing list [1],
> and
> > > > > know the
> > > > > > > > TABLE() keyword is mandatory in SQL standard paper.
> > > > > > > >
> > > > > > > > But it seems that other databases are not following the
> > > standard, I
> > > > > find
> > > > > > > > that Polymorphic Table Function syntax
> > > > > > > >  in Oracle is more concise without the TABLE() keywords
> [2][3],
> > > > e.g.
> > > > > > > >
> > > > > > > > SELECT *
> > > > > > > > FROM skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
> > > > > > > >
> > > > > > > > Besides, SQL Server also seems to support the non TABLE()
> syntax
> > > > for
> > > > > > > > table-valued functions [4].
> > > > > > > > If we can support the Oracle syntax, it would be more
> consistent
> > > > with
> > > > > > > > existing TUMBLE functions
> > > > > > > > and hence easier to be picked up by users.
> > > > > > > >
> > > > > > > > The new window TVF syntax can be:
> > > > > > > >
> > > > > > > > SELECT window_start, count(*)
> > > > > > > > FROM TUMBLE(Bid, COLUMNS(bidtime), INTERVAL '10' MINUTES))
> > > > > > > > GROUP BY window_start;
> > > > > > > >
> > > > > > > > Which is more similar to the existing group window functions:
> > > > > > > >
> > > > > > > > SELECT TUMBLE_START(bidtime, INTERVAL '10' MINUTES), count(*)
> > > > > > > > FROM Bid
> > > > > > > > GROUP BY TUMBLE(bidtime, INTERVAL '10' MINUTES);
> > > > > > > >
> > > > > > > > I am fairly inexperienced with the parsing and validation
> logic
> > > in
> > > > > > > Calcite,
> > > > > > > > so I don't know whether the new syntax can be supported in
> > > Calcite.
> > > > > > > >
> > > > > > > > What do you think?
> > > > > > > >
> > > > > > > > Best,
> > > > > > > > Jark
> > > > > > > >
> > > > > > > > [1]:
> > > > > > > >
> > > > > > >
> > > > >
> > > >
> > >
> https://lists.apache.org/x/thread.html/4a91632b1c780ef9d67311f90fce626582faae7d30a134a768c3d324@%3Cdev.calcite.apache.org%3E
> > > > > > > > [2]:
> > > > > > >
> > > https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
> > > > > > > > [3]:
> > > > > > > >
> > > > > > >
> > > > >
> > > >
> > >
> https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-optimization-and-tuning.html#GUID-F8E13DDD-710D-4626-824E-B344849C5AFE
> > > > > > > > [4]:
> > > > > > > >
> > > > > > >
> > > > >
> > > >
> > >
> https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/
> > > > > > > > [5]:
> > > > > > > >
> > > > > > >
> > > > >
> > > >
> > >
> http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-145-Support-SQL-windowing-table-valued-function-tp45269p45665.html
> > > > > > >
> > > > >
> > > >
> > >
>

Re: [DISCUSS] Support Oracle Polymorphic Table Function Syntax

Posted by Julian Hyde <jh...@apache.org>.

> CALCITE-1490 suggests only add to SQL server, but as Oracle, Snowflake and
> BigQuery seems all support the simplified syntax, it might be enough to
> justify adding this support to default syntax?

Possibly, but I'd be cautious, because the semantics become ambiguous
if there are name clashes.

We should document what should be the behavior if there are name
clashes, and also research what SQL Server, Oracle, Snowflake and
BigQuery do.

On Fri, Oct 30, 2020 at 10:55 AM Rui Wang <am...@apache.org> wrote:
>
> >Are we planning to support it as a default syntax or as a dialect ? Say,
> >maybe Oracle.
>
> CALCITE-1490 suggests only add to SQL server, but as Oracle, Snowflake and
> BigQuery seems all support the simplified syntax, it might be enough to
> justify adding this support to default syntax?
>
> >Another idea is that maybe we can use the parentheses to distinguish
> >whether
> >this is a table or table-function and use different namespace for
> >validation?
>
> The idea sounds good. The implementation might become complicated.
> Namespaces are only different when looking up a table function or a table,
> others have to be the same.
>
>
> -Rui
>
>
> On Thu, Oct 29, 2020 at 9:11 PM Jark Wu <im...@gmail.com> wrote:
>
> > Hi all,
> >
> > Yes, there are two separate discussions here.
> > 1) omit TABLE() keyword for table function calls, i.e. CALCITE-1490
> > 2) omit TABLE keyword for the table parameter in TVF.
> >
> > Let's focus on the first discussion. If I understand correctly, the problem
> > is
> >  the namespace conflict of table and table-function.
> >
> > I have tested table-function features in SQL Server (as following shows),
> > it seems that the
> > parentheses are required to invoke a parameterless table-function.
> > There is a similar question in Stackoverflow[1].
> >
> > > CREATE FUNCTION udfProductInYear ()
> >   RETURNS TABLE
> >   AS
> >   RETURN
> >     SELECT * FROM [dbo].[TEST];
> >
> > > select * from udfProductInYear;
> > Parameters were not supplied for the function 'udfProductInYear'.
> >
> > > select * from udfProductInYear();
> > This works.
> >
> > > create table udfProductInYear(a int, b VARCHAR);
> > There is already an object named 'udfProductInYear' in the database.
> >
> > From the above error messages, we can see that SQL Server shares the same
> > namespace for table and table-function.
> > So I think we can do the same thing but only enabled via a new method in
> > SqlConformance.
> >
> > Another idea is that maybe we can use the parentheses to distinguish
> > whether
> > this is a table or table-function and use different namespace for
> > validation?
> >
> > Best,
> > Jark
> >
> > [1]: https://stackoverflow.com/a/21022682/4915129
> >
> >
> > On Fri, 30 Oct 2020 at 09:48, Danny Chan <da...@apache.org> wrote:
> >
> > > > Let's not use TUMBLE (or HOP, or SESSION) as the main example. It is
> > > somewhat built-in (i.e. has special treatment in the code). Let's work
> > > in terms of, say, the RAMP user-defined function. It is used in
> > > several tests [1].
> > >
> > > We may need to support all the user defined table functions with TABLE
> > > keyword left out.
> > >
> > > Are we planning to support it as a default syntax or as a dialect ? Say,
> > > maybe Oracle.
> > >
> > > Julian Hyde <jh...@apache.org> 于2020年10月30日周五 上午5:11写道：
> > >
> > > > I think we can do them separately. And I think we should do the TABLE
> > > > first. The biggest problem is with namespaces - if you omit TABLE, you
> > > > have to deal with the possibility that there is a table (or view)
> > > > called FOO and also a parameterless table function called FOO. Not
> > > > sure how Oracle and SQL Server resolve this.
> > > >
> > > > Let's not use TUMBLE (or HOP, or SESSION) as the main example. It is
> > > > somewhat built-in (i.e. has special treatment in the code). Let's work
> > > > in terms of, say, the RAMP user-defined function. It is used in
> > > > several tests [1].
> > > >
> > > > Julian
> > > >
> > > > [1]
> > > >
> > >
> > https://github.com/apache/calcite/blob/ffc1e3b05e7f920d95c48f7c75fd48372684b8e7/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java#L361
> > > >
> > > > On Thu, Oct 29, 2020 at 1:53 PM Rui Wang <am...@apache.org> wrote:
> > > > >
> > > > > In terms of SQL grammar to support omitting TABLE, there are actually
> > > two
> > > > > changes for
> > > > >
> > > > > SELECT *
> > > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > > > MINUTES));
> > > > >
> > > > > You can support omitting the TABLE after the FROM, which makes the
> > > query
> > > > > become:
> > > > > SELECT *
> > > > > FROM TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
> > > > >
> > > > > Another is omitting TABLE from the parameter, which is
> > > > > SELECT *
> > > > > FROM TUMBLE(Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
> > > > >
> > > > > Do we want to achieve both or just one of these? If for one the
> > grammar
> > > > is
> > > > > too complicated to change but for another the grammar is easier to be
> > > > > changed, are we ok to only have one keyword omitted?
> > > > >
> > > > > -Rui
> > > > >
> > > > > On Thu, Oct 29, 2020 at 11:28 AM Julian Hyde <jh...@apache.org>
> > wrote:
> > > > >
> > > > > > Can we drop the word "polymorphic" from the discussion? Polymorphic
> > > > > > table functions are a valid ask, but can be a separate discussion.
> > > > > > This is about calling table functions without the TABLE keyword,
> > > > > > right?
> > > > > >
> > > > > > Which is what I said to you four years ago:
> > > > > >
> > https://issues.apache.org/jira/browse/CALCITE-1472#comment-15664799
> > > > > >
> > > > > > In other words: let's fix
> > > > > > https://issues.apache.org/jira/browse/CALCITE-1490.
> > > > > >
> > > > > > Julian
> > > > > >
> > > > > > On Thu, Oct 29, 2020 at 2:26 AM Jark Wu <im...@gmail.com> wrote:
> > > > > > >
> > > > > > > Hi all,
> > > > > > >
> > > > > > > It's great to see Calcite already supports PTF syntax and support
> > > the
> > > > > > > out-of-box new window syntax.
> > > > > > >
> > > > > > > SELECT *
> > > > > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > > > > > MINUTES));
> > > > > > >
> > > > > > > However, some people from the Flink community think that the
> > > TABLE()
> > > > > > > keyword is a little verbose for users [5].
> > > > > > > I have seen the discussion in the previous mailing list [1], and
> > > > know the
> > > > > > > TABLE() keyword is mandatory in SQL standard paper.
> > > > > > >
> > > > > > > But it seems that other databases are not following the
> > standard, I
> > > > find
> > > > > > > that Polymorphic Table Function syntax
> > > > > > >  in Oracle is more concise without the TABLE() keywords [2][3],
> > > e.g.
> > > > > > >
> > > > > > > SELECT *
> > > > > > > FROM skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
> > > > > > >
> > > > > > > Besides, SQL Server also seems to support the non TABLE() syntax
> > > for
> > > > > > > table-valued functions [4].
> > > > > > > If we can support the Oracle syntax, it would be more consistent
> > > with
> > > > > > > existing TUMBLE functions
> > > > > > > and hence easier to be picked up by users.
> > > > > > >
> > > > > > > The new window TVF syntax can be:
> > > > > > >
> > > > > > > SELECT window_start, count(*)
> > > > > > > FROM TUMBLE(Bid, COLUMNS(bidtime), INTERVAL '10' MINUTES))
> > > > > > > GROUP BY window_start;
> > > > > > >
> > > > > > > Which is more similar to the existing group window functions:
> > > > > > >
> > > > > > > SELECT TUMBLE_START(bidtime, INTERVAL '10' MINUTES), count(*)
> > > > > > > FROM Bid
> > > > > > > GROUP BY TUMBLE(bidtime, INTERVAL '10' MINUTES);
> > > > > > >
> > > > > > > I am fairly inexperienced with the parsing and validation logic
> > in
> > > > > > Calcite,
> > > > > > > so I don't know whether the new syntax can be supported in
> > Calcite.
> > > > > > >
> > > > > > > What do you think?
> > > > > > >
> > > > > > > Best,
> > > > > > > Jark
> > > > > > >
> > > > > > > [1]:
> > > > > > >
> > > > > >
> > > >
> > >
> > https://lists.apache.org/x/thread.html/4a91632b1c780ef9d67311f90fce626582faae7d30a134a768c3d324@%3Cdev.calcite.apache.org%3E
> > > > > > > [2]:
> > > > > >
> > https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
> > > > > > > [3]:
> > > > > > >
> > > > > >
> > > >
> > >
> > https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-optimization-and-tuning.html#GUID-F8E13DDD-710D-4626-824E-B344849C5AFE
> > > > > > > [4]:
> > > > > > >
> > > > > >
> > > >
> > >
> > https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/
> > > > > > > [5]:
> > > > > > >
> > > > > >
> > > >
> > >
> > http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-145-Support-SQL-windowing-table-valued-function-tp45269p45665.html
> > > > > >
> > > >
> > >
> >

Re: [DISCUSS] Support Oracle Polymorphic Table Function Syntax

Posted by Rui Wang <am...@apache.org>.

>Are we planning to support it as a default syntax or as a dialect ? Say,
>maybe Oracle.

CALCITE-1490 suggests only add to SQL server, but as Oracle, Snowflake and
BigQuery seems all support the simplified syntax, it might be enough to
justify adding this support to default syntax?

>Another idea is that maybe we can use the parentheses to distinguish
>whether
>this is a table or table-function and use different namespace for
>validation?

The idea sounds good. The implementation might become complicated.
Namespaces are only different when looking up a table function or a table,
others have to be the same.


-Rui


On Thu, Oct 29, 2020 at 9:11 PM Jark Wu <im...@gmail.com> wrote:

> Hi all,
>
> Yes, there are two separate discussions here.
> 1) omit TABLE() keyword for table function calls, i.e. CALCITE-1490
> 2) omit TABLE keyword for the table parameter in TVF.
>
> Let's focus on the first discussion. If I understand correctly, the problem
> is
>  the namespace conflict of table and table-function.
>
> I have tested table-function features in SQL Server (as following shows),
> it seems that the
> parentheses are required to invoke a parameterless table-function.
> There is a similar question in Stackoverflow[1].
>
> > CREATE FUNCTION udfProductInYear ()
>   RETURNS TABLE
>   AS
>   RETURN
>     SELECT * FROM [dbo].[TEST];
>
> > select * from udfProductInYear;
> Parameters were not supplied for the function 'udfProductInYear'.
>
> > select * from udfProductInYear();
> This works.
>
> > create table udfProductInYear(a int, b VARCHAR);
> There is already an object named 'udfProductInYear' in the database.
>
> From the above error messages, we can see that SQL Server shares the same
> namespace for table and table-function.
> So I think we can do the same thing but only enabled via a new method in
> SqlConformance.
>
> Another idea is that maybe we can use the parentheses to distinguish
> whether
> this is a table or table-function and use different namespace for
> validation?
>
> Best,
> Jark
>
> [1]: https://stackoverflow.com/a/21022682/4915129
>
>
> On Fri, 30 Oct 2020 at 09:48, Danny Chan <da...@apache.org> wrote:
>
> > > Let's not use TUMBLE (or HOP, or SESSION) as the main example. It is
> > somewhat built-in (i.e. has special treatment in the code). Let's work
> > in terms of, say, the RAMP user-defined function. It is used in
> > several tests [1].
> >
> > We may need to support all the user defined table functions with TABLE
> > keyword left out.
> >
> > Are we planning to support it as a default syntax or as a dialect ? Say,
> > maybe Oracle.
> >
> > Julian Hyde <jh...@apache.org> 于2020年10月30日周五 上午5:11写道：
> >
> > > I think we can do them separately. And I think we should do the TABLE
> > > first. The biggest problem is with namespaces - if you omit TABLE, you
> > > have to deal with the possibility that there is a table (or view)
> > > called FOO and also a parameterless table function called FOO. Not
> > > sure how Oracle and SQL Server resolve this.
> > >
> > > Let's not use TUMBLE (or HOP, or SESSION) as the main example. It is
> > > somewhat built-in (i.e. has special treatment in the code). Let's work
> > > in terms of, say, the RAMP user-defined function. It is used in
> > > several tests [1].
> > >
> > > Julian
> > >
> > > [1]
> > >
> >
> https://github.com/apache/calcite/blob/ffc1e3b05e7f920d95c48f7c75fd48372684b8e7/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java#L361
> > >
> > > On Thu, Oct 29, 2020 at 1:53 PM Rui Wang <am...@apache.org> wrote:
> > > >
> > > > In terms of SQL grammar to support omitting TABLE, there are actually
> > two
> > > > changes for
> > > >
> > > > SELECT *
> > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > > MINUTES));
> > > >
> > > > You can support omitting the TABLE after the FROM, which makes the
> > query
> > > > become:
> > > > SELECT *
> > > > FROM TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
> > > >
> > > > Another is omitting TABLE from the parameter, which is
> > > > SELECT *
> > > > FROM TUMBLE(Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
> > > >
> > > > Do we want to achieve both or just one of these? If for one the
> grammar
> > > is
> > > > too complicated to change but for another the grammar is easier to be
> > > > changed, are we ok to only have one keyword omitted?
> > > >
> > > > -Rui
> > > >
> > > > On Thu, Oct 29, 2020 at 11:28 AM Julian Hyde <jh...@apache.org>
> wrote:
> > > >
> > > > > Can we drop the word "polymorphic" from the discussion? Polymorphic
> > > > > table functions are a valid ask, but can be a separate discussion.
> > > > > This is about calling table functions without the TABLE keyword,
> > > > > right?
> > > > >
> > > > > Which is what I said to you four years ago:
> > > > >
> https://issues.apache.org/jira/browse/CALCITE-1472#comment-15664799
> > > > >
> > > > > In other words: let's fix
> > > > > https://issues.apache.org/jira/browse/CALCITE-1490.
> > > > >
> > > > > Julian
> > > > >
> > > > > On Thu, Oct 29, 2020 at 2:26 AM Jark Wu <im...@gmail.com> wrote:
> > > > > >
> > > > > > Hi all,
> > > > > >
> > > > > > It's great to see Calcite already supports PTF syntax and support
> > the
> > > > > > out-of-box new window syntax.
> > > > > >
> > > > > > SELECT *
> > > > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > > > > MINUTES));
> > > > > >
> > > > > > However, some people from the Flink community think that the
> > TABLE()
> > > > > > keyword is a little verbose for users [5].
> > > > > > I have seen the discussion in the previous mailing list [1], and
> > > know the
> > > > > > TABLE() keyword is mandatory in SQL standard paper.
> > > > > >
> > > > > > But it seems that other databases are not following the
> standard, I
> > > find
> > > > > > that Polymorphic Table Function syntax
> > > > > >  in Oracle is more concise without the TABLE() keywords [2][3],
> > e.g.
> > > > > >
> > > > > > SELECT *
> > > > > > FROM skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
> > > > > >
> > > > > > Besides, SQL Server also seems to support the non TABLE() syntax
> > for
> > > > > > table-valued functions [4].
> > > > > > If we can support the Oracle syntax, it would be more consistent
> > with
> > > > > > existing TUMBLE functions
> > > > > > and hence easier to be picked up by users.
> > > > > >
> > > > > > The new window TVF syntax can be:
> > > > > >
> > > > > > SELECT window_start, count(*)
> > > > > > FROM TUMBLE(Bid, COLUMNS(bidtime), INTERVAL '10' MINUTES))
> > > > > > GROUP BY window_start;
> > > > > >
> > > > > > Which is more similar to the existing group window functions:
> > > > > >
> > > > > > SELECT TUMBLE_START(bidtime, INTERVAL '10' MINUTES), count(*)
> > > > > > FROM Bid
> > > > > > GROUP BY TUMBLE(bidtime, INTERVAL '10' MINUTES);
> > > > > >
> > > > > > I am fairly inexperienced with the parsing and validation logic
> in
> > > > > Calcite,
> > > > > > so I don't know whether the new syntax can be supported in
> Calcite.
> > > > > >
> > > > > > What do you think?
> > > > > >
> > > > > > Best,
> > > > > > Jark
> > > > > >
> > > > > > [1]:
> > > > > >
> > > > >
> > >
> >
> https://lists.apache.org/x/thread.html/4a91632b1c780ef9d67311f90fce626582faae7d30a134a768c3d324@%3Cdev.calcite.apache.org%3E
> > > > > > [2]:
> > > > >
> https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
> > > > > > [3]:
> > > > > >
> > > > >
> > >
> >
> https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-optimization-and-tuning.html#GUID-F8E13DDD-710D-4626-824E-B344849C5AFE
> > > > > > [4]:
> > > > > >
> > > > >
> > >
> >
> https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/
> > > > > > [5]:
> > > > > >
> > > > >
> > >
> >
> http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-145-Support-SQL-windowing-table-valued-function-tp45269p45665.html
> > > > >
> > >
> >
>

Re: [DISCUSS] Support Oracle Polymorphic Table Function Syntax

Posted by Jark Wu <im...@gmail.com>.

Hi all,

Yes, there are two separate discussions here.
1) omit TABLE() keyword for table function calls, i.e. CALCITE-1490
2) omit TABLE keyword for the table parameter in TVF.

Let's focus on the first discussion. If I understand correctly, the problem
is
 the namespace conflict of table and table-function.

I have tested table-function features in SQL Server (as following shows),
it seems that the
parentheses are required to invoke a parameterless table-function.
There is a similar question in Stackoverflow[1].

> CREATE FUNCTION udfProductInYear ()
  RETURNS TABLE
  AS
  RETURN
    SELECT * FROM [dbo].[TEST];

> select * from udfProductInYear;
Parameters were not supplied for the function 'udfProductInYear'.

> select * from udfProductInYear();
This works.

> create table udfProductInYear(a int, b VARCHAR);
There is already an object named 'udfProductInYear' in the database.

From the above error messages, we can see that SQL Server shares the same
namespace for table and table-function.
So I think we can do the same thing but only enabled via a new method in
SqlConformance.

Another idea is that maybe we can use the parentheses to distinguish
whether
this is a table or table-function and use different namespace for
validation?

Best,
Jark

[1]: https://stackoverflow.com/a/21022682/4915129


On Fri, 30 Oct 2020 at 09:48, Danny Chan <da...@apache.org> wrote:

> > Let's not use TUMBLE (or HOP, or SESSION) as the main example. It is
> somewhat built-in (i.e. has special treatment in the code). Let's work
> in terms of, say, the RAMP user-defined function. It is used in
> several tests [1].
>
> We may need to support all the user defined table functions with TABLE
> keyword left out.
>
> Are we planning to support it as a default syntax or as a dialect ? Say,
> maybe Oracle.
>
> Julian Hyde <jh...@apache.org> 于2020年10月30日周五 上午5:11写道：
>
> > I think we can do them separately. And I think we should do the TABLE
> > first. The biggest problem is with namespaces - if you omit TABLE, you
> > have to deal with the possibility that there is a table (or view)
> > called FOO and also a parameterless table function called FOO. Not
> > sure how Oracle and SQL Server resolve this.
> >
> > Let's not use TUMBLE (or HOP, or SESSION) as the main example. It is
> > somewhat built-in (i.e. has special treatment in the code). Let's work
> > in terms of, say, the RAMP user-defined function. It is used in
> > several tests [1].
> >
> > Julian
> >
> > [1]
> >
> https://github.com/apache/calcite/blob/ffc1e3b05e7f920d95c48f7c75fd48372684b8e7/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java#L361
> >
> > On Thu, Oct 29, 2020 at 1:53 PM Rui Wang <am...@apache.org> wrote:
> > >
> > > In terms of SQL grammar to support omitting TABLE, there are actually
> two
> > > changes for
> > >
> > > SELECT *
> > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > MINUTES));
> > >
> > > You can support omitting the TABLE after the FROM, which makes the
> query
> > > become:
> > > SELECT *
> > > FROM TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
> > >
> > > Another is omitting TABLE from the parameter, which is
> > > SELECT *
> > > FROM TUMBLE(Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
> > >
> > > Do we want to achieve both or just one of these? If for one the grammar
> > is
> > > too complicated to change but for another the grammar is easier to be
> > > changed, are we ok to only have one keyword omitted?
> > >
> > > -Rui
> > >
> > > On Thu, Oct 29, 2020 at 11:28 AM Julian Hyde <jh...@apache.org> wrote:
> > >
> > > > Can we drop the word "polymorphic" from the discussion? Polymorphic
> > > > table functions are a valid ask, but can be a separate discussion.
> > > > This is about calling table functions without the TABLE keyword,
> > > > right?
> > > >
> > > > Which is what I said to you four years ago:
> > > > https://issues.apache.org/jira/browse/CALCITE-1472#comment-15664799
> > > >
> > > > In other words: let's fix
> > > > https://issues.apache.org/jira/browse/CALCITE-1490.
> > > >
> > > > Julian
> > > >
> > > > On Thu, Oct 29, 2020 at 2:26 AM Jark Wu <im...@gmail.com> wrote:
> > > > >
> > > > > Hi all,
> > > > >
> > > > > It's great to see Calcite already supports PTF syntax and support
> the
> > > > > out-of-box new window syntax.
> > > > >
> > > > > SELECT *
> > > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > > > MINUTES));
> > > > >
> > > > > However, some people from the Flink community think that the
> TABLE()
> > > > > keyword is a little verbose for users [5].
> > > > > I have seen the discussion in the previous mailing list [1], and
> > know the
> > > > > TABLE() keyword is mandatory in SQL standard paper.
> > > > >
> > > > > But it seems that other databases are not following the standard, I
> > find
> > > > > that Polymorphic Table Function syntax
> > > > >  in Oracle is more concise without the TABLE() keywords [2][3],
> e.g.
> > > > >
> > > > > SELECT *
> > > > > FROM skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
> > > > >
> > > > > Besides, SQL Server also seems to support the non TABLE() syntax
> for
> > > > > table-valued functions [4].
> > > > > If we can support the Oracle syntax, it would be more consistent
> with
> > > > > existing TUMBLE functions
> > > > > and hence easier to be picked up by users.
> > > > >
> > > > > The new window TVF syntax can be:
> > > > >
> > > > > SELECT window_start, count(*)
> > > > > FROM TUMBLE(Bid, COLUMNS(bidtime), INTERVAL '10' MINUTES))
> > > > > GROUP BY window_start;
> > > > >
> > > > > Which is more similar to the existing group window functions:
> > > > >
> > > > > SELECT TUMBLE_START(bidtime, INTERVAL '10' MINUTES), count(*)
> > > > > FROM Bid
> > > > > GROUP BY TUMBLE(bidtime, INTERVAL '10' MINUTES);
> > > > >
> > > > > I am fairly inexperienced with the parsing and validation logic in
> > > > Calcite,
> > > > > so I don't know whether the new syntax can be supported in Calcite.
> > > > >
> > > > > What do you think?
> > > > >
> > > > > Best,
> > > > > Jark
> > > > >
> > > > > [1]:
> > > > >
> > > >
> >
> https://lists.apache.org/x/thread.html/4a91632b1c780ef9d67311f90fce626582faae7d30a134a768c3d324@%3Cdev.calcite.apache.org%3E
> > > > > [2]:
> > > > https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
> > > > > [3]:
> > > > >
> > > >
> >
> https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-optimization-and-tuning.html#GUID-F8E13DDD-710D-4626-824E-B344849C5AFE
> > > > > [4]:
> > > > >
> > > >
> >
> https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/
> > > > > [5]:
> > > > >
> > > >
> >
> http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-145-Support-SQL-windowing-table-valued-function-tp45269p45665.html
> > > >
> >
>

Re: [DISCUSS] Support Oracle Polymorphic Table Function Syntax

Posted by Danny Chan <da...@apache.org>.

> Let's not use TUMBLE (or HOP, or SESSION) as the main example. It is
somewhat built-in (i.e. has special treatment in the code). Let's work
in terms of, say, the RAMP user-defined function. It is used in
several tests [1].

We may need to support all the user defined table functions with TABLE
keyword left out.

Are we planning to support it as a default syntax or as a dialect ? Say,
maybe Oracle.

Julian Hyde <jh...@apache.org> 于2020年10月30日周五 上午5:11写道：

> I think we can do them separately. And I think we should do the TABLE
> first. The biggest problem is with namespaces - if you omit TABLE, you
> have to deal with the possibility that there is a table (or view)
> called FOO and also a parameterless table function called FOO. Not
> sure how Oracle and SQL Server resolve this.
>
> Let's not use TUMBLE (or HOP, or SESSION) as the main example. It is
> somewhat built-in (i.e. has special treatment in the code). Let's work
> in terms of, say, the RAMP user-defined function. It is used in
> several tests [1].
>
> Julian
>
> [1]
> https://github.com/apache/calcite/blob/ffc1e3b05e7f920d95c48f7c75fd48372684b8e7/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java#L361
>
> On Thu, Oct 29, 2020 at 1:53 PM Rui Wang <am...@apache.org> wrote:
> >
> > In terms of SQL grammar to support omitting TABLE, there are actually two
> > changes for
> >
> > SELECT *
> > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> MINUTES));
> >
> > You can support omitting the TABLE after the FROM, which makes the query
> > become:
> > SELECT *
> > FROM TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
> >
> > Another is omitting TABLE from the parameter, which is
> > SELECT *
> > FROM TUMBLE(Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
> >
> > Do we want to achieve both or just one of these? If for one the grammar
> is
> > too complicated to change but for another the grammar is easier to be
> > changed, are we ok to only have one keyword omitted?
> >
> > -Rui
> >
> > On Thu, Oct 29, 2020 at 11:28 AM Julian Hyde <jh...@apache.org> wrote:
> >
> > > Can we drop the word "polymorphic" from the discussion? Polymorphic
> > > table functions are a valid ask, but can be a separate discussion.
> > > This is about calling table functions without the TABLE keyword,
> > > right?
> > >
> > > Which is what I said to you four years ago:
> > > https://issues.apache.org/jira/browse/CALCITE-1472#comment-15664799
> > >
> > > In other words: let's fix
> > > https://issues.apache.org/jira/browse/CALCITE-1490.
> > >
> > > Julian
> > >
> > > On Thu, Oct 29, 2020 at 2:26 AM Jark Wu <im...@gmail.com> wrote:
> > > >
> > > > Hi all,
> > > >
> > > > It's great to see Calcite already supports PTF syntax and support the
> > > > out-of-box new window syntax.
> > > >
> > > > SELECT *
> > > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > > MINUTES));
> > > >
> > > > However, some people from the Flink community think that the TABLE()
> > > > keyword is a little verbose for users [5].
> > > > I have seen the discussion in the previous mailing list [1], and
> know the
> > > > TABLE() keyword is mandatory in SQL standard paper.
> > > >
> > > > But it seems that other databases are not following the standard, I
> find
> > > > that Polymorphic Table Function syntax
> > > >  in Oracle is more concise without the TABLE() keywords [2][3], e.g.
> > > >
> > > > SELECT *
> > > > FROM skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
> > > >
> > > > Besides, SQL Server also seems to support the non TABLE() syntax for
> > > > table-valued functions [4].
> > > > If we can support the Oracle syntax, it would be more consistent with
> > > > existing TUMBLE functions
> > > > and hence easier to be picked up by users.
> > > >
> > > > The new window TVF syntax can be:
> > > >
> > > > SELECT window_start, count(*)
> > > > FROM TUMBLE(Bid, COLUMNS(bidtime), INTERVAL '10' MINUTES))
> > > > GROUP BY window_start;
> > > >
> > > > Which is more similar to the existing group window functions:
> > > >
> > > > SELECT TUMBLE_START(bidtime, INTERVAL '10' MINUTES), count(*)
> > > > FROM Bid
> > > > GROUP BY TUMBLE(bidtime, INTERVAL '10' MINUTES);
> > > >
> > > > I am fairly inexperienced with the parsing and validation logic in
> > > Calcite,
> > > > so I don't know whether the new syntax can be supported in Calcite.
> > > >
> > > > What do you think?
> > > >
> > > > Best,
> > > > Jark
> > > >
> > > > [1]:
> > > >
> > >
> https://lists.apache.org/x/thread.html/4a91632b1c780ef9d67311f90fce626582faae7d30a134a768c3d324@%3Cdev.calcite.apache.org%3E
> > > > [2]:
> > > https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
> > > > [3]:
> > > >
> > >
> https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-optimization-and-tuning.html#GUID-F8E13DDD-710D-4626-824E-B344849C5AFE
> > > > [4]:
> > > >
> > >
> https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/
> > > > [5]:
> > > >
> > >
> http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-145-Support-SQL-windowing-table-valued-function-tp45269p45665.html
> > >
>

Re: [DISCUSS] Support Oracle Polymorphic Table Function Syntax

Posted by Julian Hyde <jh...@apache.org>.

I think we can do them separately. And I think we should do the TABLE
first. The biggest problem is with namespaces - if you omit TABLE, you
have to deal with the possibility that there is a table (or view)
called FOO and also a parameterless table function called FOO. Not
sure how Oracle and SQL Server resolve this.

Let's not use TUMBLE (or HOP, or SESSION) as the main example. It is
somewhat built-in (i.e. has special treatment in the code). Let's work
in terms of, say, the RAMP user-defined function. It is used in
several tests [1].

Julian

[1] https://github.com/apache/calcite/blob/ffc1e3b05e7f920d95c48f7c75fd48372684b8e7/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java#L361

On Thu, Oct 29, 2020 at 1:53 PM Rui Wang <am...@apache.org> wrote:
>
> In terms of SQL grammar to support omitting TABLE, there are actually two
> changes for
>
> SELECT *
> FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES));
>
> You can support omitting the TABLE after the FROM, which makes the query
> become:
> SELECT *
> FROM TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
>
> Another is omitting TABLE from the parameter, which is
> SELECT *
> FROM TUMBLE(Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);
>
> Do we want to achieve both or just one of these? If for one the grammar is
> too complicated to change but for another the grammar is easier to be
> changed, are we ok to only have one keyword omitted?
>
> -Rui
>
> On Thu, Oct 29, 2020 at 11:28 AM Julian Hyde <jh...@apache.org> wrote:
>
> > Can we drop the word "polymorphic" from the discussion? Polymorphic
> > table functions are a valid ask, but can be a separate discussion.
> > This is about calling table functions without the TABLE keyword,
> > right?
> >
> > Which is what I said to you four years ago:
> > https://issues.apache.org/jira/browse/CALCITE-1472#comment-15664799
> >
> > In other words: let's fix
> > https://issues.apache.org/jira/browse/CALCITE-1490.
> >
> > Julian
> >
> > On Thu, Oct 29, 2020 at 2:26 AM Jark Wu <im...@gmail.com> wrote:
> > >
> > > Hi all,
> > >
> > > It's great to see Calcite already supports PTF syntax and support the
> > > out-of-box new window syntax.
> > >
> > > SELECT *
> > > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> > MINUTES));
> > >
> > > However, some people from the Flink community think that the TABLE()
> > > keyword is a little verbose for users [5].
> > > I have seen the discussion in the previous mailing list [1], and know the
> > > TABLE() keyword is mandatory in SQL standard paper.
> > >
> > > But it seems that other databases are not following the standard, I find
> > > that Polymorphic Table Function syntax
> > >  in Oracle is more concise without the TABLE() keywords [2][3], e.g.
> > >
> > > SELECT *
> > > FROM skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
> > >
> > > Besides, SQL Server also seems to support the non TABLE() syntax for
> > > table-valued functions [4].
> > > If we can support the Oracle syntax, it would be more consistent with
> > > existing TUMBLE functions
> > > and hence easier to be picked up by users.
> > >
> > > The new window TVF syntax can be:
> > >
> > > SELECT window_start, count(*)
> > > FROM TUMBLE(Bid, COLUMNS(bidtime), INTERVAL '10' MINUTES))
> > > GROUP BY window_start;
> > >
> > > Which is more similar to the existing group window functions:
> > >
> > > SELECT TUMBLE_START(bidtime, INTERVAL '10' MINUTES), count(*)
> > > FROM Bid
> > > GROUP BY TUMBLE(bidtime, INTERVAL '10' MINUTES);
> > >
> > > I am fairly inexperienced with the parsing and validation logic in
> > Calcite,
> > > so I don't know whether the new syntax can be supported in Calcite.
> > >
> > > What do you think?
> > >
> > > Best,
> > > Jark
> > >
> > > [1]:
> > >
> > https://lists.apache.org/x/thread.html/4a91632b1c780ef9d67311f90fce626582faae7d30a134a768c3d324@%3Cdev.calcite.apache.org%3E
> > > [2]:
> > https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
> > > [3]:
> > >
> > https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-optimization-and-tuning.html#GUID-F8E13DDD-710D-4626-824E-B344849C5AFE
> > > [4]:
> > >
> > https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/
> > > [5]:
> > >
> > http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-145-Support-SQL-windowing-table-valued-function-tp45269p45665.html
> >

Re: [DISCUSS] Support Oracle Polymorphic Table Function Syntax

Posted by Rui Wang <am...@apache.org>.

In terms of SQL grammar to support omitting TABLE, there are actually two
changes for

SELECT *
FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES));

You can support omitting the TABLE after the FROM, which makes the query
become:
SELECT *
FROM TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);

Another is omitting TABLE from the parameter, which is
SELECT *
FROM TUMBLE(Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES);

Do we want to achieve both or just one of these? If for one the grammar is
too complicated to change but for another the grammar is easier to be
changed, are we ok to only have one keyword omitted?

-Rui

On Thu, Oct 29, 2020 at 11:28 AM Julian Hyde <jh...@apache.org> wrote:

> Can we drop the word "polymorphic" from the discussion? Polymorphic
> table functions are a valid ask, but can be a separate discussion.
> This is about calling table functions without the TABLE keyword,
> right?
>
> Which is what I said to you four years ago:
> https://issues.apache.org/jira/browse/CALCITE-1472#comment-15664799
>
> In other words: let's fix
> https://issues.apache.org/jira/browse/CALCITE-1490.
>
> Julian
>
> On Thu, Oct 29, 2020 at 2:26 AM Jark Wu <im...@gmail.com> wrote:
> >
> > Hi all,
> >
> > It's great to see Calcite already supports PTF syntax and support the
> > out-of-box new window syntax.
> >
> > SELECT *
> > FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10'
> MINUTES));
> >
> > However, some people from the Flink community think that the TABLE()
> > keyword is a little verbose for users [5].
> > I have seen the discussion in the previous mailing list [1], and know the
> > TABLE() keyword is mandatory in SQL standard paper.
> >
> > But it seems that other databases are not following the standard, I find
> > that Polymorphic Table Function syntax
> >  in Oracle is more concise without the TABLE() keywords [2][3], e.g.
> >
> > SELECT *
> > FROM skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
> >
> > Besides, SQL Server also seems to support the non TABLE() syntax for
> > table-valued functions [4].
> > If we can support the Oracle syntax, it would be more consistent with
> > existing TUMBLE functions
> > and hence easier to be picked up by users.
> >
> > The new window TVF syntax can be:
> >
> > SELECT window_start, count(*)
> > FROM TUMBLE(Bid, COLUMNS(bidtime), INTERVAL '10' MINUTES))
> > GROUP BY window_start;
> >
> > Which is more similar to the existing group window functions:
> >
> > SELECT TUMBLE_START(bidtime, INTERVAL '10' MINUTES), count(*)
> > FROM Bid
> > GROUP BY TUMBLE(bidtime, INTERVAL '10' MINUTES);
> >
> > I am fairly inexperienced with the parsing and validation logic in
> Calcite,
> > so I don't know whether the new syntax can be supported in Calcite.
> >
> > What do you think?
> >
> > Best,
> > Jark
> >
> > [1]:
> >
> https://lists.apache.org/x/thread.html/4a91632b1c780ef9d67311f90fce626582faae7d30a134a768c3d324@%3Cdev.calcite.apache.org%3E
> > [2]:
> https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
> > [3]:
> >
> https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-optimization-and-tuning.html#GUID-F8E13DDD-710D-4626-824E-B344849C5AFE
> > [4]:
> >
> https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/
> > [5]:
> >
> http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-145-Support-SQL-windowing-table-valued-function-tp45269p45665.html
>

Re: [DISCUSS] Support Oracle Polymorphic Table Function Syntax

Posted by Julian Hyde <jh...@apache.org>.

Can we drop the word "polymorphic" from the discussion? Polymorphic
table functions are a valid ask, but can be a separate discussion.
This is about calling table functions without the TABLE keyword,
right?

Which is what I said to you four years ago:
https://issues.apache.org/jira/browse/CALCITE-1472#comment-15664799

In other words: let's fix https://issues.apache.org/jira/browse/CALCITE-1490.

Julian

On Thu, Oct 29, 2020 at 2:26 AM Jark Wu <im...@gmail.com> wrote:
>
> Hi all,
>
> It's great to see Calcite already supports PTF syntax and support the
> out-of-box new window syntax.
>
> SELECT *
> FROM TABLE(TUMBLE(TABLE Bid, DESCRIPTOR(bidtime), INTERVAL '10' MINUTES));
>
> However, some people from the Flink community think that the TABLE()
> keyword is a little verbose for users [5].
> I have seen the discussion in the previous mailing list [1], and know the
> TABLE() keyword is mandatory in SQL standard paper.
>
> But it seems that other databases are not following the standard, I find
> that Polymorphic Table Function syntax
>  in Oracle is more concise without the TABLE() keywords [2][3], e.g.
>
> SELECT *
> FROM skip_col(scott.emp, COLUMNS(comm, hiredate, mgr))
>
> Besides, SQL Server also seems to support the non TABLE() syntax for
> table-valued functions [4].
> If we can support the Oracle syntax, it would be more consistent with
> existing TUMBLE functions
> and hence easier to be picked up by users.
>
> The new window TVF syntax can be:
>
> SELECT window_start, count(*)
> FROM TUMBLE(Bid, COLUMNS(bidtime), INTERVAL '10' MINUTES))
> GROUP BY window_start;
>
> Which is more similar to the existing group window functions:
>
> SELECT TUMBLE_START(bidtime, INTERVAL '10' MINUTES), count(*)
> FROM Bid
> GROUP BY TUMBLE(bidtime, INTERVAL '10' MINUTES);
>
> I am fairly inexperienced with the parsing and validation logic in Calcite,
> so I don't know whether the new syntax can be supported in Calcite.
>
> What do you think?
>
> Best,
> Jark
>
> [1]:
> https://lists.apache.org/x/thread.html/4a91632b1c780ef9d67311f90fce626582faae7d30a134a768c3d324@%3Cdev.calcite.apache.org%3E
> [2]: https://oracle-base.com/articles/18c/polymorphic-table-functions-18c
> [3]:
> https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-optimization-and-tuning.html#GUID-F8E13DDD-710D-4626-824E-B344849C5AFE
> [4]:
> https://www.sqlservertutorial.net/sql-server-user-defined-functions/sql-server-table-valued-functions/
> [5]:
> http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-FLIP-145-Support-SQL-windowing-table-valued-function-tp45269p45665.html