[Tarantool-discussions] The result type and argument types of the built-in SQL functions.

[Mergen Imeev]

Hi, Peter!

I would like to ask you a few questions about the result type and argument
types of the SQL built-in functions.

I suggest changing the result types of some functions. A table with the current
result type and the suggested result type is below.

FUNCTION NAME		CURRENT		SUGGESTED
abs			number		number
avg			number		double
char			string		string
character_length	integer		unsigned
char_length		integer		unsigned
coalesce		scalar		scalar
count			integer		unsigned
greatest		scalar		scalar
group_concat		string		string
hex			string		string
ifnull			integer		scalar
least			scalar		scalar
length			integer		unsigned
like			integer		boolean
likelihood		boolean		scalar
likely			boolean		scalar
lower			string		string
max			scalar		scalar
min			scalar		scalar
nullif			scalar		scalar
position		integer		unsigned
printf			string		string
quote			string		string
random			integer		integer
randomblob		varbinary	varbinary
replace			string		string
round			integer		number
row_count		integer		unsigned
soundex			string		string
substr			string		string
sum			number		number
total			number		double
trim			string		string
typeof			string		string
unicode			string		unsigned
unlikely		boolean		scalar
upper			string		string
version			string		string
zeroblob		varbinary	varbinary


The second question is about the types of arguments to built-in functions.

I suggest this:

FUNCTION NAME		TYPES OF ARGUMENTS
abs			number
avg			number
char*			unsigned
character_length	string
char_length		string
coalesce		scalar
count			scalar
greatest		scalar
group_concat		scalar, scalar
hex			scalar
ifnull			scalar, scalar
least*			scalar
length			string
like			string, string, string
likelihood		scalar, double
likely			scalar
lower			string
max			scalar
min			scalar
nullif			scalar, scalar
position		string, string
printf*			scalar
quote			scalar
randomblob		unsigned
replace			string, string, string
round			double, unsigned
soundex			string
substr			string, integer, integer
sum			number
total			number
trim*			string
typeof			scalar
unicode			string
unlikely		scalar
upper			string
zeroblob		unsigned

* - all arguments must be of this type.


Also, we have to decide on BLOB instead of STRING. Last time you wrote that we
should allow BLOB instead of STRING, but I think it will be rather inconvenient,
because in this case we have to write SCALAR instead of STRING in function
definition and check the type of the argument inside the function. Because of
this, it will be a little incompatible with the definition of a function that
will be placed in the '_func' system space. I mean, the definition will state
that it accepts SCALAR, but in reality it will only accept STRING and BLOB.

So, I think we should disallow BLOB instead of STRING, or decide in which
functions we allow BLOB instead of STRING.


And one more question. I think we are going to add MAP and ARRAY types in SQL in
the near future, so it might be a good idea to write ANY instead of SCALAR for
some of these functions. What do you think about this?