【StarRocks版本】2.4.4/2.5.2
【集群规模】1fe+4Be
使用多字符串函数嵌套时，报ERROR 1064 (HY000): Unknown error
MySQL [ads_pc]> SELECT REVERSE(SUBSTR(LEFT(REVERSE(template_name),INSTR(REVERSE(template_name),’/’)-1),5)) FROM ads_pc.bda_pv_log;
ERROR 1064 (HY000): Unknown error
在starrock 2.3.9是正常的。
将SQL简化也能正常：
MySQL [ads_pc]> SELECT INSTR(REVERSE(template_name),’/’) FROM ads_pc.bda_pv_log ;
±-----------------------------------+
| instr(reverse(template_name), ‘/’) |
±-----------------------------------+
| 9 |
±-----------------------------------+
1 row in set (0.02 sec)
MySQL [ads_pc]> SELECT REVERSE(SUBSTR(LEFT(REVERSE(template_name),8),5)) FROM ads_pc.bda_pv_log ;
±----------------------------------------------------+
| reverse(substr(left(reverse(template_name), 8), 5)) |
±----------------------------------------------------+
| 风险明细 |
±----------------------------------------------------+
重现信息：
CREATE TABLE bda_pv_log (
created_time DATETIME NOT NULL COMMENT “创建时间”,
template_name VARCHAR(100) NOT NULL COMMENT “模板名称”,
template_parameter VARCHAR(65533) NULL COMMENT “模板参数”,
created_by VARCHAR(10) NOT NULL COMMENT “创建人”
) ENGINE=OLAP
DUPLICATE KEY(created_time, template_name)
COMMENT “PV LOG StarRocks明细模型”
DISTRIBUTED BY HASH(created_time) BUCKETS 10
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);
INSERT INTO bda_pv_log VALUES (‘2023-4-25 17:06:44’, ‘/中心/数据分析/供应链风险/风险明细.cpt’, ‘|105301|法律x诉讼||2023-04-25’, ‘lang22’);
fe.warning.log:
2023-04-25 17:36:14,104 WARN (starrocks-mysql-nio-pool-13|438) [StmtExecutor.execute():525] execute Exception, sql SELECT REVERSE(SUBSTR(LEFT(REVERSE(template_name),INSTR(REVERSE(template_name),’/’)-1),5)) FROM ads_pc.bda_pv_log limit 1
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:179) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.DictMappingRewriter$Rewriter.rewriteForScalarOperator(DictMappingRewriter.java:92) ~[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:179) ~[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:550) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.rewriteProjectOperator(AddDecodeNodeForDictStringRule.java:475) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitProjectionAfter(AddDecodeNodeForDictStringRule.java:260) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalOlapScan(AddDecodeNodeForDictStringRule.java:448) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalOlapScan(AddDecodeNodeForDictStringRule.java:175) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.operator.physical.PhysicalOlapScanOperator.accept(PhysicalOlapScanOperator.java:142) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalDistribution(AddDecodeNodeForDictStringRule.java:816) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalDistribution(AddDecodeNodeForDictStringRule.java:175) ~[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:303) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.rule.tree.AddDecodeNodeForDictStringRule$DecodeVisitor.visitPhysicalLimit(AddDecodeNodeForDictStringRule.java:175) ~[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:906) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.Optimizer.physicalRuleRewrite(Optimizer.java:417) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.Optimizer.optimizeByCost(Optimizer.java:170) ~[starrocks-fe.jar:?]
at com.starrocks.sql.optimizer.Optimizer.optimize(Optimizer.java:92) ~[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:375) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:317) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:434) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:692) ~[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_352]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_352]
at java.lang.Thread.run(Thread.java:750) [?:1.8.0_352]