[Tarantool-discussions] SQL built-in functions position

[Peter Gulutzan]

Hi,

On 2020-09-27 9:18 a.m., Mergen Imeev wrote:
 > Hi all. I have a question that I would like to discuss.
 >
 > The question is about SQL built-in functions. At the moment these 
functions are
 > partially described in _func and partially in src/box/sql/func.c. I 
received two
 > completely different suggestions from my reviewers on what to do with 
these
 > functions:
 > 1) Move definitions completely to _func. Remove definitions from func.c.
 > 2) Move definitions completely to func.c. Remove definitions from _func.
 >
 > In the first case, users will be able to see the function 
definitions. Also, in
 > the future, we may allow these functions to be called from Lua 
(although not
 > sure if this is necessary). The main idea is 'all functions have the same
 > interface'.
 >
 > In the second case, the implementation is simpler, and we can more easily
 > implement some features, such as "virtual" functions. For users, the 
definition
 > can only be seen in the documentation. The main idea is 'SQL built-in 
functions
 > are part of SQL'.
 >
 > Which of these approaches do you think is more beneficial to us?
 >

I hope you will say _func.

The current built-in functions are harmless, except perhaps for 
RANDOMBLOB with a huge value.
However, in future maybe there will be built-in functions that should 
require privileges.
In that case, I believe, they will have to be in _func (and someday in 
_vfunc)
so that grant() will work for them.

I have tried to redirect the UPPER() function in order to violate 
security, thus:
"
tarantool> function UPPER(x) return x end
---
...
tarantool> box.schema.func.create('UPPER')
---
- error: Function 'UPPER' already exists
...
tarantool> box.schema.func.drop('UPPER')
---
- error: 'Can''t drop function 1: function is SQL built-in'
...
"
This is good behaviour and I think it works because UPPER() is in _func.

I did not document in the manual's SQL section that built-in functions will
be in _func, so removing them is not a regression from documented behaviour.
Also I acknowledge that they don't exist in MySQL/MariaDB
information_schema.routines.
But I think users benefit from being able to see them.


Peter Gulutzan