[Bug] Doris 嵌套子查询问题

[awesomeleo]

Search before asking

• I had searched in the issues and found no similar issues.

Version

doris 2.0.9

What's Wrong?

嵌套子查询，在第三层以上的子查询不能访问父节点的父节点定义的表的别名

What You Expected?

MySQL可以执行，希望Doris也能正常执行

How to Reproduce?

CREATE TABLE customers (
cust_id int(11) NOT NULL,
cust_name varchar(150) NOT NULL
) ENGINE=OLAP
UNIQUE KEY(cust_id)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(cust_id) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);

CREATE TABLE orderitems (
order_num int(11) NOT NULL,
order_item int(11) NOT NULL,
prod_id varchar(30) NOT NULL
) ENGINE=OLAP
UNIQUE KEY(order_num, order_item)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(order_num, order_item) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);

CREATE TABLE orders (
order_num int(11) NOT NULL,
order_date datetime NOT NULL,
cust_id int(11) NOT NULL
) ENGINE=OLAP
UNIQUE KEY(order_num)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(order_num) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"is_being_synced" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);

select cust_name, cust_id from customers cm where cust_id in (select cust_id from orders where order_num in ( select order_num from orderitems where prod_id = 'TNT2' and cm.cust_name='Place'));

执行报错：
ERROR 1105 (HY000): errCode = 2, detailMessage = errCode = 2, detailMessage = errCode = 2, detailMessage = Unknown column 'cust_name' in 'cm'
ERROR 1064 (HY000): Getting analyzing error. Detail message: Column 'test.cm.cust_name' cannot be resolved.

把cm.cust_name='Place' 移到上一层就可以执行
select cust_name, cust_id from customers cm where cust_id in (select cust_id from orders where order_num in ( select order_num from orderitems where prod_id = 'TNT2')) and cm.cust_name='Place' ;

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[ixzc]

After the logic for binding fields supports multi-level scope, the logic for solving subqueries does not. So the bound field now also only binds the current scope and the previous level scope