From: "n.pettik" <korablev@tarantool.org> To: tarantool-patches@freelists.org Cc: Konstantin Osipov <kostja@tarantool.org>, Vladislav Shpilevoy <v.shpilevoy@tarantool.org> Subject: [tarantool-patches] Re: [PATCH 3/3] sql: fix passing FP values to integer iterator Date: Tue, 28 May 2019 16:00:11 +0300 [thread overview] Message-ID: <A8343998-1ECA-47CE-9855-411E77DE7026@tarantool.org> (raw) In-Reply-To: <20190528071951.GA31215@atlas> > On 28 May 2019, at 10:19, Konstantin Osipov <kostja@tarantool.org> wrote: > > * Vladislav Shpilevoy <v.shpilevoy@tarantool.org> [19/05/28 09:22]: >> On 25/05/2019 08:51, Konstantin Osipov wrote: >>> * Nikita Pettik <korablev@tarantool.org> [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. >>> >>> Are you sure other vendords would fail to return any results for >>> WHERE foo = 1.0? >> >> I do not understand, what you are talking about. It works. > > 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 = 1.1; rows: [] SELECT * FROM t WHERE id = 1.0; rows: - [1, 1] SELECT * FROM t WHERE a = 1.1; rows: [] SELECT * FROM t WHERE a = 1.0; rows: - [1, 1] But if we create index on field ‘a’, 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 = 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' … Current patch fixes this misbehaviour and being applied result of query above is the same as using full scan. >> >> 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 = 1.0;") > > I don't understand how it works then. > a = 1.0 works but a = 1.1 doesn’t? Ok, consider searching condition ‘a == FP’, 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/tpc/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: ‘implicit_cast(a to float) == FP’. In our particular case: ‘1.0 == 1.1’ and ‘1.0 == 1.0’. First one is obviously false, second one it true.
next prev parent reply other threads:[~2019-05-28 13:00 UTC|newest] Thread overview: 19+ messages / expand[flat|nested] mbox.gz Atom feed top 2019-05-24 17:39 [tarantool-patches] [PATCH 0/3] Fix passing key with different from iterator's type Nikita Pettik 2019-05-24 17:39 ` [tarantool-patches] [PATCH 1/3] sql: remove redundant check of space format from QP Nikita Pettik 2019-05-24 17:39 ` [tarantool-patches] [PATCH 2/3] sql: remove redundant type derivation " Nikita Pettik 2019-05-27 21:49 ` [tarantool-patches] " Vladislav Shpilevoy 2019-05-28 19:58 ` n.pettik 2019-05-24 17:39 ` [tarantool-patches] [PATCH 3/3] sql: fix passing FP values to integer iterator Nikita Pettik 2019-05-25 5:51 ` [tarantool-patches] " Konstantin Osipov 2019-05-27 21:49 ` Vladislav Shpilevoy 2019-05-28 7:19 ` Konstantin Osipov 2019-05-28 11:31 ` Vladislav Shpilevoy 2019-05-29 7:02 ` Konstantin Osipov 2019-05-28 13:00 ` n.pettik [this message] 2019-05-29 9:14 ` Konstantin Osipov 2019-05-27 21:49 ` Vladislav Shpilevoy 2019-05-28 19:58 ` n.pettik 2019-06-02 18:11 ` Vladislav Shpilevoy 2019-06-06 13:51 ` n.pettik 2019-06-06 19:07 ` Vladislav Shpilevoy 2019-07-11 9:19 ` [tarantool-patches] Re: [PATCH 0/3] Fix passing key with different from iterator's type Kirill Yukhin
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=A8343998-1ECA-47CE-9855-411E77DE7026@tarantool.org \ --to=korablev@tarantool.org \ --cc=kostja@tarantool.org \ --cc=tarantool-patches@freelists.org \ --cc=v.shpilevoy@tarantool.org \ --subject='[tarantool-patches] Re: [PATCH 3/3] sql: fix passing FP values to integer iterator' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox