From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng1.m.smailru.net (smtpng1.m.smailru.net [94.100.181.251]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id D1C67469719 for ; Tue, 10 Mar 2020 16:23:42 +0300 (MSK) Date: Tue, 10 Mar 2020 16:23:39 +0300 From: Mergen Imeev Message-ID: <20200310132339.GB9563@tarantool.org> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Subject: [Tarantool-discussions] Rules for comparing numbers using Tarantool indexes in SQL. List-Id: Tarantool development process List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: tarantool-discussions@dev.tarantool.org, korablev@tarantool.org, alexander.turenko@tarantool.org, v.shpilevoy@tarantool.org, kyukhin@tarantool.org 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 |