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"