[tarantool-patches] Re: [PATCH 3/3] sql: fix passing FP values to integer iterator

[n.pettik]

> On 28 May 2019, at 10:19, Konstantin Osipov <kostja at tarantool.org> wrote:
> 
> * Vladislav Shpilevoy <v.shpilevoy at tarantool.org> [19/05/28 09:22]:
>> On 25/05/2019 08:51, Konstantin Osipov wrote:
>>> * Nikita Pettik <korablev at 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.