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
(5 links to)
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.1.0 [ 12200 ] |
| Summary | UDF returning arrays / result set | UDF returning arrays / result set (a.k.a. table UDF) |
| Labels | gsoc14 |
| Priority | Major [ 3 ] | Minor [ 4 ] |
| Workflow | defaullt [ 29500 ] | MariaDB v2 [ 42754 ] |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.1.0 [ 12200 ] |
| Labels | gsoc14 | gsoc14 gsoc15 |
| Fix Version/s | 10.1 [ 16100 ] |
| Description | Having the possibility that UDFs can return arrays / result sets would be awesome. I am missing this functionality often and working around it is not always a user friendly option. |
User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example {code:sql} SELECT * FROM t1, tf(15,"foobar") AS t2 WHERE t1.a=t2.b {code} We might (or might not) specify that all function arguments must be constants. |
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.
| Remote Link | This issue links to "See also: “Table functions in MySQL” (Web Link)" [ 22707 ] |
| Summary | UDF returning arrays / result set (a.k.a. table UDF) | Table functions (a.k.a UDF returning arrays / result set) |
| Description |
User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example {code:sql} SELECT * FROM t1, tf(15,"foobar") AS t2 WHERE t1.a=t2.b {code} We might (or might not) specify that all function arguments must be constants. |
h2. Idea User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example {code:sql} SELECT * FROM t1, tf(15,"foobar") AS t2 WHERE t1.a=t2.b {code} We might (or might not) specify that all function arguments must be constants. h2. SQL Standard says _this is my understanding of the standard. it might be wrong_ To define a table function one uses {code:sql} CREATE FUNCTION ... RETURNS TABLE (column type [, column type ...]) {code} It should return a table expression, which pretty much means either {code:sql} RETURN SELECT ... {code} or {code:sql} CREATE LOCAL TEMPORARY TABLE t1 (...) INSERT t1 VALUES (...) RETURN t1; {code} and it's used as {code:sql} SELECT ... FROM TABLE(func(arg1, arg2, ...)) ... {code} |
| Description |
h2. Idea User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example {code:sql} SELECT * FROM t1, tf(15,"foobar") AS t2 WHERE t1.a=t2.b {code} We might (or might not) specify that all function arguments must be constants. h2. SQL Standard says _this is my understanding of the standard. it might be wrong_ To define a table function one uses {code:sql} CREATE FUNCTION ... RETURNS TABLE (column type [, column type ...]) {code} It should return a table expression, which pretty much means either {code:sql} RETURN SELECT ... {code} or {code:sql} CREATE LOCAL TEMPORARY TABLE t1 (...) INSERT t1 VALUES (...) RETURN t1; {code} and it's used as {code:sql} SELECT ... FROM TABLE(func(arg1, arg2, ...)) ... {code} |
h2. Idea User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example {code:sql} SELECT * FROM t1, tf(15,"foobar") AS t2 WHERE t1.a=t2.b {code} We might (or might not) specify that all function arguments must be constants. h2. SQL Standard says _this is my understanding of the standard. it might be wrong_ To define a table function one uses {code:sql} CREATE FUNCTION ... RETURNS TABLE (column type [, column type ...]) {code} It should return a table expression, which pretty much means either {code:sql} RETURN SELECT ... {code} or {code:sql} CREATE LOCAL TEMPORARY TABLE t1 (...) INSERT t1 VALUES (...) RETURN t1; {code} and it's used as {code:sql} SELECT ... FROM TABLE(func(arg1, arg2, ...)) ... {code} h2. 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 * it uses exactly the second pattern above. CREATE/INSERT/RETURN. * CREATE and RETURN are not conditional, every inserted row is guaranteed to be returned. * No DELETE, no UPDATE, no TRUNCATE, no ALTER, etc. |
| Description |
h2. Idea User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example {code:sql} SELECT * FROM t1, tf(15,"foobar") AS t2 WHERE t1.a=t2.b {code} We might (or might not) specify that all function arguments must be constants. h2. SQL Standard says _this is my understanding of the standard. it might be wrong_ To define a table function one uses {code:sql} CREATE FUNCTION ... RETURNS TABLE (column type [, column type ...]) {code} It should return a table expression, which pretty much means either {code:sql} RETURN SELECT ... {code} or {code:sql} CREATE LOCAL TEMPORARY TABLE t1 (...) INSERT t1 VALUES (...) RETURN t1; {code} and it's used as {code:sql} SELECT ... FROM TABLE(func(arg1, arg2, ...)) ... {code} h2. 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 * it uses exactly the second pattern above. CREATE/INSERT/RETURN. * CREATE and RETURN are not conditional, every inserted row is guaranteed to be returned. * No DELETE, no UPDATE, no TRUNCATE, no ALTER, etc. |
h2. Idea User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example {code:sql} SELECT * FROM t1, tf(15,"foobar") AS t2 WHERE t1.a=t2.b {code} We might (or might not) specify that all function arguments must be constants. h2. UDF * It won't be a UDF, of course, but a new plugin type. * it'll use a generator interface h2. SQL h3. SQL Standard says _this is my understanding of the standard. it might be wrong_ To define a table function one uses {code:sql} CREATE FUNCTION ... RETURNS TABLE (column type [, column type ...]) {code} It should return a table expression, which pretty much means either {code:sql} RETURN SELECT ... {code} or {code:sql} CREATE LOCAL TEMPORARY TABLE t1 (...) INSERT t1 VALUES (...) RETURN t1; {code} and it's used as {code:sql} SELECT ... FROM TABLE(func(arg1, arg2, ...)) ... {code} h3. 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 * it uses exactly the second pattern above. CREATE/INSERT/RETURN. * CREATE and RETURN are not conditional, every inserted row is guaranteed to be returned. * No DELETE, no UPDATE, no TRUNCATE, no ALTER, etc. |
| Remote Link | This issue links to "Table Functions in SQL Server (Web Link)" [ 23102 ] |
| Remote Link | This issue links to "Table Functions in Oracle (Web Link)" [ 23103 ] |
| Remote Link | This issue links to "Table Functions in DB2 (Web Link)" [ 23104 ] |
| Remote Link | This issue links to "Table Functions in PostgreSQL (Web Link)" [ 23105 ] |
| Description |
h2. Idea User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example {code:sql} SELECT * FROM t1, tf(15,"foobar") AS t2 WHERE t1.a=t2.b {code} We might (or might not) specify that all function arguments must be constants. h2. UDF * It won't be a UDF, of course, but a new plugin type. * it'll use a generator interface h2. SQL h3. SQL Standard says _this is my understanding of the standard. it might be wrong_ To define a table function one uses {code:sql} CREATE FUNCTION ... RETURNS TABLE (column type [, column type ...]) {code} It should return a table expression, which pretty much means either {code:sql} RETURN SELECT ... {code} or {code:sql} CREATE LOCAL TEMPORARY TABLE t1 (...) INSERT t1 VALUES (...) RETURN t1; {code} and it's used as {code:sql} SELECT ... FROM TABLE(func(arg1, arg2, ...)) ... {code} h3. 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 * it uses exactly the second pattern above. CREATE/INSERT/RETURN. * CREATE and RETURN are not conditional, every inserted row is guaranteed to be returned. * No DELETE, no UPDATE, no TRUNCATE, no ALTER, etc. |
h2. Idea User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example {code:sql} SELECT * FROM t1, tf(15,"foobar") AS t2 WHERE t1.a=t2.b {code} We might (or might not) specify that all function arguments must be constants. h2. UDF * It won't be a UDF, of course, but a new plugin type. * it'll use a generator interface h2. SQL h3. SQL Standard says _this is my understanding of the standard. it might be wrong_ To define a table function one uses {code:sql} CREATE FUNCTION ... RETURNS TABLE (column type [, column type ...]) {code} It should return a table expression, which pretty much means either {code:sql} RETURN SELECT ... {code} or {code:sql} CREATE LOCAL TEMPORARY TABLE t1 (...) INSERT t1 VALUES (...) RETURN t1; {code} and it's used as {code:sql} SELECT ... FROM TABLE(func(arg1, arg2, ...)) ... {code} h3. 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 |
== 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.
| Assignee | Sergei Golubchik [ serg ] |
| Workflow | MariaDB v2 [ 42754 ] | MariaDB v3 [ 64666 ] |
| Labels | gsoc14 gsoc15 | gsoc14 gsoc15 gsoc16 |
| Labels | gsoc14 gsoc15 gsoc16 | gsoc14 gsoc15 |
| Workflow | MariaDB v3 [ 64666 ] | MariaDB v4 [ 130158 ] |
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.
| Link | This issue relates to MDEV-15457 [ MDEV-15457 ] |
| Link | This issue relates to MDEV-15457 [ MDEV-15457 ] |
| Link | This issue relates to MDEV-15457 [ MDEV-15457 ] |
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.