OR operation not correct in where clause

[Hexilee]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Load tidb-test.sql in test.zip, then execute:

SELECT * 
FROM table_varchar RIGHT JOIN table_float_varchar 
ON NULl 
WHERE (col_varchar_2 OR FALSE) IS NULL;

2. What did you expect to see? (Required)

(In mysql 8.0)

+------+---------------+------+-------------+---------------------+
| id_2 | col_varchar_2 | id_5 | col_float_5 | col_varchar_5       |
+------+---------------+------+-------------+---------------------+
| NULL | NULL          |    1 |        NULL | false               |
| NULL | NULL          |    2 |        NULL | NULL                |
| NULL | NULL          |    3 |        NULL |                     |
| NULL | NULL          |    4 |         0.5 | -1                  |
| NULL | NULL          |    5 |           0 | true                |
| NULL | NULL          |    6 |        NULL | NULL                |
| NULL | NULL          |    7 |         0.1 | -0                  |
| NULL | NULL          |    8 |          -1 | NULL                |
| NULL | NULL          |    9 |           0 | NULL                |
| NULL | NULL          |   10 |        NULL | -1                  |
| NULL | NULL          |   11 |         0.1 | 2020-02-02 02:02:00 |
| NULL | NULL          |   12 |         0.1 | 0000-00-00 00:00:00 |
| NULL | NULL          |   13 |           1 | NULL                |
| NULL | NULL          |   14 |         0.5 | NULL                |
| NULL | NULL          |   15 |         1.5 |                     |
| NULL | NULL          |   16 |         0.5 | NULL                |
| NULL | NULL          |   17 |           0 |                     |
+------+---------------+------+-------------+---------------------+
17 rows in set, 1 warning (0.03 sec)

3. What did you see instead (Required)

Empty set (0.00 sec)

4. Affected version (Required)

Release Version: v4.0.0-beta.2-517-gaf7bbbe24
Edition: Community
Git Commit Hash: af7bbbe2412f9a0174338526daa01fe270500806
Git Branch: HEAD
UTC Build Time: 2020-05-27 07:58:22
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

5. Root Cause Analysis

• Remove OR Operation

SELECT * 
FROM table_varchar RIGHT JOIN table_float_varchar 
ON NULl 
WHERE (col_varchar_2) IS NULL;

+------+---------------+------+-------------+---------------------+
| id_2 | col_varchar_2 | id_5 | col_float_5 | col_varchar_5       |
+------+---------------+------+-------------+---------------------+
| NULL | NULL          |    1 |        NULL | false               |
| NULL | NULL          |    2 |        NULL | NULL                |
| NULL | NULL          |    3 |        NULL |                     |
| NULL | NULL          |    4 |         0.5 | -1                  |
| NULL | NULL          |    5 |           0 | true                |
| NULL | NULL          |    6 |        NULL | NULL                |
| NULL | NULL          |    7 |         0.1 | -0                  |
| NULL | NULL          |    8 |          -1 | NULL                |
| NULL | NULL          |    9 |           0 | NULL                |
| NULL | NULL          |   10 |        NULL | -1                  |
| NULL | NULL          |   11 |         0.1 | 2020-02-02 02:02:00 |
| NULL | NULL          |   12 |         0.1 | 0000-00-00 00:00:00 |
| NULL | NULL          |   13 |           1 | NULL                |
| NULL | NULL          |   14 |         0.5 | NULL                |
| NULL | NULL          |   15 |         1.5 |                     |
| NULL | NULL          |   16 |         0.5 | NULL                |
| NULL | NULL          |   17 |           0 |                     |
+------+---------------+------+-------------+---------------------+
17 rows in set (0.01 sec)

[zhangysh1995]

A regression for fixing of this?

The previous one is wrong result of colNULL AND 1, which is manipulating a NULL column with AND.

[lzmhhh123]

@dyzsr PTAL.

[lzmhhh123]

/assign dyzsr

[dyzsr]

/assign @dyzsr