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

[Mergen Imeev]

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.

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.

| 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.

| 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      |