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

[Vladislav Shpilevoy]

Hi!

Nice table. Perhaps it is worth documenting it somewhere on the
site, or on wiki, or in an RFC. To persist it somehow, and send
users to there, when they ask something about cross-type comparison.

On 10/03/2020 14:23, 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.

I don't think I fully understand what it means to compare
numbers using an index.

See 2 comments below.

> 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) 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     |
> 
> 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      |
> 
> In the next table we assert that d >= 0 and d <= UINT64_MAX.

1. What is 'd'? Value to search by?

> 
> | opcode   | conditions  | iter   | value  | is_none |
> ----------------------------------------------------------
> | GT       | -           | GT     | (int)d     | No      |
> | GE       | d == (int)d | GE     | (int)d     | No      |
> | GE       | d != (int)d | GT     | (int)d     | No      |
> | EQ       | d == (int)d | EQ     | (int)d     | No      |
> | EQ       | d != (int)d | -      | -          | Yes     |
> | LT       | d == (int)d | LT     | (int)d     | No      |
> | LT       | d != (int)d | LE     | (int)d     | No      |
> | LE       | -           | LE     | (int)d     | No      |
> 
> II) Field type of index part == INTEGER:
> 
> In this case the only possible mp_type of value is MP_DOUBLE.

2. Why? INTEGER field type can store MP_UINT and MP_INT. So you
can search here by any number mp_type.

> | opcode   | conditions     | iter   | value      | is_none |
> -------------------------------------------------------------
> | GE/GT    | d < INT64_MIN  | GE     | INT64_MIN  | No      |
> | LE/LT/EQ | d < INT64_MIN  | -      | -          | Yes     |
> | GE/GT/EQ | d > UINT64_MAX | -      | -          | Yes     |
> | LE/LT    | d > UINT64_MAX | LE     | UINT64_MAX | No      |
> 
> In the next table we assert that d >= INT64_MIN and
> d < 0. In case d >= 0 and d <= UINT64_MAX we may use table for
> UNSIGNED.
> 
> | opcode   | conditions  | iter   | value     | is_none |
> ---------------------------------------------------------
> | GT       | d == (int)d | GT     | (int)d    | No      |
> | GT       | d != (int)d | GE     | (int)d    | No      |
> | GE       | -           | GE     | (int)d    | No      |
> | EQ       | d == (int)d | EQ     | (int)d    | No      |
> | EQ       | d != (int)d | -      | -         | Yes     |
> | LT       | -           | LT     | (int)d    | No      |
> | LE       | d == (int)d | LE     | (int)d    | No      |
> | LE       | d != (int)d | LT     | (int)d    | No      |
> 
> III) Field type of index part == DOUBLE:
> 
> In this case, the rules are the same for MP_INT and MP_UINT. In
> addition, we exclude the case when the value is less than 2^53 and
> more than -2^53, since we can simply convert it to DOUBLE without
> precision loss.
> 
> In the table below, we assert that the value is INTEGER which is
> greater than 2^53 or less than -2^53.
> 
> | opcode   | conditions     | iter   | value     | is_none |
> ------------------------------------------------------------
> | GT       | (double)i > i  | GE     | (double)i | No      |
> | GT       | (double)i <= i | GT     | (double)i | No      |
> | GE       | (double)i >= i | GE     | (double)i | No      |
> | GE       | (double)i < i  | GT     | (double)i | No      |
> | EQ       | (double)i == i | EQ     | (double)i | No      |
> | EQ       | (double)i != i | -      | -         | Yes     |
> | LT       | (double)i < i  | LE     | (double)i | No      |
> | LT       | (double)i >= i | LT     | (double)i | No      |
> | LE       | (double)i <= i | LE     | (double)i | No      |
> | LE       | (double)i > i  | LT     | (double)i | No      |
>