P0-[4.0 bug hunting]-[WHERE clause]-WRONG query result returned with CORRECT predicate evaluation

[zhangysh1995]

Bug Report

1. What did you do?

mysql> create table t                                              
        -> (a int not null primary key,                        
        ->  b float);                                            
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values(1, -0.1);                        
Query OK, 1 row affected (0.00 sec)  

mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 | -0.1 |
+---+------+
1 row in set (0.00 sec)

select b from t where b = -0.1;

mysql> select b from t where b = -0.1;
Empty set (0.00 sec)

2. What did you expect to see?

Returned result with one row.

3. What did you see instead?

Nothing returned. However, the predicate is evaluated to true (-0.1=-0.1):

mysql> select -0.1=-0.1;
+-----------+
| -0.1=-0.1 |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

4. What version of TiDB are you using? (tidb-server -V or run select tidb_version(); on TiDB)

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-rc
Git Commit Hash: 79db9e30ab8f98ac07c8ae55c66dfecc24b43d56
Git Branch: heads/refs/tags/v4.0.0-rc
UTC Build Time: 2020-04-08 07:32:25
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[shuke987]

This is because 0.1 cannot be represented exactly by float type. You can try 6.5, which in binary is 01000000 11010000 00000000 00000000, and can be selected from t.

If you want to use float, you can try select * from t where ABS(t - 0.1) < 0.0000001.

So this matches the design.

[shuke987]

/bug non-bug