P0-[4.0 bug hunting]-[WHERE clause]-Wrong NULL value evaluation result in INCORRECT query results

[zhangysh1995]

Bug Report

1. What did you do?

Running wreck-it with the default command line.

mysql> desc table_int_float;
+-----------+---------+------+------+---------+----------------+
| Field     | Type    | Null | Key  | Default | Extra          |
+-----------+---------+------+------+---------+----------------+
| id        | int(16) | NO   | PRI  | NULL    | auto_increment |
| col_int   | int(16) | YES  |      | NULL    |                |
| col_float | float   | YES  | MUL  | NULL    |                |
+-----------+---------+------+------+---------+----------------+
3 rows in set (0.00 sec)

mysql> select col_float from table_varchar_float;
+-----------+
| col_float |
+-----------+
|      NULL |
|        -1 |
|      -0.1 |
|      NULL |
|       1.5 |
|       0.1 |
|      NULL |
|      NULL |
|         0 |
|       0.5 |
|      NULL |
|      NULL |
|      -0.1 |
|         1 |
|      NULL |
|       1.5 |
|      NULL |
|      NULL |
|      NULL |
+-----------+
19 rows in set (0.00 sec)

mysql> SELECT * FROM table_varchar_float WHERE !(table_varchar_float.col_float and 1) IS NULL;
Empty set (0.00 sec)

2. What did you expect to see?

It should return some rows in the table (where the col_float=NULL).

mysql> select !(NULL and 1) is NULL;
+-----------------------+
| !(NULL and 1) is NULL |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

3. What did you see instead?

It returns empty set.

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

mysql> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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]

/bug P1

[zhangysh1995]

@shuke987 Not sure why this is P1 but not P0, it could cause wrong return results. Could you explain the reason that I could better tag the later new bugs?

[SunRunAway]

@Reminiscent
Is this issue the same problem that pingcap/tidb#16542 will resolve?

[Reminiscent]

@SunRunAway Sure. This issue can be fixed by #16542. Should we close this issue?

[SunRunAway]

@shuke987 This issue is fixed by pingcap/tidb#16542 and can be closed.

[shuke987]

@zhangysh1995 hi, this is a bug which causes wrong return results, but it is an expression corner case. We treat these corner cases as P1 bug.

[zhangysh1995]

@zhangysh1995 hi, this is a bug which causes wrong return results, but it is an expression corner case. We treat these corner cases as P1 bug.

Thanks for the explanation! You may close the issue now! @shuke987