"references invalid table" when querying CTE views

[tangenta]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

-- set up 2 same tables in different db.
create database if not exists test;
create database if not exists test1;
drop table if exists test.t, test1.t;
drop view if exists test.v;
create table test.t (a int);
insert into test.t values (1);
create table test1.t (a int);
insert into test1.t values (2);

use test;
create view test.v as with tt as (select * from t) select * from tt;
use test;
select * from test.v;
use test1;
select * from test.v;

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

mysql> use test;
Database changed
mysql> select * from test.v;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> use test1;
Database changed
mysql> select * from test.v;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

mysql> use test;
Database changed
mysql> select * from test.v;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> use test1;
Database changed
mysql> select * from test.v;
ERROR 1356 (HY000): View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

4. What is your TiDB version? (Required)

45e48b6

[tangenta]

This error is reported by BuildDataSourceFromView. This function converts several errors to ErrInvalidView:

tidb/planner/core/logical_plan_builder.go

Lines 4597 to 4607 in 334508e

	selectLogicalPlan, err := b.Build(ctx, selectNode)
	if err != nil {
	if terror.ErrorNotEqual(err, ErrViewRecursive) &&
	terror.ErrorNotEqual(err, ErrNoSuchTable) &&
	terror.ErrorNotEqual(err, ErrInternal) &&
	terror.ErrorNotEqual(err, ErrFieldNotInGroupBy) &&
	terror.ErrorNotEqual(err, ErrMixOfGroupFuncAndFields) {
	err = ErrViewInvalid.GenWithStackByArgs(dbName.O, tableInfo.Name.O)
	}
	return nil, err
	}

The actual error is ErrUnknownColumn, which is further reported by the expression rewriter. It complains that column is not found in the output names:

tidb/planner/core/expression_rewriter.go

Lines 1889 to 1894 in d981c0e

	func (er *expressionRewriter) toColumn(v *ast.ColumnName) {
	idx, err := expression.FindFieldName(er.names, v)
	if err != nil {
	er.err = ErrAmbiguous.GenWithStackByArgs(v.Name, clauseMsg[fieldList])
	return
	}

tidb/planner/core/expression_rewriter.go

Line 1932 in d981c0e

er.err = ErrUnknownColumn.GenWithStackByArgs(v.String(), clauseMsg[er.b.curClause])

We can compare these names:

er.names = []{{
    DBName = {model.CIStr} test1
    TblName = {model.CIStr} tt
    ColName = {model.CIStr} a
}}
v = {
    Schema = {model.CIStr} test
    Table = {model.CIStr} tt
    Name = {model.CIStr} a
}

Obviously, the DBName and Schema here are incorrect because the CTE table name does not belong to any database.

The select SQL stored by view is:

WITH `tt` AS (SELECT `test`.`t`.`a` AS `a` FROM `test`.`t`) SELECT `test`.`tt`.`a` AS `a` FROM `tt`