Re: [Tarantool-discussions] Rules for comparing numbers using Tarantool indexes in SQL.

[Peter Gulutzan <pgulutzan@ocelot.ca>]

Hi,

On 2020-03-10 7:23 a.m., Mergen Imeev wrote:
 > Hi all,
 > I would like to discuss comparison between numbers using an index
 > in SQL. I admit that I was wrong when I said that we can compare
 > numbers without any explicit conversion.
 >

Do you mean that you will require explicit conversion
i.e. the CAST function when comparing numbers, sometimes?

 > To achieve the same result as if we would not use any index, I
 > propose the following rules.
 >
 > Column names in the tables below:
 > opcode - original operation ('>' == GT, etc.);
 > conditions - additional conditions on the value of the variable;
 > iter - iterator that will be used to search in Tarantool;
 > value - value that will be used to search in Tarantool;
 > is_none - flag, is_none == true if tuples will not be found.
 >

I'll assume that when you say "the value of the variable",
you mean the value in the index. So later, when you say
"d < 0", you mean "the value in the index is less than 0".

I'll assume that when you say "is_none == true if tuples will be found",
you do not mean that is_none == false implies that tuples
will be found, that is unknown.
And of course when you put "No" and "Yes" in the is_none column,
you mean False and True respectively.

I'll assume that when you put "-" in the value column, you mean
that value can be anything.

I'll assume that when you say "d < 0" in the conditions column
and "-" in the value column and "Yes" in the is_none column,
you do not mean "if the index contains any value that is less
than 0 then there will be no returned tuples", you mean
"if the value in the index is less than 0 then the tuple
associated with that value will not be returned (but for
another value in the index there might be a returned tuple)".

 > I) Field type of index part == UNSIGNED:
 >
 > 1) mp_type of value == MP_INT:
 >
 > All values with mp_type == MP_INT are less than 0 by definition.
 >
 > | opcode   | conditions | iter   | value | is_none |
 > --------------------------------------------------------
 > | GE/GT    | -          | GE     | 0     | No      |
 > | LE/LT/EQ | -          | -      | -     | Yes     |
 >

MessagePack definition
https://github.com/msgpack/msgpack/blob/master/spec.md#int-format-family
does not say that numbers in the "int" family are all less than 0.
So I'll assume that when you say MP_INT values are less than 0
"by definition", you do not mean MessagePack definition, you
mean that you will guarantee that any search values which are
greater than or equal to 0 will not be identified as MP_INT.

 > 2) mp_type of value == MP_DOUBLE:
 >
 > | opcode   | conditions     | iter   | value      | is_none |
 > -------------------------------------------------------------
 > | GE/GT    | d < 0          | GE     | 0          | No |
 > | LE/LT/EQ | d < 0          | -      | -          | Yes |
 > | GE/GT/EQ | d > UINT64_MAX | -      | -          | Yes |
 > | LE/LT    | d > UINT64_MAX | LE     | UINT64_MAX | No |
 >
<cut>

Because of the assumptions that I made about what you mean,
I think that these statements are true:
If index on column1 has -1: column1 >= 0.0 maybe will return tuples.
If index on column1 has -1: column1 <= 0.0 will not return tuples.
If index on column1 has UINT64_MAX+1: column1 > 0.0 will not return tuples.
If index on column1 has UINT64_MAX+1: column1 < 0.0 maybe will return 
tuples.

But those results are impossible or absurd.

Therefore some or all of my assumptions, about what you mean,
must be incorrect. Perhaps you can explain what I did wrong?

Peter Gulutzan