Bug #100213 wrong result when filter decimal column by digit string in IN-predicate

• Submitted: 14 Jul 2020 9:15
• Modified: 20 Jul 2020 13:47
• Reporter: Kang Xia (OCA)
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.7.21,5.7.27,8.0.20
• OS: Any
• CPU Architecture: Any
• Tags: decimal, IN

[14 Jul 2020 9:15] Kang Xia

Description:
The query result below is confusing. IN() is not equivalent to OR.
Although my SQL specification says " Do data type converting in java, avoid implicit type converting in SQL", 
App developer : "quotes is added by mybatis when using <foreach>".

How to repeat:
mysql> create table t1(col decimal(21, 0));

mysql> insert into t1 values(171887878719799005);

mysql> insert into t1 values(171887878719799004);

mysql> commit;

mysql> select col from t1 where col in ('171887878719799005');
col
----------------
171887878719799005

mysql> select col from t1 where col in ('123');
Empty set

mysql> select col from t1 where col in ('123', '171887878719799005');
col
-----------------
171887878719799005
171887878719799004              <-- surprise!

mysql> show warnings;
Empty set

mysql> select col from t1 where col='171887878719799005';
col
----------------
171887878719799005

mysql> select col from t1 where col='123';
Empty set

mysql> select col from t1 where col='171887878719799005' or col='123';
col
---------------
171887878719799005

[14 Jul 2020 13:05] MySQL Verification Team

Hi Mr. Xia,

I have run your test case on 8.0.20, exactly as you have sent us.

I got this output:

---------------

col
171887878719799005
col
171887878719799005
col
171887878719799005
col
171887878719799005

---------------

I have interspersed each SQL command with SHOW WARNINGS and there were none.

The only change that I have made is that I wrote numeric data as numeric and not as a string.

Can't repeat.

[20 Jul 2020 1:10] Kang Xia

Hi:
    Try with string please.
    If MySQL don't support cast string to decimal implicitly when assign a string value to a decimal volumn, maybe here could send a warning or error. Anyway, the query result in my case is counterintuitive.

[20 Jul 2020 13:47] MySQL Verification Team

Hi Mr. Xia,

Numeric data should never be entered as strings. Rules on converting from string types to numerical types are explained in our manul.

That is why we have all these casting functions. This is asol fully described in our Reference Manual.

Not a bug.