Tidb: Incorrect result for query that uses an AND operator on floats

[mrigger]

Consider the following statements:

CREATE TABLE t0(c0 BOOL);
INSERT INTO t0 VALUES (0);
SELECT * FROM t0 WHERE 1 AND 0.4; -- expected: {0}, actual: {}

Unexpectedly, the SELECT does not fetch any rows. 1 AND 0.4 should evaluate to 1, which means that the whole predicate should evaluate to TRUE. The negated predicate seems to correctly evaluate to FALSE:

SELECT * FROM t0 WHERE NOT (1 AND 0.4); -- {}

I found this bug with the following environment:

mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.12
Git Commit Hash: 8c4696b3f3408c61dd7454204ddd67c93501609a
Git Branch: heads/refs/tags/v3.0.12
UTC Build Time: 2020-03-16 09:56:22
GoVersion: go version go1.13 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[qw4990]

@mrigger Thanks for your feedback. This is a bug and we will fix it soon.

[qw4990]

It's funny ... I find that MySQL also has this problem @mrigger :

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t0(c0 BOOL);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t0 VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t0 WHERE 1 AND 0.4; 
Empty set (0.00 sec)

But after investigating, I think the reason is that MySQL and TiDB round the second argument 0.4 to 0, so the expression is converted to 1 and round(0.4) => 1 and 0 => false.
When you let the second argument be equal or larger than 0.5, you can read your data:

mysql> SELECT * FROM t0 WHERE 1 AND 0.4999;
Empty set (0.00 sec)

mysql> SELECT * FROM t0 WHERE 1 AND 0.5000;
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

I think we should let TiDB be compatible with MySQL, so the behavior is regarded as right... 🤣
What's your opinion? @zz-jason

[mrigger]

It might be that I previously also reported this case to MySQL; I'll check. It's definitely a bug though, and the predicate should evaluate to TRUE, which is the case when it is not used in the WHERE clause:

mysql> SELECT (1 AND 0.4999) IS TRUE;
+------------------------+
| (1 AND 0.4999) IS TRUE |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.01 sec)

mysql> SELECT (1 AND 0.5000) IS TRUE;
+------------------------+
| (1 AND 0.5000) IS TRUE |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

On a related note: https://github.com/pingcap/tidb/issues/15725 is another instance for a bug that affected a previous version of MySQL, and was then fixed, but still affects TiDB.

[mrigger]

I just checked and found that the latest version of MySQL is not affected by this bug:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t0(c0 BOOL);
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t0 VALUES (0);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t0 WHERE 1 AND 0.4;
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

[SunRunAway]

SELECT * FROM t0 WHERE 1 AND 0.4;

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.29    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t0(c0 BOOL);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t0 VALUES (0);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t0 WHERE 1 AND 0.4;
Empty set (0.00 sec)

MySQL 5.7 behaves differently, would you like to report a bug to the MySQL team, either? @mrigger

[mrigger]

I created a bug report in the MySQL bug tracker: https://bugs.mysql.com/bug.php?id=99120

---

Update: The bug has been verified.