[Tarantool-discussions] Rules for comparing numbers using Tarantool indexes in SQL.
Peter Gulutzan
pgulutzan at ocelot.ca
Fri Mar 13 19:25:26 MSK 2020
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
More information about the Tarantool-discussions
mailing list