General error: 1064 Only support use correlated columns in the where clause of subqueries

💬 StarRocks 用户问答 功能使用相关
#1

【背景】刚升级到4.0.1
【是否存算分离】否
【StarRocks版本】4.0.1
【集群规模】 3fe +7be
【详述】创建一个elasticsearch的catalog，然后使用这个catalog进行查询报错：

General error: 1064 Only support use correlated columns in the where clause of subqueries

查询语句：

SQL: select `bid`, count(id) as total_count, sum(interaction_count) as interaction_count, 0 as cost, sum(if(pos > 0, 1, 0)) as pos_count, sum(if(pos < 0, 1, 0)) as neg_count from `test` where `bid` in (1) and `industry_id` = 1 and `bid` in (1) and `date` between 2025-01-01 and 2025-09-30 and `id` in (select `id` from `es_database`.`default_db`.`dev-test-*` where esquery(id, '{\"bool\":{\"must\":[{\"term\":{\"industryid\":{\"value\":1}}},{\"terms\":{\"bid\":[1]}},{\"range\":{\"date\":{\"gte\":\"2025-01-01\",\"lte\":\"2025-09-30\"}}},{\"bool\":{\"must\":[{\"bool\":{\"should\":[{\"match_phrase\":{\"text\":\"茶\"}},{\"match_phrase\":{\"image_text\":\"茶\"}},{\"match_phrase\":{\"video_text\":\"茶\"}}]}}]}}]}}')) group by `bid`

通过grant把表权限赋重新给用户之后第一次查询可以成功执行，过一会查询又报错。

#2

补充一下权限设置SQL：

GRANT SELECT ON ALL TABLES IN ALL DATABASES TO  USER 't@'%';
#3

报错又变成了：

 SQLSTATE[HY000]: General error: 1064 Getting analyzing error. Detail message: Column 'aid' cannot be resolved.

有时候还会变成es查询的表不存在：

SQLSTATE[42602]: Invalid name: 5502 Getting analyzing error. Detail message: Unknown table 'default_db.dev-test-*'.
#4

目前看来应该和设置权限没有关系，现在的情况是有时候查询能正常返回结果，有时候就报上面的错误。

#5

fe log：

2025-11-28 12:01:56.913+08:00 INFO (starrocks-mysql-nio-pool-374|4251) [StmtExecutor.execute():885] execute Exception, sql: select ..., error: Getting analyzing error. Detail message: Column 'id' cannot be resolved.

#6

其他错误日志：

2025-11-28 12:03:49.783+08:00 WARN (starrocks-mysql-nio-pool-391|4274) [StmtExecutor.execute():901] execute Exception, sql: {}, select ......

com.starrocks.sql.common.UnsupportedException: Only support use correlated columns in the where clause of subqueries

at com.starrocks.sql.common.UnsupportedException.unsupportedException(UnsupportedException.java:23) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.SqlToScalarOperatorTranslator.translate(SqlToScalarOperatorTranslator.java:169) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.QueryTransformer.project(QueryTransformer.java:267) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.QueryTransformer.project(QueryTransformer.java:257) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.QueryTransformer.plan(QueryTransformer.java:140) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.RelationTransformer.visitSelect(RelationTransformer.java:290) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.RelationTransformer.visitSelect(RelationTransformer.java:162) ~[starrocks-fe.jar:?]

at com.starrocks.sql.ast.SelectRelation.accept(SelectRelation.java:232) ~[starrocks-fe.jar:?]

at com.starrocks.sql.ast.AstVisitor.visit(AstVisitor.java:106) ~[starrocks-fe.jar:?]

at com.starrocks.sql.ast.AstVisitor.visit(AstVisitor.java:102) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.RelationTransformer.transform(RelationTransformer.java:228) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.SubqueryUtils.getLogicalPlan(SubqueryUtils.java:108) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.SqlToScalarOperatorTranslator$Visitor.getSubqueryPlan(SqlToScalarOperatorTranslator.java:559) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.SqlToScalarOperatorTranslator$Visitor.visitInPredicate(SqlToScalarOperatorTranslator.java:644) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.SqlToScalarOperatorTranslator$Visitor.visitInPredicate(SqlToScalarOperatorTranslator.java:280) ~[starrocks-fe.jar:?]

at com.starrocks.analysis.InPredicate.accept(InPredicate.java:183) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.SqlToScalarOperatorTranslator$Visitor.visit(SqlToScalarOperatorTranslator.java:318) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.SqlToScalarOperatorTranslator$Visitor.visitCompoundPredicate(SqlToScalarOperatorTranslator.java:462) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.SqlToScalarOperatorTranslator$Visitor.visitCompoundPredicate(SqlToScalarOperatorTranslator.java:280) ~[starrocks-fe.jar:?]

at com.starrocks.analysis.CompoundPredicate.accept(CompoundPredicate.java:150) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.SqlToScalarOperatorTranslator$Visitor.visit(SqlToScalarOperatorTranslator.java:318) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.SqlToScalarOperatorTranslator.translate(SqlToScalarOperatorTranslator.java:192) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.QueryTransformer.filter(QueryTransformer.java:296) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.QueryTransformer.plan(QueryTransformer.java:99) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.RelationTransformer.visitSelect(RelationTransformer.java:290) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.RelationTransformer.visitSelect(RelationTransformer.java:162) ~[starrocks-fe.jar:?]

at com.starrocks.sql.ast.SelectRelation.accept(SelectRelation.java:232) ~[starrocks-fe.jar:?]

at com.starrocks.sql.ast.AstVisitor.visit(AstVisitor.java:106) ~[starrocks-fe.jar:?]

at com.starrocks.sql.ast.AstVisitor.visit(AstVisitor.java:102) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.RelationTransformer.transform(RelationTransformer.java:228) ~[starrocks-fe.jar:?]

at com.starrocks.sql.optimizer.transformer.RelationTransformer.transformWithSelectLimit(RelationTransformer.java:196) ~[starrocks-fe.jar:?]

at com.starrocks.sql.StatementPlanner.createQueryPlan(StatementPlanner.java:312) ~[starrocks-fe.jar:?]

at com.starrocks.sql.StatementPlanner.plan(StatementPlanner.java:151) ~[starrocks-fe.jar:?]

at com.starrocks.sql.StatementPlanner.plan(StatementPlanner.java:109) ~[starrocks-fe.jar:?]

at com.starrocks.qe.StmtExecutor.generateExecPlan(StmtExecutor.java:592) ~[starrocks-fe.jar:?]

at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:674) ~[starrocks-fe.jar:?]

at com.starrocks.qe.ConnectProcessor.executeQueryAttempt(ConnectProcessor.java:506) ~[starrocks-fe.jar:?]

at com.starrocks.qe.ConnectProcessor.runWithParserStageRetry(ConnectProcessor.java:404) ~[starrocks-fe.jar:?]

at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:349) ~[starrocks-fe.jar:?]

at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:703) ~[starrocks-fe.jar:?]

at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:1050) ~[starrocks-fe.jar:?]

at com.starrocks.mysql.nio.MySQLReadListener.handleRequest(MySQLReadListener.java:100) ~[starrocks-fe.jar:?]

at com.starrocks.mysql.nio.MySQLReadListener.lambda$handleEvent$0(MySQLReadListener.java:86) ~[starrocks-fe.jar:?]

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[?:?]

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[?:?]

at java.lang.Thread.run(Thread.java:842) ~[?:?]
#7

升级后出问题的，还是都有问题，能给个复现方法吗，ES是哪个版本。

#8

我们是升级后才测试的es catalog的使用，之前我们一直用的是es外部表，看新版本文档里不鼓励再使用外部表推荐catalog才做的迁移测试，不确定之前的版本有没有这个问题，我们只在4.0.1的版本上测试了这个。

我们用的es的版本是8.13.2，其他的没什么特殊的。就是创建一个新的es catalog之后，使用上面的查询语句进行查询，对es的查询用的是子查询，有时候查询能正常，有时候就报错。

创建catalog的语句：

CREATE EXTERNAL CATALOG `es_database`
comment "es数据库"
PROPERTIES (
"enable_docvalue_scan"  =  "true",
"es.nodes.wan.only"  =  "true",
"hosts"  =  "",
"es.net.ssl"  =  "false",
"type"  =  "es",
"user"  =  "",
"enable_keyword_sniff"  =  "true"
);

京ICP备2022026421号-1