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

[Timur Safin]

: From: Peter Gulutzan <pgulutzan at ocelot.ca>
: Subject: Re: The result type and argument types of the built-in SQL
: functions.
: 
...
: 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
: https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html
: UNSIGNED is an attribute of a data type, as in BIGINT UNSIGNED, not a
: data type.
: (b) The maximum INTEGER value is the same as the maximum UNSIGNED value,
: 18446744073709551615, so the change is not necessary.

Agreed that UNSIGNED is unnecessary and should be covered by INTEGER type.

: 
: 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".

I'd vote for NUMBER in the places where we need either integer or double.
NUMBER looks more Lua-ish (and we do want to have consistent behavior 
between Lua and SQL as much as possible), i.e. if you have double value 
which happens to be fraction-less then it will be normalized and 
look as integer.

: 
: 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.

Yup, this is part of blob vs string question. 

: 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.

I do agree that that there are no single place where string 
should be behave anyhow different than varbinary. Blob is string,
guid is string, thus in any case where string is acceptable should
be others acceptable. 

 
: 
: 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.

But should we do anything here - if it's blob with underlying string 
storage? Is it anyhow different to varbinary?


: (
: 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.

I feel your pain, but (hope) your estimations here are a little bit 
too conservative :)

: 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.
: 
: Peter Gulutzan

I believe Lua non-scalar should be incompatible with scalar context and 
should generate runtime errors. But it's too early to discuss this though.

Timur