StarRocks4.0.1版本 UDF嵌套使用出现问题

💬 StarRocks 用户问答
#1

【详述】StarRocks4.0.1版本UDF嵌套使用出现问题，报UDF Not Support Type的错误。
【是否存算分离】存算一体
【StarRocks版本】4.0.1
【集群规模】例如：3fe+3be
StarRocks 4.0.1版本使用如下udf执行sql时报错：

企业微信截图_17648421433204
企业微信截图_176484214332041285×484 17.3 KB

这里使用了any_match在箭头函数中嵌套使用的blind_area_judge这个udf函数，这个udf函数返回时boolean类型的：
image
image1473×176 7.37 KB

但在单独使用udf时，是正常的，如下图这种sql：
企业微信截图_17648422588017
企业微信截图_17648422588017741×165 5.4 KB

将StarRocks重新降至3.5.9后是可以正常使用的，嵌套也正常返回数据。
是4.0.1版本做了什么限制吗？还是有BUG？

#2

应该是bug，可以整理一个复现case出来吗，例如

create table xx;
insert xx;
select xx;

然后上传相关的文件

#3

【建表语句】
CREATE TABLE IF NOT EXISTS ads.ads_alarm_event_multiple_vessel
(
alarmCode int(11) NOT NULL COMMENT “报警代码”,
alarmStartDateTime datetime NOT NULL COMMENT “报警事件初始发生开始时间”,
partialKey varchar(1024) NOT NULL COMMENT “辅助更新key”,
alarmId bigint(20) NULL COMMENT “报警ID”,
areaId bigint(20) NULL COMMENT “区域ID”,
alarmStartTime bigint(20) NULL COMMENT “报警事件初始发生开始时间”,
alarmTime bigint(20) NULL COMMENT “报警事件处理时间”,
alarmDuration bigint(20) NULL COMMENT “”,
alarmLatestTime bigint(20) NULL COMMENT “”,
closeTime int(11) NULL COMMENT “关闭时长(分钟)”,
openTime int(11) NULL COMMENT “开启时长(分钟)”,
highSpeedLevel int(11) NULL COMMENT “高速报警航速条件等级”,
highSpeedAvg double NULL COMMENT “高速报警平均航速，单位:节”,
highSpeedMin double NULL COMMENT “高速报警最小航速，单位:节”,
highSpeedMax double NULL COMMENT “高速报警最大航速，单位:节”,
probability double NULL COMMENT “AIS切换概率”,
normal boolean NULL COMMENT “低速报警”,
retentionTimePeriod bigint(20) NULL COMMENT “滞留报警时间段”,
retentionRange double NULL COMMENT “滞留范围，单位：米”,
lineDirection int(11) NULL COMMENT “过线报警方向”,
greatStayDuration int(11) NULL COMMENT “接驳、靠近滞留时间”,
approachStartTs bigint(20) NULL COMMENT “接驳开始时间”,
approachEndTs bigint(20) NULL COMMENT “接驳结束时间”,
approachMaxDistance double NULL COMMENT “搭靠最大距离（计算值）”,
approachMaxDistanceCal double NULL COMMENT “搭靠最大距离（数据值）”,
approachMinDistanceCal double NULL COMMENT “搭靠最小距离（数据值）”,
disappearPointDistance double NULL COMMENT “消失点与母船距离”,
approachDuration int(11) NULL COMMENT “搭靠时长”,
dcpaCal double NULL COMMENT “平台避碰的最近距离”,
tcpaCal double NULL COMMENT “平台避碰的最近时间”,
pointId varchar(32) NULL COMMENT “平台避碰的点ID”,
pointLongitude double NULL COMMENT “平台避碰的点经度”,
pointLatitude double NULL COMMENT “平台避碰的点纬度”,
centerLatitude double NULL COMMENT “围网中心点纬度”,
centerLongitude double NULL COMMENT “围网中心点经度”,
seineOneFlag int(11) NULL COMMENT “围网类型”,
avgMinute double NULL COMMENT “围网平均时间”,
avgRadius double NULL COMMENT “围网平均半径”,
avgSpeed double NULL COMMENT “围网平均速度”,
beginLastDT varchar(32) NULL COMMENT “围网绕圈开始时间”,
endLastDT varchar(32) NULL COMMENT “围网绕圈结束时间”,
monotonousRate double NULL COMMENT “围网航向单调性”,
minRadius double NULL COMMENT “围网最小半径”,
wideHeightRatio double NULL COMMENT “围网长宽比”,
distance double NULL COMMENT “围网起点终点距离”,
maxMoveRange double NULL COMMENT “围网最大活动范围”,
circleRate double NULL COMMENT “围网绕圈率”,
geoHash varchar(32) NULL COMMENT “围网中心点geoHash值”,
changeInfos json NULL COMMENT “AIS信息不一致报警信息”,
target json NULL COMMENT “单船信息”,
targets json NULL COMMENT “多船信息”,
ignored tinyint(4) NOT NULL DEFAULT “0” COMMENT “报警信息是否忽略”,
alarmEventLabel tinyint(4) NULL COMMENT “事件标记，初始0,-1标记删除”,
generalFeature json NULL COMMENT “其他报警字段”
) ENGINE=OLAP
PRIMARY KEY(alarmCode, alarmStartDateTime, partialKey)
PARTITION BY RANGE(alarmStartDateTime)
(START (“2025-10-01”) END (“2025-12-06”) EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(alarmCode, alarmStartDateTime)
PROPERTIES (
“replication_num” = “1”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.time_zone” = “Asia/Shanghai”,
“dynamic_partition.start” = “-180”, – 设置保留多久数据
“dynamic_partition.end” = “7”,
“dynamic_partition.prefix” = “p”
);
【插入数据】
INSERT INTO ads.ads_alarm_event_multiple_vessel (alarmCode, alarmStartDateTime, partialKey, alarmId, areaId, alarmStartTime, alarmTime, alarmDuration, alarmLatestTime, closeTime, openTime, highSpeedLevel, highSpeedAvg, highSpeedMin, highSpeedMax, probability, normal, retentionTimePeriod, retentionRange, lineDirection, greatStayDuration, approachStartTs, approachEndTs, approachMaxDistance, approachMaxDistanceCal, approachMinDistanceCal, disappearPointDistance, approachDuration, dcpaCal, tcpaCal, pointId, pointLongitude, pointLatitude, centerLatitude, centerLongitude, seineOneFlag, avgMinute, avgRadius, avgSpeed, beginLastDT, endLastDT, monotonousRate, minRadius, wideHeightRatio, distance, maxMoveRange, circleRate, geoHash, changeInfos, target, targets, ignored, alarmEventLabel, generalFeature) VALUES (70, ‘2025-11-28 11:29:38’, ‘[1994174988923461632]2’, NULL, NULL, 1764300578000, 1764363599697, 3431, 1764304009000, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, ‘[{“course”: 176.6, “displacement”: 38403, “displayId”: 412350001, “duration”: 20779000, “fixedState”: 2, “flags”: 0, “fusionTargets”: [{“provider”: “GLOBAL”, “stationId”: 123, “stationType”: “AIS”, “targetId”: 1994174988923461632, “updateTime”: 1764304009000}], “globalAisType”: 4, “heading”: 176, “id”: “1994174988923461632”, “lastDT”: “2025-11-28 12:26:49”, “lastTm”: 1764304009000, “latitude”: 34.307975, “len”: 351, “longitude”: 120.310355, “mMmsi”: 412350001, “maxLen”: 351, “mmsi”: 412350001, “nationality”: “China”, “originalId”: “1994174988923461632”, “period”: 0, “processTime”: “2025-11-28 12:26:52”, “province”: “Global”, “rangeMaxDistance”: 2.966854138193875e+4, “receiveDT”: “2025-11-28 12:26:49”, “receiveTime”: 1764304009000, “registry”: 1, “sClass”: “AIS_B”, “shipType”: 30, “shortLived”: false, “sources”: [{“ids”: [“123”], “provider”: “GLOBAL”, “type”: “AIS”}], “speed”: 8.2, “state”: 1, “staticState”: 1, “status”: “UNKNOW”, “vesselName”: “SUBINYU19199”, “wid”: 4}]’, 1, 0, ‘{“alarmEventLabel”: 0, “moveToNear”: false, “shamType”: 2}’);

【创建udf语句】
CREATE GLOBAL FUNCTION udf_test(double,double,string,boolean)
RETURNS boolean
PROPERTIES (
“symbol” = “starrocks.udf.TestUDF”,
“type” = “StarrocksJar”,
“file” = “http://10.100.0.221/udf/udf-1.0.jar”,
“isolation” = “shared”
);

【执行SQL 1】
select udf_test(1.0,1.0,‘test’,true);

image
image998×333 9.08 KB

正常返回结果
【执行SQL 2 】
SELECT
*
FROM
ads.ads_alarm_event_multiple_vessel
WHERE
alarmCode = 70
AND alarmStartDateTime BETWEEN ‘2025-11-27 16:25:26’
AND ‘2025-12-04 16:25:26’
AND any_match(
x-> !udf_test(get_json_double(x,’$.longitude’),get_json_double(x,’$.longitude’),‘70’,false),
CAST(targets AS ARRAY)
)
limit 10;
image
image1816×949 46 KB

报错

【附件-udf jar包】
udf-1.0.jar (645.3 KB)

#4

我复现下看看

1赞
#5

set cbo_json_v2_rewrite=false 可以临时绕过

#6

试了一下，是可以的，感谢！

京ICP备2022026421号-1