From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 551442DC87 for ; Tue, 28 May 2019 09:00:17 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 1ZRcjbWZiAGq for ; Tue, 28 May 2019 09:00:17 -0400 (EDT) Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 08BFD2D9F2 for ; Tue, 28 May 2019 09:00:16 -0400 (EDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.8\)) Subject: [tarantool-patches] Re: [PATCH 3/3] sql: fix passing FP values to integer iterator From: "n.pettik" In-Reply-To: <20190528071951.GA31215@atlas> Date: Tue, 28 May 2019 16:00:11 +0300 Content-Transfer-Encoding: quoted-printable Message-Id: References: <789091b7acd99c908d26689f27c55f8b6dba3d16.1558700151.git.korablev@tarantool.org> <20190525055140.GA14501@atlas> <1fea92f3-20cc-d539-4010-0ef00b1b6c9a@tarantool.org> <20190528071951.GA31215@atlas> Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: tarantool-patches@freelists.org Cc: Konstantin Osipov , Vladislav Shpilevoy > On 28 May 2019, at 10:19, Konstantin Osipov = wrote: >=20 > * Vladislav Shpilevoy [19/05/28 09:22]: >> On 25/05/2019 08:51, Konstantin Osipov wrote: >>> * Nikita Pettik [19/05/24 20:42]: >>>> That happened due to the fact that type casting mechanism = (OP_ApplyType) >>>> doesn't affect FP value when it is converted to integer. Hence, FP = value >>>> was passed to the iterator over integer field which resulted in = error. >>>> Meanwhile, comparison of integer and FP values is legal in SQL. To = cope >>>> with this problem for each equality comparison involving integer = field >>>> we emit OP_MustBeInt, which checks whether value to be compared is >>>> integer or not. If the latter, we assume that result of comparison = is >>>> always false and continue processing query.=20 >>>=20 >>> Are you sure other vendords would fail to return any results for >>> WHERE foo =3D 1.0? >>=20 >> I do not understand, what you are talking about. It works. >=20 > Is this with the patch applied? This behaviour is observed even without applied patch: CREATE TABLE t(id INT PRIMARY KEY, a INT); INSERT INTO t VALUES(1, 1); SELECT * FROM t WHERE id =3D 1.1; rows: [] SELECT * FROM t WHERE id =3D 1.0; rows: - [1, 1] SELECT * FROM t WHERE a =3D 1.1; rows: [] SELECT * FROM t WHERE a =3D 1.0; rows: - [1, 1] But if we create index on field =E2=80=98a=E2=80=99, iterator will be used instead of full scan. However, usage of index search will result in error: CREATE INDEX i1 on t(a); SELECT * FROM t WHERE a =3D 1.1; 2019-05-28 15:51:40.974 [38764] main/102/interactive key_def.h:511 E> = ER_KEY_PART_TYPE: Supplied key type of part 0 does not match index part = type: expected integer --- - error: 'Failed to execute SQL statement: Supplied key type of part 0 = does not match index part type: expected integer' =E2=80=A6 Current patch fixes this misbehaviour and being applied result of query above is the same as using full scan. >>=20 >> tarantool> box.execute("CREATE TABLE t1(id INT PRIMARY KEY, a INT = UNIQUE);") >> tarantool> box.execute("INSERT INTO t1 VALUES (1, 1);") >> tarantool> box.execute("SELECT a FROM t1 WHERE a =3D 1.0;") >=20 > I don't understand how it works then. > a =3D 1.0 works but a =3D 1.1 doesn=E2=80=99t? Ok, consider searching condition =E2=80=98a =3D=3D FP=E2=80=99, where a = is integer field and FP is floating point value. Since both sides of comparison operator have different numeric types, one of them is promoted to another according to precedence list (I use DB2 terminology: = https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tp= c/db2z_promotionofdatatypes.html I suppose it is not significantly different from ANSI but way much easier to understand). In our case, integer is promoted to float type. Hence, searching condition is transformed to this one: =E2=80=98implicit_cast(a to float) =3D=3D = FP=E2=80=99. In our particular case: =E2=80=981.0 =3D=3D 1.1=E2=80=99 and =E2=80=981.0 = =3D=3D 1.0=E2=80=99. First one is obviously false, second one it true.