Tarantool discussions archive
 help / color / mirror / Atom feed
* [Tarantool-discussions] Rules for comparing numbers using Tarantool indexes in SQL.
@ 2020-03-10 13:23 Mergen Imeev
  2020-03-12 21:37 ` Vladislav Shpilevoy
  2020-03-13 16:25 ` Peter Gulutzan
  0 siblings, 2 replies; 6+ messages in thread
From: Mergen Imeev @ 2020-03-10 13:23 UTC (permalink / raw)
  To: tarantool-discussions, korablev, alexander.turenko, v.shpilevoy, kyukhin

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      |

^ permalink raw reply	[flat|nested] 6+ messages in thread

end of thread, other threads:[~2020-03-14 14:44 UTC | newest]

Thread overview: 6+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2020-03-10 13:23 [Tarantool-discussions] Rules for comparing numbers using Tarantool indexes in SQL Mergen Imeev
2020-03-12 21:37 ` Vladislav Shpilevoy
2020-03-14  9:19   ` Mergen Imeev
2020-03-13 16:25 ` Peter Gulutzan
2020-03-14  9:54   ` Mergen Imeev
2020-03-14 14:44     ` Peter Gulutzan

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox