Tidb: NATURAL RIGHT JOIN results in an unexpected "Unknown column" error

[mrigger]

Consider the following statements:

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
SELECT t0.c0 FROM t0 NATURAL RIGHT JOIN t1 WHERE t1.c0; -- Unknown column 't0.c0' in 'field list'

Unexpectedly, the SELECT results in an error ERROR 1054 (42S22): Unknown column 't0.c0' in 'field list'. I would expect the SELECT to execute without errors, fetching no rows.

When removing the WHERE condition, the query executes as expected:

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
SELECT t0.c0 FROM t0 NATURAL RIGHT JOIN t1; -- {}

It also works to not explicitly refer to t0.c0:

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
SELECT * FROM t0 NATURAL RIGHT JOIN t1; -- {}

Environment:

mysql> SELECT tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                               |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-beta.2-75-ga6de0e38d-dirty
Git Commit Hash: a6de0e38d49c97671d316590c8c945eb518ca2b2
Git Branch: master
UTC Build Time: 2020-03-26 12:11:33
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)

[XuHuaiyu]

Thanks for your feedback.

[zz-jason]

@winoros PTAL

[winoros]

@mrigger Thanks for your reporting! This issue needs some big changes in planner's building phase. We'll need some time to solve it.

[lzmhhh123]

/assign

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: https://github.com/pingcap/tidb/issues/20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions