MySQL Bugs: #108765: comparison of INT column with DECIMAL value changes if index is used or not

Bug #108765	comparison of INT column with DECIMAL value changes if index is used or not
Submitted:	13 Oct 2022 13:52	Modified:	17 Oct 2022 6:55
Reporter:	Guilhem Bichot	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	8.0.31	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
create table tinytable(a int, key(a));
insert into tinytable values(0),(0),(0);
select /*+ no_index(tinytable a) */ * from tinytable where a = 0.29+0.0;
a
select  * from tinytable where a = 0.29+0.0;
a
0
0
0

The two queries should return the same result.
In the second query's case, EXPLAIN shows that the WHERE is eliminated and replaced with an index lookup, but this index lookup converts 0.29 to INT (=0) that's why there are wrong matches.

How to repeat:
create table tinytable(a int, key(a));
insert into tinytable values(0),(0),(0);
select /*+ no_index(tinytable a) */ * from tinytable where a = 0.29+0.0;
a
select  * from tinytable where a = 0.29+0.0;
a
0
0
0

Suggested fix:
I think test_if_ref() should be more strict in case of type mismatch, when the indexed column's type is narrower than the lookup value's type.

Hello Guilhem,

Thank you for the report and test case.

regards,
Umesh

Actually, it may be a broader problem.

If we look at test_if_ref(), it calls ref_lookup_subsumes_comparison() which ends with:
      return !right_item->save_in_field_no_warnings(field, true);
The idea of this line is: for "col=value", try to store "value" into "col", and if the "store" code gets a problem when storing (for example: truncation, or overflow), which implies that the lookup key is not reliable, then index lookup cannot be done.
Here, we could precisely expect the storing of decimal 0.29 into integer column tinytable.a to say it had a problem.
But, no, it doesn't say so. my_decimal2int() remains silent (it just rounds 0.29 to 0).
And that has consequences on INSERT too. See this:

insert into tinytable values(0.29);

0 is inserted, decimals are lost, I'm using strict mode, I should get a warning, and I don't. Take this similar query, but this time I'm not using INT as a target column, I'm using DECIMAL(10,0), which is, in essence, just an integer:

create table t_dec(a decimal(10,0));
insert into t_dec values(0.29);
Warnings:
Note	1265	Data truncated for column 'a' at row 1

There I get a warning!
So I believe the root problem is that when the decimal 0.29 is converted to integer no warning is raised. If this was fixed, then ref_lookup_subsumes_comparison() would work all fine.

Same situation with a floating-point or string input:

- if I try to store a float 0.29e0 into the INT column, no warning; into the DECIMAL(10,0): warning.
- If I try to store a string '0.29': same results.

Hi Guilhem,

notice that insertion is different from comparison.

Quoting from the standard document we both know:

If a member of the declared type of T can be obtained from V by rounding or truncation, then T is set to that value. If the declared type of T is exact numeric, then it is implementation-defined whether the approximation is obtained by rounding or by truncation.

It is a valid operation, and no warning is mentioned. Imagine how many warnings that would have to be given when converting from a float type to an exact number.

Hello Roy! You're right, there is no violation of the document you quoted. However, there is inconsistency between the two exact numeric types decimal(10,0) and INT: inserting 0.29 into decimal(10,0) gives warning while inserting into INT does not.
If this inconsistency was fixed, we would:
- get more warnings upon insertion (a good thing here - consistency)
- not get more warnings upon comparison in SELECT, because in comparison the flags passed to the conversion functions are more permissive so I don't think this case would be affected,
- get a solution for the index-dependent query result (real bug) which I posted at the beginning of the report, as test_if_ref() would see a "conversion error" return value and give up on using the index.