[Tarantool-discussions] Implicit cast for COMPARISON

[Nikita Pettik]

On 04 Feb 19:57, Peter Gulutzan wrote:
> 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.

Yes, I stick to the point that implicit cast should be 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?

Yes.
 
> scalar_column < non_scalar_column is legal?

Yes, comparison rules are supposed to be the same as in NoSQL Tarantool.

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

Yes.

> SUM(scalar_column) is SCALAR?

No, it is not. Taking into account removing implicit cast between
numeric and string types, SUM() is assumed to be finished only in
case all arguments are of numeric type. What is more SUM() features
NUMBER return type (in its function definition).

> scalar_column + non_scalar_column is SCALAR?

No. If addition is allowed, type of resulting value will be numeric
(integer or float).

> SELECT scalar_column UNION SELECT non_scalar_column is SCALAR?

I guess yes (at least it seems to be reasonable to me).

> UPDATE t SET non_scalar_column = scalar_column is legal?

It depends on particular value of scalar_column.

To sum up, my proposal is to allow comparing scalar values with values of
any other type. On the other hand, result of such operations like
assignment or addition should depend on particular values of SCALAR
type.

Alternatively, we can abandon this idea and operate apply comparison
rules depending purely on mp_ types of particular scalar values. But
then rules in NoSQL and SQL will be different.