sql: define rules for comparison for SCALAR

[ImeevMA]

We should define rules for comparison between value of SCALAR type and value of any of scalar types (INTEGER, STRING, BOOLEAN, DOUBLE, VARBINARY, NUMBER).

[Gerold103]

But the rules are defined. In box. And I don't see why should they be different in SQL, it would be inconsistent, and would lead to different results when you select using box and using SQL.

[ImeevMA]

@pgulutzan Could you say you agree or disagree with these rules?

[pgulutzan]

I agree with the box rules, which are in the manual https://www.tarantool.io/en/doc/2.4/reference/reference_lua/box_space/#lua-function.space_object.create_index:

"When there is a mix of types, the key order is: null, then booleans, then numbers, then strings, then byte arrays."

restated in the SQL section:

"When there is a mix of types, the key order is: null, then booleans, then numbers, then strings, then varbinary."

In issue#4783 comment @ImeevMA said:

"if we decide that the behaviour for cases when the index can be used is the same as the
behaviour for cases when the index will not be used, then the only possible option is to use SCALAR rules for comparison."

I do not agree that there should be a "value of SCALAR type".

[ImeevMA]

@pgulutzan Do you mean that we use the mentioned rules only if we search in a column of type SCALAR?

[pgulutzan]

No, I mean that I believe we should use the mentioned rules always.

[ImeevMA]

@pgulutzan What do you think of the idea of ​​making SCALAR a completely separate type? I mean, in this case, we must cast the values ​​to SCALAR (CAST (a AS SCALAR)) before we can insert them into a field of type SCALAR or compare with a value of type SCALAR. If we try to compare a value of any other type with a value of type SCALAR, we get an error. The same goes for inserting values ​​into a SCALAR type field.

So far this is just an idea. But we can do it if it's worth it.

[pgulutzan]

@ImeevMA: I do not like this. As I said in old emails,
I believe BOOLEAN values in SCALAR columns should have
type = BOOLEAN, STRING values in SCALAR columns should
have type = STRING, and so on. However, I am not saying
that your plan will not work. Yes, you will always need
to cast to/from SCALAR for any operations on SCALAR
columns. I wonder whether you need to do more than that.

CREATE TABLE t (s1 SCALAR PRIMARY KEY);
INSERT INTO t VALUES (1),('a');
SELECT * FROM t WHERE s1 = 'a';

... will fail because you cannot compare 'a' to SCALAR

SELECT * FROM t WHERE CAST(s1 AS STRING) = 'a';

... will fail because you cannot cast 1 to STRING.
Does everyone else in the team like your idea?

[kyukhin]

@pgulutzan, this was originally my idea. I am personally don't like any kinds of VARIANT type in strictly typed language and that is why I am trying to limit SCALAR. I don't understand, how can we deal with different types in a single column of a table.
Considering your example, do you have an idea, how it should work?

CREATE TABLE t (s1 SCALAR PRIMARY KEY);
INSERT INTO t VALUES (1),('a');
SELECT * FROM t WHERE s1 = 'a';

Should we try to compare integer (1) to 'a'? Once again I am against of implicit casting.
But I don't see how it should work w/o runtime type issues (comparisons of integer to string).

[ImeevMA]

All values ​​in the SCALAR field are divided into 8 classes, between the values ​​of different classes the comparison is performed as a comparison between classes:

	MP_CLASS_NIL,
	MP_CLASS_BOOL,
	MP_CLASS_NUMBER,
	MP_CLASS_STR,
	MP_CLASS_BIN,
	MP_CLASS_UUID,
	MP_CLASS_ARRAY,
	MP_CLASS_MAP,

For example:

tarantool> s = box.schema.space.create('s', {format = {{'a', 'scalar'}}})
---
...

tarantool> _ = s:create_index('i')
---
...

tarantool> s:insert({1})
---
- [1]
...

tarantool> s:insert({2.5})
---
- [2.5]
...

tarantool> s:insert({true})
---
- [true]
...

tarantool> s:insert({'b4'})
---
- ['b4']
...

tarantool> s:select({2}, {iterator = 'GE'})
---
- - [2.5]
  - ['b4']
...

tarantool> s:select({false}, {iterator = 'LE'})
---
- []
...

tarantool> s:select({false}, {iterator = 'GE'})
---
- - [true]
  - [1]
  - [2.5]
  - ['b4']
...

tarantool> s:select({'0'}, {iterator = 'LE'})
---
- - [2.5]
  - [1]
  - [true]
...

[pgulutzan]

@kyukhin: I have argued about this several times in dev emails, for example
https://lists.tarantool.org/pipermail/tarantool-discussions/2020-February/000042.html
Mergen Imeev did not accept my arguments, and that is okay,
so we are not looking at my "idea, of how it should work" (which of course meant
that 1 = 'a' is false). We are looking at his idea,
which apparently means that 1 = 'a' does not work, so the
statement "SELECT * FROM t WHERE s1 = 'a';" causes an error.

[tsafin]

Rules defined as part of #6009