[Tarantool-discussions] Implicit cast in SQL

[Peter Gulutzan]

Hi,

On 2020-01-11 6:33 a.m., Mergen Imeev wrote:
 > Hi all,
 > I have a question to discuss. I know that we have discussed this
 > issue quite a few times.

Very true! I thought that most of these matters were settled.
Some of the discussions were with K. Osipov or CCed to dev,
for example the long Re: [dev] cast thread.
Should they be on the email "To:" list?

 > But, since we have a few open issues on
 > github, I think the final answer has not yet been found.

I see two open issues on github say implicit cast:
#3809 sql: implicit type cast problems during assignment 
https://github.com/tarantool/tarantool/issues/3809
#4230 sql: implicit type cast during comparison 
https://github.com/tarantool/tarantool/issues/4230
Are there others that I have missed?

 > I want to
 > completely close the issue this time.
 >
 > I would like to start with an implicit cast. Implicit cast
 > consists of implicit type conversion for COMPARISON and implicit
 > type conversion for ASSIGNMENT.

I will compare your suggestion with the chart that I wrote for
the documentation, which was the result of my understanding from
prior discussions. The documentation has not been published,
but can be seen in section Data type conversion, lines 1632 and 
following, in
https://github.com/tarantool/doc/blob/pgulutzan-2.3/doc/reference/reference_sql/sql.rst

 > Currently, all types can be divided into four categories:
 > numeric (UNSIGNED, INTEGER, NUMBER, DOUBLE), string (STRING),
 > binary (VARBINARY) and boolean (BOOLEAN). We also have a scalar
 > type, but any value of a scalar type has some subtype that can be
 > placed in one of these four categories.
 >

Please don't call them "subtypes". I proposed that we should regard
SCALAR as a supertype (and therefore the rules for the supertype
would apply for the subtype), but that was rejected.

 > So, I suggest this:
 > Implicit type conversion for COMPARISON:
 > 1) types of the same category can be implicitly cast to each
 > other;

Or "implicitly cast to the type whose range includes both values".

 > 2) types of different categories cannot be implicitly cast to
 > each other.
 >

The chart does not agree.

* The chart says A (always) numeric can be compared to string.
But I was fooled, I did not look in both directions.
Now S (sometimes) a numeric can be compared to a string.
For example, 1 = 'a' is legal, 'a' = 1 is illegal.
But anyway, it is currently not always true that types of different
categories cannot be implicitly cast to each other.

Incidentally, I believe this behaviour is bad, I argued against
implicit casting of numbers and strings for either comparison
or assignment. But other people, probably the majority,
argued that it was good, so it is the current behaviour.

Also, in SCALAR columns, comparisons of values of different
data types are allowed for the sake of ORDER BY (booleans
are less than strings etc.) but not allowed for the sake of
WHERE (searches for booleans cause an error if there are
strings in the column etc.). This is, I believe, a bad thing.

 > Implicit type conversion for ASSIGNMENT:
 > 1) If a user-defined cast function (UDCF) is defined, then this
 > function is used;
 > 2) If UDCF is not defined, then types of the same category can be
 > implicitly cast to each other, and types of different categories
 > cannot be implicitly cast to each other.
 >

The chart does not agree.

* The chart says A (always) numeric can be assigned to string,
and S (sometimes) string can be assigned to number.

* The chart says numeric to numeric implicit conversion is S
(sometimes allowed) not A (always allowed) because of range
differences, for example -1 will not fit in UNSIGNED.
Of course, you can reply that the cast is allowed but the
assignment is not allowed, if you prefer.

I have assumed that function calls may involve implicit assignment.

 > I think this is what ANSI says.
 >
 > We currently do not have such a thing as UDCF. I suggest to fill
 > an issue and use the same rules for ASSIGNMENT as for COMPARISON.
 > In fact, I think we can create a special option for assigning
 > UCDF during the rework of implicit type conversion for ASSIGNMENT.
 >
 > What do you think about this?
 >

When you say user-defined casts, do you really mean that
Tarantool's users will be given facilities that allow them
to write the code that handles comparison and assignment?
I think this might be hard, but will wait till I see the issue.

Peter Gulutzan