【详述】2.3.8升级到2.5.4，concat函数报错
【背景】测试环境从2.3.8升级到2.5.4，concat函数报错，同样的SQL在原来2.3.8环境跑正常，在升级到2.5.4.报错，SQL语句，如下：
select
module,
EQPID,
mfgmonth ,
case
when mfgmonth = date_format(now(), ‘%Y-M%m’) then round(sum(total_time)/(dayofmonth(now())* 24 * 3600), 2)
else concat((REPLACE(mfgmonth, ‘M’, ‘’)), ‘01’)
end mth_time
from
(
select
c.module,
EQPID,
mfgmonth ,
total_time
from
(
select
testdate as mfgdate,
l.mfgmonth,
l.mfg_week as mfgweek,
EQPID,
total_time
from
(
select
EQPID,
REPLACE(testdate,
‘-’,
‘’)testdate ,
sum(total_time) total_time
from
(
select
toolid as EQPID,
testdate,
sum(time_difference) total_time
from
dx_ie.t_dwd_lab_site_user_summary
where
1 = 1
group by
toolid,
testdate
UNION all
SELECT
EQPID,
TEST_TIME,
sum(END_TIME-START_TIME) total_time
from
dx_ie.t_dwd_lab_eqp_upt
where
1 = 1
group by
EQPID,
TEST_TIME ) d
group by
EQPID,
testdate
order by
EQPID,
testdate ) e
left join dx_ie.rpt_brt_calendar_lst l on
e.testdate = l.mfgdate
where
1 = 1
and mfgdate >= date_format(date_sub(NOW(), interval 6 MONTH), ‘%Y%m%d’) ) f
left join dx_ie.t_tgt_lab_eqp_module_config c on
f.eqpid = c.eqid
where
1 = 1 ) r
group by
module,
EQPID,
mfgmonth
ORDER BY
mfgmonth desc
【StarRocks版本】2.5.4
【集群规模】3fe（3 follower）+5be
【联系方式】15623937986
【附件】
FE报错日志，如下：
group by
module,
EQPID,
mfgmonth
ORDER BY
mfgmonth desc
java.lang.IllegalStateException: null
at com.google.common.base.Preconditions.checkState(Preconditions.java:494) ~[spark-dpp-1.0.0.jar:?]
at com.starrocks.sql.optimizer.rule.tree.DictMappingRewriter$Rewriter.rewriteForScalarOperator(DictMappingRewriter.java:96) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.DictMappingRewriter$Rewriter.visitCall(DictMappingRewriter.java:149) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.DictMappingRewriter$Rewriter.visitCall(DictMappingRewriter.java:74) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.operator.scalar.CallOperator.accept(CallOperator.java:186) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.DictMappingRewriter$Rewriter.addDictExprToBlockDictOpt(DictMappingRewriter.java:118) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.DictMappingRewriter$Rewriter.visit(DictMappingRewriter.java:139) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.DictMappingRewriter$Rewriter.rewriteForScalarOperator(DictMappingRewriter.java:106) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.DictMappingRewriter$Rewriter.visitCall(DictMappingRewriter.java:149) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.DictMappingRewriter$Rewriter.visitCall(DictMappingRewriter.java:74) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.operator.scalar.CallOperator.accept(CallOperator.java:186) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.DictMappingRewriter.rewrite(DictMappingRewriter.java:42) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.rewriteOneScalarOperatorForProjection(AddDecodeNodeForDictStringRule.java:544) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.rewriteProjectOperator(AddDecodeNodeForDictStringRule.java:473) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitProjectionAfter(AddDecodeNodeForDictStringRule.java:247) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalHashAggregate(AddDecodeNodeForDictStringRule.java:806) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalHashAggregate(AddDecodeNodeForDictStringRule.java:171) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.operator.physical.PhysicalHashAggregateOperator.accept(PhysicalHashAggregateOperator.java:173) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalTopN(AddDecodeNodeForDictStringRule.java:313) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalTopN(AddDecodeNodeForDictStringRule.java:171) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.operator.physical.PhysicalTopNOperator.accept(PhysicalTopNOperator.java:113) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalTopN(AddDecodeNodeForDictStringRule.java:313) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalTopN(AddDecodeNodeForDictStringRule.java:171) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.operator.physical.PhysicalTopNOperator.accept(PhysicalTopNOperator.java:113) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule.rewrite(AddDecodeNodeForDictStringRule.java:913) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.Optimizer.physicalRuleRewrite(Optimizer.java:463) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.Optimizer.optimizeByCost(Optimizer.java:171) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.Optimizer.optimize(Optimizer.java:93) ~[starrocks-fe.jar:?]
at com.starrocks.sql.StatementPlanner.createQueryPlan(StatementPlanner.java:95) ~[starrocks-fe.jar:?]
at com.starrocks.sql.StatementPlanner.plan(StatementPlanner.java:66) ~[starrocks-fe.jar:?]
at com.starrocks.sql.StatementPlanner.plan(StatementPlanner.java:37) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:379) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:323) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:440) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:698) ~[starrocks-fe.jar:?]
at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:55) ~[starrocks-fe.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_181]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_181]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_181]
select
module,
EQPID,
mfgmonth ,
case
when mfgmonth = date_format(now(), ‘%Y-M%m’) then concat((REPLACE(mfgmonth, ‘M’, ‘’)), ‘01’)
else concat((REPLACE(mfgmonth, ‘M’, ‘’)), ‘01’)
end mth_time
from
(
select
c.module,
EQPID,
mfgmonth ,
total_time
from
(
select
testdate as mfgdate,
l.mfgmonth,
l.mfg_week as mfgweek,
EQPID,
total_time
from
(
select
EQPID,
REPLACE(testdate,
‘-’,
‘’)testdate ,
sum(total_time) total_time
from
(
select
toolid as EQPID,
testdate,
sum(time_difference) total_time
from
dx_ie.t_dwd_lab_site_user_summary
where
1 = 1
group by
toolid,
testdate
UNION all
SELECT
EQPID,
TEST_TIME,
sum(END_TIME-START_TIME) total_time
from
dx_ie.t_dwd_lab_eqp_upt
where
1 = 1
group by
EQPID,
TEST_TIME ) d
group by
EQPID,
testdate
order by
EQPID,
testdate ) e
left join dx_ie.rpt_brt_calendar_lst l on
e.testdate = l.mfgdate
where
1 = 1
and mfgdate >= date_format(date_sub(NOW(), interval 6 MONTH), ‘%Y%m%d’) ) f
left join dx_ie.t_tgt_lab_eqp_module_config c on
f.eqpid = c.eqid
where
1 = 1 ) r
group by
module,
EQPID,
mfgmonth
ORDER BY
mfgmonth desc
case when 里面分别执行又不报错，很奇怪。
2.5.0以上版本中，有些函数逻辑是调整过的，这个去官网升级日志里看一下。
2.5.5 修复过了
select
module,
EQPID,
mfgmonth ,
case
when mfgmonth = date_format(now(), ‘%Y-M%m’) then round(sum(total_time)/(dayofmonth(now())* 24 * 3600), 2)
else day(DAYS_SUB(months_add(STR_TO_DATE(concat(REPLACE(REPLACE(mfgmonth, ‘M’, ‘’), ‘-’, ‘’), ‘01’), ‘%Y%m%d’), 1), 1))
end mth_time
from
(
select
c.module,
EQPID,
mfgmonth ,
total_time
from
(
select
testdate as mfgdate,
l.mfgmonth,
l.mfg_week as mfgweek,
EQPID,
total_time
from
(
select
EQPID,
REPLACE(testdate,
‘-’,
‘’)testdate ,
sum(total_time) total_time
from
(
select
toolid as EQPID,
testdate,
sum(time_difference) total_time
from
dx_ie.t_dwd_lab_site_user_summary
where
1 = 1
group by
toolid,
testdate
UNION all
SELECT
EQPID,
TEST_TIME,
sum(END_TIME-START_TIME) total_time
from
dx_ie.t_dwd_lab_eqp_upt
where
1 = 1
group by
EQPID,
TEST_TIME ) d
group by
EQPID,
testdate
order by
EQPID,
testdate ) e
left join dx_ie.rpt_brt_calendar_lst l on
e.testdate = l.mfgdate
where
1 = 1
and mfgdate >= date_format(date_sub(NOW(), interval 6 MONTH), ‘%Y%m%d’) ) f
left join dx_ie.t_tgt_lab_eqp_module_config c on
f.eqpid = c.eqid
where
1 = 1 ) r
group by
module,
EQPID,
mfgmonth
ORDER BY
mfgmonth desc
这个SQL仍然报错，帮忙看下。
确定是升到2.5.5了吗？
确定升级到2.5.5，如下：
| EditLogPort | HttpPort | QueryPort | RpcPort | Role | ClusterId | Join | Alive | ReplayedJournalId | LastHeartbeat | IsHelper | ErrMsg | StartTime | Version |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 19010 | 18030 | 19030 | 19020 | FOLLOWER | 461923451 | true | true | 63924224 | 2023-05-24 11:03:10 | true | 2023-05-19 14:25:31 | 2.5.5-24c1eca | |
| 19010 | 18030 | 19030 | 19020 | LEADER | 461923451 | true | true | 63924223 | 2023-05-24 11:03:10 | true | 2023-05-19 14:25:06 | 2.5.5-24c1eca | |
| 19010 | 18030 | 19030 | 19020 | FOLLOWER | 461923451 | true | true | 63924223 | 2023-05-24 11:03:10 | true | 2023-05-19 14:24:34 | 2.5.5-24c1eca |
@trueeyu 老师您好，我在使用2.5.13版本时偶尔也会遇到类似报错，只能临时在会话中将cbo_enable_low_cardinality_optimize置为false来规避。不知道是不是同一个问题：
2024-02-20 09:32:18,029 WARN (starrocks-mysql-nio-pool-331|28388) [StmtExecutor.execute():551] execute Exception, sql SELECT CASE WHEN assignee_id = '' THEN '' ELSE SUBSTR(MD5(assignee_id), 1, 8) END AS sample_value FROM data_center.mart_board_issues_basic LIMIT 10
java.lang.IllegalStateException: null
at com.google.common.base.Preconditions.checkState(Preconditions.java:496) ~[spark-dpp-1.0.0.jar:?]
at com.starrocks.sql.optimizer.rule.tree.DictMappingRewriter.rewriteAsDictMapping(DictMappingRewriter.java:67) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.DictMappingRewriter.rewrite(DictMappingRewriter.java:46) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.rewriteOneScalarOperatorForProjection(AddDecodeNodeForDictStringRule.java:560) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.rewriteProjectOperator(AddDecodeNodeForDictStringRule.java:489) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitProjectionAfter(AddDecodeNodeForDictStringRule.java:262) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalOlapScan(AddDecodeNodeForDictStringRule.java:458) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalOlapScan(AddDecodeNodeForDictStringRule.java:171) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.operator.physical.PhysicalOlapScanOperator.accept(PhysicalOlapScanOperator.java:138) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalDistribution(AddDecodeNodeForDictStringRule.java:840) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalDistribution(AddDecodeNodeForDictStringRule.java:171) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.operator.physical.PhysicalDistributionOperator.accept(PhysicalDistributionOperator.java:44) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalLimit(AddDecodeNodeForDictStringRule.java:308) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalLimit(AddDecodeNodeForDictStringRule.java:171) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.operator.physical.PhysicalLimitOperator.accept(PhysicalLimitOperator.java:33) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule.rewrite(AddDecodeNodeForDictStringRule.java:930) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.Optimizer.physicalRuleRewrite(Optimizer.java:484) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.Optimizer.optimizeByCost(Optimizer.java:174) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.Optimizer.optimize(Optimizer.java:95) ~[starrocks-fe.jar:?]
at com.starrocks.sql.StatementPlanner.createQueryPlanWithReTry(StatementPlanner.java:181) ~[starrocks-fe.jar:?]
at com.starrocks.sql.StatementPlanner.planQuery(StatementPlanner.java:103) ~[starrocks-fe.jar:?]
at com.starrocks.sql.StatementPlanner.plan(StatementPlanner.java:73) ~[starrocks-fe.jar:?]
at com.starrocks.sql.StatementPlanner.plan(StatementPlanner.java:44) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:402) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:327) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:444) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:711) ~[starrocks-fe.jar:?]
at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:55) ~[starrocks-fe.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_392]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_392]
at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_392]
是查询的时候出现的吗？
原因已确定，我们尽快修复下。