复杂sql报错

💬 StarRocks 用户问答
#1

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】问题详细描述
【背景】做过哪些操作？
【业务影响】
【是否存算分离】
【StarRocks版本】例如：1.18.2
【集群规模】例如：3fe（1 follower+2observer）+5be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【联系方式】18840044216
【附件】
fe.log
com.starrocks.sql.common.StarRocksPlannerException: Invalid plan:
PhysicalTopNOperator {phase=FINAL, orderBy=[1264: empno ASC NULLS FIRST, 1273: expr ASC NULLS FIRST], limit=-1, offset=0}
-> PHYSICAL_FILTER
-> PHYSICAL_WINDOW
-> PhysicalTopNOperator {phase=FINAL, orderBy=[880: empno ASC NULLS FIRST, 994: substr ASC NULLS FIRST], limit=-1, offset=0}
-> PhysicalDistributionOperator {distributionSpec=SHUFFLE_AGG[880(true), 994(true)] ,globalDict=[]}
-> PhysicalHashJoinOperator{joinType=INNER JOIN, joinPredicate=872: coalesce = 1822: cast AND 873: coalesce = 904: apportionCustomer, limit=-1, predicate=null}
-> PhysicalDistributionOperator {distributionSpec=SHUFFLE_JOIN[872(true), 873(true)] ,globalDict=[]}
-> PhysicalCTEConsumeOperator{cteId=‘1’, limit=-1, predicate=872: coalesce IS NOT NULL AND 873: coalesce IS NOT NULL}
-> PhysicalDistributionOperator {distributionSpec=SHUFFLE_JOIN[1822(true), 904(true)] ,globalDict=[]}
-> PHYSICAL_NO_CTE
-> PhysicalHashJoinOperator{joinType=LEFT OUTER JOIN, joinPredicate=880: empno = 923: empno, limit=-1, predicate=null}
-> PhysicalDistributionOperator {distributionSpec=SHUFFLE_JOIN[880(true)] ,globalDict=[]}
-> PHYSICAL_FILTER
-> PhysicalDistributionOperator {distributionSpec=SHUFFLE_JOIN[923(true)] ,globalDict=[]}
-> PHYSICAL_FILTER
-> PHYSICAL_FILTER
-> PHYSICAL_WINDOW
-> PhysicalTopNOperator {phase=FINAL, orderBy=[1131: empno ASC NULLS FIRST, 1245: substr ASC NULLS FIRST], limit=-1, offset=0}
-> PhysicalDistributionOperator {distributionSpec=SHUFFLE_AGG[1131(true), 1245(true)] ,globalDict=[]}
-> PhysicalHashJoinOperator{joinType=RIGHT OUTER JOIN, joinPredicate=1123: coalesce = 1820: cast, limit=-1, predicate=null}
-> PhysicalDistributionOperator {distributionSpec=SHUFFLE_JOIN[1123(true)] ,globalDict=[]}
-> PhysicalCTEConsumeOperator{cteId=‘1’, limit=-1, predicate=null}
-> PhysicalDistributionOperator {distributionSpec=SHUFFLE_JOIN[1820(true)] ,globalDict=[]}
-> PHYSICAL_NO_CTE
-> PhysicalHashJoinOperator{joinType=LEFT OUTER JOIN, joinPredicate=1131: empno = 1174: empno, limit=-1, predicate=null}
-> PhysicalDistributionOperator {distributionSpec=SHUFFLE_JOIN[1131(true)] ,globalDict=[]}
-> PHYSICAL_FILTER
-> PhysicalDistributionOperator {distributionSpec=SHUFFLE_JOIN[1174(true)] ,globalDict=[]}
-> PHYSICAL_FILTER
Input dependency cols check failed. The required cols {1273} cannot obtain from input cols {1250,1251,1253,1254,1255,1258,1259,1260,1261,1262,1263,1264,1265,1266,1267,1268,1269,1270,1271,1272}.
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkInputCols(InputDependenciesChecker.java:173) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:107) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkJoinOpt(InputDependenciesChecker.java:112) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:66) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.checkOptExprWithOneChild(InputDependenciesChecker.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:64) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker$Visitor.visit(InputDependenciesChecker.java:71) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.InputDependenciesChecker.validate(InputDependenciesChecker.java:53) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.validate.PlanValidator.validatePlan(PlanValidator.java:54) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.Optimizer.optimizeByCost(Optimizer.java:221) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.Optimizer.optimize(Optimizer.java:130) ~[starrocks-fe.jar:?]
at com.starrocks.sql.StatementPlanner.createQueryPlan(StatementPlanner.java:142) ~[starrocks-fe.jar:?]
at com.starrocks.sql.StatementPlanner.planQuery(StatementPlanner.java:117) ~[starrocks-fe.jar:?]
at com.starrocks.sql.StatementPlanner.plan(StatementPlanner.java:92) ~[starrocks-fe.jar:?]
at com.starrocks.sql.StatementPlanner.plan(StatementPlanner.java:57) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:432) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:363) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:477) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:753) ~[starrocks-fe.jar:?]
at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:69) ~[starrocks-fe.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:834) ~[?:?]
报错截图：

1708509755643
17085097556431825×575 27.6 KB

#2

版本是1.18.2么？explain costs sql 结果看下

#3

版本是3.1.4 explain costs 后还是

企业微信截图_17085694468936
企业微信截图_170856944689361814×1347 133 KB

#4

你好 请参考这个文档搞个dump file . 我们排查下

京ICP备2022026421号-1