maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Hi,

Right, my point was that UDF (classical UDF) really can't do arrays or table functions, you need a different type of UDF, and external procs are one type of "new udf".  The other type of "new UDF" I would like to see my s Antony's other patch I linked to, which adds "pluggable item functions".  Such functions could also use the same table function interface (be it via generator or temp table, or whatever).  The biggest advantage over classic UDF is that pluggable item functions  get a THD, can use SQL security, properly deal with character sets, etc, and it is a small patch.

I'd kind of like two or three type of table func:

One returns  a SQL statement and is evaluated like a dependent subquery - it doesn't have to be materialized, but if it is, it can be indexed.  This is like a "dynamic view" which is difficult to do without ugly hacks.  His would be the easiest to implement.

Another type would returns a temporary table name, and the temp table is used in the query.  These would be evaluated at start of execution like old dependent subqueries.  This would allow table functions with a varying number of columns, or that essentially can return more than one data type based in the input.  While conceptually similar to the first type, this type can get data from external sources and store it in the table. The temporary would be dropped in deinit() function.

Finally, generator functions would use fixed schema and could return big data sets, but they probably can't open any tables without locking problems (all table locks must be taken at query execution start).

I hope that makes sense.

Sent from my iPhone

> On Mar 11, 2015, at 7:35 AM, Sergei Golubchik <serg@xxxxxxxxxxx> wrote:
> 
> Hi, Justin!
> 
>> On Mar 10, Justin Swanhart wrote:
>> wl#820 also has table functions in it:
>> http://antbits.blogspot.com/2009/01/table-functions-in-mysql.html
>> 
>> mysql> INSTALL PLUGIN Deep_Thought SONAME 'psm_example.so';
>> mysql> CREATE FUNCTION test.FooAnswer()
>>    -> RETURNS TABLE(answer TEXT)
>>    -> NO SQL LANGUAGE Deep_Thought EXTERNAL NAME 'compute';
>> mysql> CREATE VIEW foobar AS SELECT * FROM TABLE(test.FooAnswer) AS wibble;
>> mysql> SELECT * FROM foobar WHERE answer LIKE 'F%';
>> +-----------+
>> | answer    |
>> +-----------+
>> | Forty-Two |
>> +-----------+
> 
> Ah, indeed. Thanks.
> 
> It's still an unrelated feature - I mean, there is no reason why a table
> function must necessarily be impemented as an external procedure.
> 
> And the implementation - I believe - creates a temporary table and puts
> all the data into it. I would rather prefer a generator-like interface,
> that produces rows one by one. It works much better for big data sets.
> Or, may be, we can do both.
> 
> Either way - this implementation can be very helpful. But it'll need at
> least to be updated to work in 10.1. And may be we add the generator
> interface.
> 
> Regards,
> Sergei
>