[Tarantool-discussions] Implicit cast for COMPARISON

[Mergen Imeev]

Hi! Thanks for answering.

On Tue, Jan 21, 2020 at 11:09:25AM -0700, Peter Gulutzan wrote:
> Hi,
> 
> On 2020-01-21 4:11 a.m., Mergen Imeev wrote:
> > Hi all,
> > I would like once again ask a question about implicit cast for
> > COMPARISON. Currently the chart of implicit cast for COMPARISON
> > looks this way:
> >             To BOOL | To INT | To DBL | To STR | To BIN
> > ---------------------------------------------------------
> > From BOOL | A       | -      | -      | -      | -
> > From INT  | -       | A      | A      | A      | -
> > From DBL  | -       | A      | A      | A      | -
> > From STR  | -       | S      | S      | A      | -
> > From BIN  | -       | -      | -      | -      | A
> >
> > I suggested to change this chart so that is should look this way:
> >             To BOOL | To NUM | To STR | To BIN
> > -----------------------------------------------
> > From BOOL | A       | -      | -      | -
> > From NUM  | -       | A      | -      | -
> > From STR  | -       | -      | A      | -
> > From BIN  | -       | -      | -      | A
> >
> > At the moment the only thing that different in these charts is
> > impicit cast from STRING to number and from number to STRING.
> >
> > I asked the Russian community in Telegram about whether to allow
> > this implicit cast for COMPARISON. At the moment, 31 people have
> > voted, and 25 of them have voted for dropping this implicit cast.
> >
> > So, once again I suggest using these rules:
> > 1) The values of numeric types are comparable without any implicit
> > cast.
> > 2) If the type of one of the values contains the type of another
> > value, then they are comparable.
> > 3) In any other cases, the values are not comparable.
> >
> > About "one type contains another type": we can say that INTEGER
> > contains UNSIGNED; NUMBER contains INTEGER, UNSIGNED, DOUBLE;
> > SCALAR contains all available in SQL types.
> >
> > Since all numeric types are comparable, my suggestion means that a
> > value of any type can be compared with a value of type SCALAR. And
> > in this comparison, the same rules will be used as in no-SQL
> > Tarantool. For example, any value of type STRING is greater than
> > any value of type INTEGER.
> >
> > What do you think about this?
> >
> 
> I believe that the words "compared with a value of type SCALAR"
> are technically incorrect -- columns can be defined as having a
> SCALAR type but the values in those columns are not SCALAR.
> But (I hope) that detail probably does not affect the proposition.
> 
Sorry, but I suggested a different approach: each value has
its own type, and SCALAR is one of the types. This means
that each value in a column of type SCALAR is of type
SCALAR. If we compare a value of type SCALAR with a value
of any other type, we will apply the SCALAR rules. If not
both values have numeric types and none of them has SCALAR
type, we get an error.

> This would reverse what some developers decided earlier
> in dev threads "Clarify the rule" and "[dev] cast".
> There would be no implicit casts, the SCALAR rules would apply.
> Therefore:
> 
> SELECT 1 = '1';
> Currently, returns: TRUE.
> After fix, returns: FALSE.
> 
No, after fix it will throw an error.

> CREATE TABLE t (s1 SCALAR PRIMARY KEY);
> INSERT INTO t VALUES (FALSE),('A');
> SELECT COUNT(*) FROM t WHERE s1 <> 'A';
> Currently, returns: Type mismatch: can not convert text to boolean.
> After fix, returns: 1.
> 
This is true.

> CREATE TABLE t (s1 INTEGER PRIMARY KEY);
> INSERT INTO t VALUES (1);
> SELECT COUNT(*) FROM t WHERE s1 <> 'A';
> Currently, returns: Type mismatch: can not convert text to boolean.
> After fix, returns: 1.
> 
Here we will see an error.

> If the above example is correct, it differs from NoSQL behaviour:
> box.schema.space.create('T')
> box.space.T:create_index('I')
> box.space.T:insert{1}
> box.space.T:select('A')
> Currently, returns: Supplied key type of part 0 does not match index part
> type
> After fix, returns: Supplied key type of part 0 does not match index part
> type
> (No change because the proposed fix is for SQL not Lua)
> 
> And, since there is no change suggested for assignment,
> implicit cast would still occur. Therefore:
> 
> CREATE TABLE t (s1 INTEGER PRIMARY KEY);
> INSERT INTO t VALUES ('1');
> Currently, returns: no error
> After fix, returns: no error
> (No change because assignments cause implicit cast)
> 
> SELECT UPPER(FALSE) FROM t;
> Currently, returns: 'FALSE' (string)
> After fix, returns: 'FALSE' (string)
> (No change because functions can cause assignments)
> 
> I think that the proposed change is good.
> 
> Peter Gulutzan
> 
> 
Still, I think the idea of using SCALAR rules is viable.
But I suggest adding this as one of the session settings.