[Tarantool-discussions] The result type and argument types of the built-in SQL functions.
pgulutzan at ocelot.ca
Mon Jul 27 22:39:29 MSK 2020
On 2020-07-27 6:24 a.m., Mergen Imeev wrote:
> Hi, Peter!
> I would like to ask you a few questions about the result type and
> types of the SQL built-in functions.
> I suggest changing the result types of some functions. A table with
> 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
> 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
> because in this case we have to write SCALAR instead of STRING in
> definition and check the type of the argument inside the function.
> this, it will be a little incompatible with the definition of a
> will be placed in the '_func' system space. I mean, the definition
> that it accepts SCALAR, but in reality it will only accept STRING and
> 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
> some of these functions. What do you think about this?
Re your table of "current" result data types. I think we must define
In version 2.4, SELECT TYPEOF(LENGTH('')); returns 'integer'.
In version 2.6, SELECT TYPEOF(LENGTH('')); returns 'unsigned'.
In other words, somebody has already made changes, for tarantool-master.
However, I did not document that the return will be 'integer' so this is
a "change in behaviour" but not a "change in documented behaviour".
And I think we must define 'result type'.
You write that GREATEST etc. return 'scalar'.
But of course SELECT TYPEOF(GREATEST(1,'a')); returns 'string'.
So I assume you do not mean that the result value has the Tarantool 'scalar'
type, you only mean that the result value will be anything that
Tarantool/SQL currently supports.
Re returning UNSIGNED instead of INTEGER for ...LENGTH(), ROW_COUNT(), etc.
(a) This data type is not standard and is not built-in for most major DBMSs.
Even in MySQL
UNSIGNED is an attribute of a data type, as in BIGINT UNSIGNED, not a
(b) The maximum INTEGER value is the same as the maximum UNSIGNED value,
18446744073709551615, so the change is not necessary.
(c) Although in Tarantool it is not likely, there are generic programs
be checking whether the result of a function is negative. For example,
a result of an equivalent of our ROW_COUNT() function can be -1.
... Therefore I am not enthusiastic about this change.
Re returning NUMBER from ROUND():
You say that the current return is 'integer', but when I try it,
I get 'double'. I think this current behaviour is acceptable.
However, if ROUND(1) returns INTEGER, that is good too --
the general idea, not a law, would be
"if the result data type can be the same as the input data type(s),
let it be the same".
Re types of arguments:
You suggest that TRIM() etc. must have 'string' arguments,
but currently they can be 'varbinary' and I don't see why that is bad.
You suggest that CHAR() must have 'unsigned' argument,
but currently it can be some other type, and well, *maybe* that is bad.
I don't object to strictness, but worry that I might have to document
"sometimes we do an implicit cast, some other times we are strict".
Re BLOB instead of string. This is related to the fact that
TRIM() etc. currently do not need to have 'string' arguments,
they can have 'varbinary' arguments. I admit that many (maybe
most) other vendors expect character strings in such cases.
But the standard suggests that something very similar is allowed,
the DB2 manual says it is allowed,
the MariaDB manual is not explicit but here I show it is allowed:
mariadb>SELECT HEX(TRIM(X'D0' FROM CAST('Д' AS BINARY)));
OK 1 rows affected (0.0 seconds)
| HEX(TRIM(X'D0' FROM CAST('Д' AS BINARY))) |
| 94 |
I believe that Tarantool should continue to allow varbinary arguments.
Re MAP and ARRAY types "in the near future":
I think we must define "near future".
Currently in SQL we do not even have the Lua DECIMAL or UUID data types.
Kirill Yukhin made the issue Implement DECIMAL data type #4415
on August 8 2019, saying
"After DECIMAL type was introduced to the core, its time to implement
this type in SQL frontend."
We are nearly at August 8 2020, so apparently it takes more than one
year to put
a data type in SQL even though it is already in the core.
So I think that maps and arrays, which I think are more difficult,
will not exist in SQL for two years. I am not worried.
However, it is interesting to imagine
UPPER(array of strings) -- should we return upper of all elements?
UPPER(map) -- should we return upper of both the key and the value?
and so on.
I believe Lua non-scalar values should be flattened in SQL,
so perhaps the questions can all be avoided.
More information about the Tarantool-discussions