Table functions (aka SQL functions returning tables)

This is a part of MDEV-5199 (Table functions) which addresses table functions that are defined in SQL (as opposed to table functions that are implemented in C++ and loaded as .so/.dlls).

This task is a GSOC '15 project of dj not anymore

Proposed syntax

(note: the following differs from description in MDEV-5199, and this is the latest text).

We will use SQL Server's variant of syntax, where CREATE FUNCTION explicitly mentions function name. To define a table function one will use:

CREATE FUNCTION func_name (args)  RETURNS TABLE table_name (column type [, column type ...])

Inside function body, table table_name is visible as a temporary table. The table is initially empty. The code of the function is expected to insert rows into table_name. When the function finishes, the table will be returned to outside as the function's result.

Implementation overview

Table function will create and populate a temporary table (either HEAP or Aria/MyISAM, just like it is done for other kinds of temporary tables). Once the table function has finished populating the temporary table, it will be returned to the statement that invoked the table function. The caller statement will then read the table.

In other words, we will not support any kind of "streaming" for results of table function.

A possible optimization

Trivial functions (with the body of a single INSERT ... SELECT) can be treated as a VIEW. It is not clear whether this should be implemented in the scope of this WL entry.

Other details

Constant parameters

Table functions must have constant parameters. One can't use things like

select * from tbl1,  table_func(tbl1.column) where ...

This is because our implementation will pre-populate table function tables once per query execution. MySQL/MariaDB optimizer doesn't support tables that may change their contents during query execution.

Parser

The parser should support all proposed syntax.
note: the table can only be addressed through an alias. This syntax is not
allowed:

SELECT table_func(1,2).column FROM table_func(1,2)

one must use

SELECT T_ALIAS.column FROM table_func(1,2) as T_ALIAS

Name resolution/preparation in the upper query

When we find a table reference in the FROM clause, we should make
a lookup in mysql.proc, make appropriate checks, then open and parse
the function.
We should be able to take the "RETURNS ...." statement and produce a TABLE
object from it. This is needed for doing name resolution on the other parts
of the "caller" query .

Name resolution/preparation in the Stored Function

When one defines a stored function:

CREATE FUNCTION func_name (args)  RETURNS TABLE table_name (column type [, column type ...])

BEGIN

   stmt1;

   stmt2;

END

All statements inside the body of the function (i.e. stmt1, stmt2, etc) must use a modified name resolution process where table table_name resolves to the table that will be returned by the function. This may shadow the real table with name table_name.

mysql.proc contents

How does table functions should be stored in mysql.proc table. Are they a special kind of

Execution

• "SELECT sp_func()" should return error for table functions