[MDEV-5199] Table functions (a.k.a UDF returning arrays / result set) Created: 2013-10-28  Updated: 2023-03-27

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Adrian Partl Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 12
Labels: gsoc14, gsoc15

Issue Links:
Blocks
blocks MDEV-7417 Implement unnest feature Open
blocks MDEV-7994 Needed SQL statement which converted ... Open
Relates
relates to MDEV-8100 Table functions (aka SQL functions re... Stalled
relates to MDEV-7535 query the metadata of a dynamic column Open

 Description   

Idea

User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example

SELECT * FROM t1, tf(15,"foobar") AS t2 WHERE t1.a=t2.b

We might (or might not) specify that all function arguments must be constants.

UDF

  • It won't be a UDF, of course, but a new plugin type.
  • it'll use a generator interface

SQL

SQL Standard says

this is my understanding of the standard. it might be wrong

To define a table function one uses

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

It should return a table expression, which pretty much means either

  RETURN SELECT ...

or

  CREATE LOCAL TEMPORARY TABLE t1 (...)
 
  INSERT t1 VALUES (...)
 
  RETURN t1;

and it's used as

SELECT ... FROM TABLE(func(arg1, arg2, ...)) ...

Optimizations

Trivial function (with the body of only RETURN SELECT) can be treated as view. But I'm not sure it's a use case worth optimizing.

Complex function can be changed from materializing to streaming if we can be sure that every inserted row is guaranteed to be returned. That is:

  • it uses exactly the second pattern above. CREATE/INSERT/RETURN.
  • CREATE and RETURN are not conditional.
  • No DELETE, no UPDATE, no TRUNCATE, no ALTER, etc.
  • No dynamic SQL

 Comments   
Comment by Sergei Golubchik [ 2015-02-07 ]

A possible implementation could be: the UDF exports a generator, we create a handler of the hidden "storage engine" class, no indexes, and convert this generator to rnd_init/rnd_next. Need to disable rnd_pos somehow.

Comment by kapil chhajer [ 2015-03-11 ]

Hi,
I am Kapil Chhajer student from IIIT Hyderabad pursuing M.tech. CSE. I am comfortable with c, c++ and java. From Database perspective , I developed Simple Database engine in java as academic project. It seems "UDFs returning arrays" as interesting one but I am not getting completely.
Please explain it or give me some related material link so I can understand.

Comment by Sergei Petrunia [ 2015-04-22 ]

== General notes ==
Table functions must have constant parameters. This is because the table
is pre-populated once per query. Our optimizer doesn't support tables that
may change their contents depending on some parameters.

== 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 ==

  • When we find a table reference in the FROM clause, we should make
    a lookup in mysql.proc, make appopriate 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 query .

== Execution ==

  • "SELECT sp_func()" should return error for table functions
  • "RETURN table" should check whether the returned table has the same datatype
    as in the function declaration (Q: do we do it statically or dynamically?)
  • CREATE LOCAL TEMPORARY TABLE should shadow any other table with the same
    name. Name resolution for all subsequent statements should find this table
    first. Like regular CREATE TEMPORARY TABLE, it creates a table in the current
    database.

(Q: what exactly does LOCAL mean? That the table is DROPped when leaving the
function?)

  • "RETURN table" should transfer the table to outside of the function. (this
    is not totally trivial. According to , the upper query will create an early TABLE
    and run name resolution on one TABLE object, and then the SP will create/fill/return
    another TABLE/handler* object. Can we make a clean replacement? (e.g. take a ha_heap
    and change its TABLE*?)
Comment by Sergei Petrunia [ 2015-05-04 ]

Decisions:

  • We will use SQL-server variant of the syntax. Here, CREATE LOCAL TEMPORARY TABLE won't be necessary.
  • SQL table functions will be worked on in MDEV-8100. This MDEV will only cover UDF table functions.
Comment by Rafsun Masud [ 2023-03-27 ]

Any update on this task?

Comment by Daniel Black [ 2023-03-27 ]

Keep voting or watching this issue and it will eventually raise to the visibility to implement; or engage in development of the task; or fund the development https://mariadb.com, or a community lead funding of it.

Generated at Thu Feb 08 07:02:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.