Details
-
Task
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
-
None
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
Attachments
Issue Links
- blocks
-
-
- Open
-
-
-
- Open
-
- relates to
-
-
- Stalled
-
-
MDEV-15457 Pipe function technique
-
- Stalled
-
-
-
- Open
-
- links to
Activity
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.
== 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*?)
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.
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.
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.