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 <[email protected]> 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 <[email protected]> 于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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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
> > > >
> >
>
