From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from rhino.ch-server.com (rhino.ch-server.com [209.59.190.103]) (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 AA0AC469719 for ; Fri, 13 Mar 2020 19:25:31 +0300 (MSK) References: <20200310132339.GB9563@tarantool.org> From: Peter Gulutzan Message-ID: Date: Fri, 13 Mar 2020 10:25:26 -0600 MIME-Version: 1.0 In-Reply-To: <20200310132339.GB9563@tarantool.org> Content-Type: text/plain; charset="utf-8"; format="flowed" Content-Transfer-Encoding: 8bit Content-Language: en-US Subject: Re: [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: Mergen Imeev Cc: tarantool-discussions@dev.tarantool.org 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 | > 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