P0-[4.0 bug hunting]-[SQL Plan Management]-NATURAL LEFT JOIN results in incorrect result for <=> operator

[mrigger]

Consider the following statements:

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
INSERT INTO t0 VALUES (0);
INSERT INTO t1 VALUES (0);
SELECT * FROM t1 NATURAL LEFT JOIN t0 WHERE NOT(t0.c0<=>t1.c0); -- expected: {}, actual: {0}

Unexpectedly, the SELECT fetches a row:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1452
Server version: 5.7.25-TiDB-v4.0.0-beta.2-231-gc66320c46 TiDB Server (Apache License 2.0), MySQL 5.7 compatible

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> CREATE TABLE t1(c0 INT);
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> SELECT * FROM t1 NATURAL LEFT JOIN t0 WHERE NOT(t0.c0<=>t1.c0); -- expected: {}, actual: {0}
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

Interestingly, the following query with the negated predicate also fetches a row, which is expected:

SELECT t1.c0 FROM t1 NATURAL LEFT JOIN t0 WHERE t0.c0<=>t1.c0 -- {0}

I double-checked with MySQL 8.0.19, which does not fetch a row for the first query, as expected:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> CREATE TABLE t1(c0 INT);
Query OK, 0 rows affected (0.04 sec)

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

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

mysql> SELECT * FROM t1 NATURAL LEFT JOIN t0 WHERE NOT(t0.c0<=>t1.c0); -- expected: {}, actual: {0}
Empty set (0.00 sec)

Environment:

| Release Version: v4.0.0-beta.2-231-gc66320c46
Git Commit Hash: c66320c46456c0d5b23b3b0403be6b9f8227d6d8
Git Branch: master
UTC Build Time: 2020-04-14 11:12:29
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

I propose P0 as a bug level, since it matches the following description: wrong results returned by the query, and inconsistent results returned by the SQL output.

[winkyao]

Could you please try 4.0.0 rc version? https://github.com/pingcap/tidb/releases/tag/v4.0.0-rc

[mrigger]

I can also reproduce this on the RC version:

Server version: 5.7.25-TiDB- TiDB Server (Apache License 2.0), MySQL 5.7 compatible

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP DATABASE db0;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE DATABASE db0;
Query OK, 0 rows affected (0.00 sec)

mysql> USE db0;
Database changed
mysql> 
mysql> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1(c0 INT);
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> SELECT * FROM t1 NATURAL LEFT JOIN t0 WHERE NOT(t0.c0<=>t1.c0); -- expected: {}, actual: {0}
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: 
Git Commit Hash: 
Git Branch: 
UTC Build Time: 2020-04-15 06:06:10
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[shuke987]

Thank you for submitting this bug. It returns error but it is a corner case and appears less frequently. Do you accept rating it as a P1 bug？

[mrigger]

Yes, feel free to also assign a P1 rating for other, similar cases (see my response at https://github.com/tidb-challenge-program/bug-hunting-issue/issues/4#issuecomment-613934230).

[shuke987]

/bug P1