drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Julien Le Dem <jul...@dremio.com>
Subject Re: select from table with options
Date Fri, 06 Nov 2015 22:20:31 GMT
- Table function syntax: I did a quick search and it seems there's no
consensus about this.
It seems that Posgres [1] and SQL Server [2] both allow calling table
functions without the table(...) wrapper while Oracle [3] and DB2 [4]
expect it.
MySQL does not have table functions [5]
2 for, 2 against and 1 undecided: that's a draw :)
Would it be reasonable to allow a switch in the grammar generation to have
a posgres compatible syntax? Currently in Drill we use the MySQL like
syntax (back ticks for identifiers etc)

[1] http://www.postgresql.org/docs/7.3/static/xfunc-tablefunctions.html
[2] https://technet.microsoft.com/en-us/library/aa214485(v=sql.80).aspx
[3] https://oracle-base.com/articles/misc/pipelined-table-functions
[4] http://www.ibm.com/developerworks/ibmi/library/i-power-of-udtf/
[5]
http://stackoverflow.com/questions/12163666/mysql-function-to-return-a-table

- It seems a simple change in SqlCallBinding fixes the function
overloading: https://github.com/apache/calcite/pull/166/files
But that seems too easy to be true. Possibly this method is called more
than once (before and after the function has been resolved?)

FYI this would happen only when using named parameter. We do want to
overload in this case, which is why I'm looking into it.

I'll fill a JIRA for my other branch

Julien

On Thu, Nov 5, 2015 at 5:39 PM, Julian Hyde <jhyde@apache.org> wrote:

>
> On Nov 5, 2015, at 5:00 PM, Julien Le Dem <julien@dremio.com> wrote:
>
> TL;DR: TableMacro works for me; I need help with a bug in Calcite when
> there's more than 1 function with the same name.
>
>
> Yes; see below.
>
> FYI: I have a prototype of TableMacro working in Drill. For now just being
> able to specify the delimiter for csv files.
> So it seem the answer to my question 1) is that TableMacros are the way to
> go.
> I'm still wondering about *3) is the table(...) wrapping syntax
> necessary?*
>
>
> Consider:
>
> select * from myTable as f(x, y)
> select * from myTable f(x, y)
> select * from myFunction(x, y)
>
> #1 and #2 mean the same thing; #2 and #3 look awfully similar. Also, if f
> is a function with zero arguments, could you invoke it like this?:
>
> select * from f
>
> I don’t know the actual rationale. But I know that the SQL standards
> people in their wisdom decided to add a keyword to disambiguate.
>
> I had to fix some things in Calcite to enable this:
> https://github.com/dremio/calcite/pull/1/files
> Drill uses Frameworks.getPlanner() that does not seem to be used in
> Calcite for the Maze example.
> Which is why some hooks were missing.
>
>
> Can you log a jira case to track this bug?
>
>
> I think I found a bug in Calcite but I'd need help to fix it.
> Here is a test that reproduces the problem:
> https://github.com/apache/calcite/pull/166
> If we return more than 1 TableFunction with the same name, we get a NPE
> later on.
>
>
> Yes, I knew there was a problem with overloading. Please log a JIRA case
> on resolution of overloaded functions when invoked with named arguments.
> (It probably applies to all functions, not just table functions.) The fix
> will take a while (if you wait for me to write it).
>
> For now please tell your users not to overload. :)
>
>
> Julian
>
>


-- 
Julien

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message