[Tarantool-discussions] Implicit cast for COMPARISON

[Peter Gulutzan]

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.

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.

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.

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.

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