Re: [Tarantool-discussions] Implicit cast for COMPARISON

[Peter Gulutzan <pgulutzan@ocelot.ca>]

Hi,

On 2020-02-04 11:50 a.m., Nikita Pettik wrote:

 > On 03 Feb 14:35, Mergen Imeev wrote:
<cut>
 >> 7) We need to clarify the rules when comparing SCALAR values. I
 >> think we cannot use the Tarantool rules here, as the Tarantool
 >> rules indicate that “100 < '2' == true”, but we decided that
 >> "100 > '2' == true", since '2' implicitly cast to 2. Could you
 >> suggest the rules that we should use here?
 >
 > There's already existing solution: while fetching value from space,
 > we preserve its initial field type. For SCALAR values we may use one
 > rules, for values fetched from INTEGER/STRING fields - apply another 
ones.

I do not think it is always so simple.

First, with regard to the implicit cast part of the question,
the current situation is:
CREATE TABLE t (A SCALAR PRIMARY KEY, B INTEGER);
INSERT INTO t VALUES (1,1);
SELECT * FROM t WHERE A = B AND A < '0' AND B > '0';
Result: 1 row.
It looks odd, but that is what happens if the SCALAR rules
and the non-SCALAR rules can fit in the same statement.
This could be solved by making implicit cast illegal.

But you still want to have two sets of rules, and (behaviour change) 
values can be SCALAR.

Suppose CREATE TABLE t (scalar_column SCALAR PRIMARY KEY,
                         non_scalar_column INT);

CAST(non_scalar_column AS SCALAR) result data type is SCALAR?

scalar_column < non_scalar_column is legal?

scalar_column < 1 /* data type of 1 is INTEGER */ ... is legal?

SUM(scalar_column) is SCALAR?

scalar_column + non_scalar_column is SCALAR?

SELECT scalar_column UNION SELECT non_scalar_column is SCALAR?

UPDATE t SET non_scalar_column = scalar_column is legal?

Peter Gulutzan