Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8100

Table functions (aka SQL functions returning tables)

    XMLWordPrintable

    Details

      Description

      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 diwas joshi 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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              psergey Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              10 Start watching this issue

                Dates

                Created:
                Updated: