为了更快的定位您的问题，请提供以下信息，谢谢
【详述】问题详细描述
嵌套子查询超过两层后，内部不能识别外部定义的表的别名
【背景】做过哪些操作？
1.创建表
CREATE TABLE customers (
cust_id int(11) NOT NULL COMMENT “”,
cust_name varchar(150) NOT NULL COMMENT “”
) ENGINE=OLAP
PRIMARY KEY( cust_id )
DISTRIBUTED BY HASH( cust_id )
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“enable_persistent_index” = “true”,
“replicated_storage” = “true”,
“compression” = “LZ4”
);
CREATE TABLE orderitems (
order_num int(11) NOT NULL COMMENT “”,
order_item int(11) NOT NULL COMMENT “”,
prod_id varchar(30) NOT NULL COMMENT “”
) ENGINE=OLAP
PRIMARY KEY( order_num , order_item )
DISTRIBUTED BY HASH( order_num , order_item )
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“enable_persistent_index” = “true”,
“replicated_storage” = “true”,
“compression” = “LZ4”
);
CREATE TABLE orders (
order_num int(11) NOT NULL COMMENT “”,
order_date datetime NOT NULL COMMENT “”,
cust_id int(11) NOT NULL COMMENT “”
) ENGINE=OLAP
PRIMARY KEY( order_num )
DISTRIBUTED BY HASH( order_num )
PROPERTIES (
“replication_num” = “1”,
“in_memory” = “false”,
“enable_persistent_index” = “true”,
“replicated_storage” = “true”,
“compression” = “LZ4”
);
执行查询
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 1064 (HY000): Getting analyzing error. Detail message: Column ’ test . cm . cust_name ’ cannot be resolved.
Expected behavior (Required)
能够执行成功
Real behavior (Required)
报错，内部子查询不能识别外部定义的表的别名
StarRocks version (Required)
- 尝试过
- StarRocks3.1.14
- StarRocks3.2.29
表现相同
PS：两层的子查询可以执行，超过三层子查询就不能识别
select cust_name, cust_id from customers cm where cust_id in (select cust_id from orders where order_num in (20005, 20007) and cm.cust_name=‘Yosemite Place’);
±---------------±--------+
| cust_name | cust_id |
±---------------±--------+
| Yosemite Place | 10004 |
±---------------±--------+
【业务影响】
查询执行失败
【是否存算分离】否
【StarRocks版本】3.1.14 和3.2.29
【集群规模】1fe+1be
【机器信息】CPU虚拟核/内存/网卡，例如：80C/360G/万兆