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 <danny0...@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 <danny0...@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 <amaliu...@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 <imj...@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 <danny0...@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 < > amaliu...@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 < > imj...@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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
