Bug #104332 Comparing signed int with floating number result is wrong
Submitted: 16 Jul 2021 7:07 Modified: 16 Jul 2021 11:37
Reporter: ick R Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.25 OS:Any
Assigned to: CPU Architecture:Any

[16 Jul 2021 7:07] ick R
Description:
When we compare signed int with floating number, what we get is wrong. As we know, the result of comparison between two numbers is either equal or not equal,but in this example, we can get some duplicate tuples from both where clauses "where col_int_key_signed != 7/9" and "where not col_int_key_signed != 7/9". It seems like an optimizer bug.

How to repeat:
create table table_5_latin1_6 (                                                                                                                                                                                                                       `pk` int primary key,                                                                                                                                                                                                                                 
`col_bigint_undef_signed` bigint  ,                                                                                                                                                                                                                   `col_bigint_key_signed` bigint  ,                                                                                                                                                                                                                     
`col_float_undef_signed` float  ,                                                                                                                                                                                                                     `col_float_key_signed` float  ,                                                                                                                                                                                                                       
`col_double_undef_signed` double  ,                                                                                                                                                                                                                   `col_double_key_signed` double  ,                                                                                                                                                                                                                     
`col_int_undef_signed` int  ,                                                                                                                                                                                                                         `col_int_key_signed` int  ,                                                                                                                                                                                                                           
`col_char(20)_undef_signed` char(20)  ,                                                                                                                                                                                                               `col_char(20)_key_signed` char(20)  ,                                                                                                                                                                                                                 
`col_varchar(20)_undef_signed` varchar(20)  ,                                                                                                                                                                                                         `col_varchar(20)_key_signed` varchar(20)  ,                                                                                                                                                                                                           
`col_tinyint_undef_signed` tinyint  ,                                                                                                                                                                                                                 `col_tinyint_key_signed` tinyint  ,                                                                                                                                                                                                                   
`col_smallint_undef_signed` smallint  ,                                                                                                                                                                                                               `col_smallint_key_signed` smallint  ,                                                                                                                                                                                                                 key (`col_bigint_key_signed`),                                                                                                                                                                                                                        key (`col_float_key_signed`),                                                                                                                                                                                                                         
key (`col_double_key_signed`),                                                                                                                                                                                                                        key (`col_int_key_signed`),                                                                                                                                                                                                                           
key (`col_char(20)_key_signed`),                                                                                                                                                                                                                      key (`col_varchar(20)_key_signed`),                                                                                                                                                                                                                   
key (`col_tinyint_key_signed`),                                                                                                                                                                                                                       key (`col_smallint_key_signed`)                                                                                                                                                                                                                       
) character set latin1                                                                                                                                                                                                                                
partition by hash(pk)
partitions 6;

insert into table_5_latin1_6 values (0,-9.183,0.000001,0,0.0001,0.000001,0.000001,0.0001,100,2  ,2      ,null,2 ,2
,2
,2
,null),(1,-1,1.009,-0.0001,0.000001,-0,1.009,-9.183,12.991,2    ,null,2
,2      ,null,2 ,2      ,2
),(2,100,1,-1,0,12.991,-0,0,1.009,2     ,null,2 ,2
,2      ,2
,2
,2      ),(3,1,-1,100,0.0001,-9.183,1,-0,1,null,2
,2
,null,2 ,2
,null,2 ),(4,-9.183,12.991,-0,100,1,0.0001,-1,-9.183,2
,2      ,null,2 ,2      ,2      ,2      ,null);

Then run queries:

SELECT 'col_int_key_signed' != 7/9;

SELECT pk,col_int_key_signed FROM table_5_latin1_6 WHERE  ( `col_int_key_signed` != ( 7 / 9 ) ) IS NULL;

SELECT pk,col_int_key_signed FROM table_5_latin1_6 WHERE NOT ( `col_int_key_signed` != ( 7 / 9 ) ) ;

SELECT pk,col_int_key_signed FROM table_5_latin1_6 WHERE  ( `col_int_key_signed` != ( 7 / 9 ) );

Results:

mysql> SELECT 'col_int_key_signed' != 7/9;
+-----------------------------+
| 'col_int_key_signed' != 7/9 |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT pk,col_int_key_signed FROM table_5_latin1_6 WHERE  ( `col_int_key_signed` != ( 7 / 9 ) ) IS NULL;
Empty set (0.00 sec)

mysql> SELECT pk,col_int_key_signed FROM table_5_latin1_6 WHERE NOT ( `col_int_key_signed` != ( 7 / 9 ) ) ;
+----+--------------------+
| pk | col_int_key_signed |
+----+--------------------+
|  2 |                  1 |
|  3 |                  1 |
+----+--------------------+
2 rows in set (0.00 sec)

mysql> SELECT pk,col_int_key_signed FROM table_5_latin1_6 WHERE  ( `col_int_key_signed` != ( 7 / 9 ) );
+----+--------------------+
| pk | col_int_key_signed |
+----+--------------------+
|  0 |                100 |
|  1 |                 13 |
|  2 |                  1 |
|  3 |                  1 |
|  4 |                 -9 |
+----+--------------------+
5 rows in set (0.00 sec)

This is obvious wrong because when col_int_key_signed=1, we can get result from both "where col_int_key_signed != 7/9" and "where not col_int_key_signed != 7/9"
[16 Jul 2021 11:37] MySQL Verification Team
Hi Mr. R,

Thank you for your bug report.

However, this is not a bug.

Our Reference Manual has a chapter on the floating point numbers, that also deals with comparisons.

You can not compare integers and IEEE floating point and expect reliable results.

MySQL is not a strong typing server, which return the error on comparisons, like those that you have presented.

We do not plan to make it strongly typed product, because it would break thousands of the legacy applications.

However, in your code, you can use typecasting , conversion functions and similar .......

Not a bug.