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. |
| 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 |
| 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 ] |
| 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 ] |