P0-[4.0 bug hunting]-[SQL Plan Management]-Incorrect result when using the empty string as a predicate in a RIGHT JOIN

[mrigger]

Consider the following statements:

CREATE TABLE t0(c0 CHAR);
CREATE TABLE t1(c0 CHAR UNIQUE);
INSERT INTO t1(c0) VALUES ('');
SELECT t0.c0 FROM t0 RIGHT JOIN t1 ON true WHERE t1.c0; -- expected: {}, actual: {NULL}

Unexpectedly, the query fetches a row:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2041
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 CHAR);
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> SELECT t0.c0 FROM t0 RIGHT JOIN t1 ON true WHERE t1.c0;
+------+
| c0   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

When removing the UNIQUE constraint, the NULL value is not fetched. MySQL 8.0.19 computes the result I would expect:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
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 CHAR);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t1(c0 CHAR UNIQUE);
Query OK, 0 rows affected (0.06 sec)

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

mysql> SELECT t0.c0 FROM t0 RIGHT JOIN t1 ON true WHERE t1.c0;
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 reproduce it based on this version:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
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 CHAR);
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> SELECT t0.c0 FROM t0 RIGHT JOIN t1 ON true WHERE t1.c0; -- expected: {}, actual: {NULL}
+------+
| c0   |
+------+
| NULL |
+------+
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)

[mrigger]

I noticed that I haven't received points for this issue yet. I also checked MySQL 5.7 (using http://sqlfiddle.com), which computes the result I would expect.

[shuke987]

/bug P1