search

apache / doris

Apache Doris is an easy-to-use, high performance and unified analytics database.
https://doris.apache.org
Apache License 2.0
12.77k stars 3.28k forks source link

[Bug] Doris 嵌套子查询问题 #44271

Open awesomeleo opened 2 days ago

awesomeleo commented 2 days ago

Search before asking

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?

Code of Conduct

ixzc commented 1 day ago

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