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