On Sat, Aug 22, 2015 at 02:10:58AM +0530, Diwas Joshi wrote:

Hello Sergei, sorry for late reply, i was busy finishing my office work yesterday. Please find some of my doubts below (I would come on irc to discuss but I am travelling today and will be working overnight in the train, I hope to be on irc tomorrow).

Regarding use of storage engine-- If one uses SET default_tmp_storage_engine, to set the default storage engine for temporary tables in the current session, it is using that default storage engine. In my function I have used create_info.use_default_db_type(thd) which does the work. (Referencing https://dev.mysql.com/doc/refman/5.6/en/storage-engine-setting.html)

Regarding the name resolution error. I didn't know it but if there is already a table present in the database with same name, temporary table is still being created and used int the function. so that is one problem solved. For the problem of a function being used multiple times in a query, I was just curious as how would we solve it if we switch to create_tmp_table().

As I mentioned before, the solution should be:

...

- Patch the name resolution code so that the statements inside a Table Function see the return table of that Table Function.

"Name resolution" is a process of finding which table should be used when a query refers to table named TBL. The process currently is: 1. Look at user-created temporary tables with name TBL. 2. If none found, look at the regular database tables in the current database with the name TBL. Our problem is that tables created with mysql_create_table() are visible to step#1, and all parts of the query can see them. This includes those parts of the query that we don't want to see the table: other stored procedures, the query that invokes the table function, etc. Temporary tables that are created with create_tmp_table() are not visible to step #1. We will not have the problem of temporary table being visible where it should not be visible. We will have the problem though, that temporary table must be visible where it needs to be visible. This is achieved by modifying step#1 in Name Resolution code to also look at the name of the returned table when we are in a table function.

Still looking into crash because of select name from f21('aaa') where name like '%foo%' and errors MDEV-8649 and MDEV-8650; Other errors in the patch have been fixed.

BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog