Bug #100777 Small negative float equals to 0 when index is used

• Submitted: 9 Sep 2020 8:21
• Modified: 9 Sep 2020 9:06
• Reporter: Yushan ZHANG
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.21, 5.7.31
• OS: Any
• CPU Architecture: Any

[9 Sep 2020 8:21] Yushan ZHANG

Description:
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> create table t1 (
    -> id smallint ,
    ->   key myid (id)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> 
mysql> insert into t1 value (0);
Query OK, 1 row affected (0.02 sec)

mysql> select TAN ( 2 - TAN ( 3 ) ) % SIN ( 9 );
+-----------------------------------+
| TAN ( 2 - TAN ( 3 ) ) % SIN ( 9 ) |
+-----------------------------------+
|               -0.3177895486384313 |
+-----------------------------------+
1 row in set (0.00 sec)

-- incorrect, comparison right hand is not zero
mysql> select id, TAN ( 2 - TAN ( 3 ) ) % SIN ( 9 ) from t1 where id =  TAN ( 2 - TAN ( 3 ) ) % SIN ( 9 );
+------+-----------------------------------+
| id   | TAN ( 2 - TAN ( 3 ) ) % SIN ( 9 ) |
+------+-----------------------------------+
|    0 |               -0.3177895486384313 |
+------+-----------------------------------+
1 row in set (0.00 sec)

However, if we remove the index from the table, the result is correct:

mysql> drop table if exists t2;
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> create table t2 (
    -> id smallint
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> 
mysql> insert into t2 value (0);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> select id from t2 where id =  TAN ( 2 - TAN ( 3 ) ) % SIN ( 9 );
Empty set (0.00 sec)

How to repeat:
drop table if exists t1;

create table t1 (
	id smallint ,
  	key myid (id)
);

insert into t1 value (0);

select TAN ( 2 - TAN ( 3 ) ) % SIN ( 9 );

-- incorrect
select id, TAN ( 2 - TAN ( 3 ) ) % SIN ( 9 ) from t1 where id =  TAN ( 2 - TAN ( 3 ) ) % SIN ( 9 );

drop table if exists t2;

create table t2 (
	id smallint
);

insert into t2 value (0);

select id from t2 where id =  TAN ( 2 - TAN ( 3 ) ) % SIN ( 9 );

[9 Sep 2020 9:06] MySQL Verification Team

Hello Yushan ZHANG,

Thank you for the report and feedback.

regards,
Umesh