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

[Peter Gulutzan]

Hi,

On 2020-07-29 2:50 a.m., Timur Safin wrote:
 >
 >
 > : 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.
 >

Okay, we vote differently.

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

I think that we agree, but gave a longer answer in an earlier email.

You mention guid, which reminds me about the UUID data type.
Someday we will have to decide ...
remembering that all number values sort together,
in a column defined as SCALAR,
which contains STRING and VARBINARY and UUID values,
do the UUID and STRING values sort together,
or do the UUID and VARBINARY values sort together,
or do the UUID values come after all the STRING and VARBINARY values?

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

I am not sure what you refer to.
Do you mean that ARRAY and MAP should be treated as blobs?
Would there be any change to what
box.execute([[SELECT * FROM "_space";]])
returns?

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

I explained in an earlier email what I thought about issue#4763.
But I am far away
(maybe I am the only Tarantool-related worker outside the Moscow area?)
so of course you know about many things that I do not know about.

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

Peter Gulutzan