P0-[4.0 bug hunting]-[SQL Plan Management]-Incorrect result when fetching from a view

[mrigger]

Consider the following statements:

CREATE TABLE t0(c0 INT);
CREATE VIEW v0(c0, c1) AS SELECT t0.c0, 1 FROM t0;
INSERT INTO t0 VALUES (0);
SELECT v0.c0 FROM v0, t0 WHERE v0.c1; -- expected: {0}, actual: {}

Unexpectedly, the SELECT does not fetch any rows:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2478
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.00 sec)

mysql> CREATE VIEW v0(c0, c1) AS SELECT t0.c0, 1 FROM t0;
Query OK, 0 rows affected (0.01 sec)

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

mysql> SELECT v0.c0 FROM v0, t0 WHERE v0.c1; -- expected: {0}, actual: {}
Empty set (0.00 sec)

Interestingly, the query work as expected when fetching all columns, and not only v0.c0:

SELECT * FROM v0, t0 WHERE v0.c1; -- {0|1|0}

I checked with MySQL 8.0.19, which computes the result I would expect:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
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> 
mysql> CREATE TABLE t0(c0 INT);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE VIEW v0(c0, c1) AS SELECT t0.c0, 1 FROM t0;
Query OK, 0 rows affected (0.01 sec)

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

mysql> SELECT v0.c0 FROM v0, t0 WHERE v0.c1; -- expected: {0}, actual: {}
+------+
| c0   |
+------+
|    0 |
+------+
1 row in 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

[shuke987]

4.0 RC has this bug.

[shuke987]

/bug p0 n

[mrigger]

@shuke987 Thanks for checking! I double checked and confirm that the 4.0 RC has this bug:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
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.01 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.01 sec)

mysql> CREATE VIEW v0(c0, c1) AS SELECT t0.c0, 1 FROM t0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT v0.c0 FROM v0, t0 WHERE v0.c1; -- expected: {0}, actual: {}
Empty set (0.00 sec)

[shuke987]

/bug P1