QueryRecord过滤器返回无效值：预期结果与实际不符问题排查

原始JSON数据 #

[{"id" : 1,"start_time" : 0,"status" : 0,"stop_time" : 0,"update_time" : 1692859450},{"id" : 2,"start_time" : 0,"status" : 1,"stop_time" : 0,"update_time" : 1692859455},{"id" : 3,"start_time" : 0,"status" : 0,"stop_time" : 0,"update_time" : 1687680280}]

筛选需求 #

筛选满足以下任一条件的id：

• status = 1
• stop_time或update_time对应的时间戳晚于当前时间减30天的时间戳

执行的SQL语句（QueryRecord中） #

SELECT CAST('campaign_daily_stat' AS VARCHAR) AS report_type,
CAST('campaign' AS VARCHAR) AS ids_type,
LISTAGG(id, ',') as ids,
COUNT(id) as ids_count 
FROM FLOWFILE 
WHERE status = 1 
OR ((stop_time*1000) >= ${now():toNumber():minus(2592000000)} 
OR (update_time*1000) >= ${now():toNumber():minus(2592000000)})

当前查询结果 #

[ {"report_type" : "campaign_daily_stat","ids_type" : "campaign","ids" : "2","ids_count" : 1} ]

问题 #

预期结果应包含id=1和id=2，但QueryRecord仅返回id=2；在PostgreSQL中改写相同逻辑的查询可得到id=1，请问QueryRecord存在什么问题？遗漏了哪些细节？

问题分析与解决方案 #

1. 时间戳单位转换误差 #

QueryRecord中${now():toNumber()}返回的是毫秒级时间戳，你的update_time是秒级时间戳，虽然乘以1000转换为毫秒的逻辑正确，但如果${now():toNumber():minus(2592000000)}的计算结果恰好大于update_time*1000，就会导致id=1不满足条件。而PostgreSQL中使用TO_TIMESTAMP(update_time) > NOW() - INTERVAL '30 days'的方式，会自动处理时区和毫秒级截断，与QueryRecord的纯数值计算存在细微差异。

解决方法：直接用秒级计算，避免单位转换：

SELECT CAST('campaign_daily_stat' AS VARCHAR) AS report_type,
CAST('campaign' AS VARCHAR) AS ids_type,
LISTAGG(id, ',') as ids,
COUNT(id) as ids_count 
FROM FLOWFILE 
WHERE status = 1 
OR (stop_time >= ${now():toNumber():divide(1000):minus(2592000)} 
OR update_time >= ${now():toNumber():divide(1000):minus(2592000)})

这里将now():toNumber()的毫秒值转为秒（除以1000），再减去30天的秒数（2592000），直接与秒级的stop_time/update_time比较。

2. 数据类型隐式转换问题 #

QueryRecord基于Apache Calcite，对整数与长整数的比较可能存在隐式转换异常。比如stop_time为0时，stop_time*1000被解析为普通整数，而${now():toNumber()}返回的是长整数，两者比较时可能出现预期外的结果。

解决方法：显式将字段转换为长整型：

SELECT CAST('campaign_daily_stat' AS VARCHAR) AS report_type,
CAST('campaign' AS VARCHAR) AS ids_type,
LISTAGG(id, ',') as ids,
COUNT(id) as ids_count 
FROM FLOWFILE 
WHERE status = 1 
OR ((CAST(stop_time AS BIGINT)*1000) >= ${now():toNumber():minus(2592000000)} 
OR (CAST(update_time AS BIGINT)*1000) >= ${now():toNumber():minus(2592000000)})

3. 聚合函数的分组行为差异 #

PostgreSQL在无GROUP BY时会自动将所有符合条件的行聚合为一组，但Apache Calcite（QueryRecord的SQL引擎）可能要求显式指定GROUP BY，否则仅返回部分匹配行。

解决方法：添加显式GROUP BY子句：

SELECT CAST('campaign_daily_stat' AS VARCHAR) AS report_type,
CAST('campaign' AS VARCHAR) AS ids_type,
LISTAGG(id, ',') as ids,
COUNT(id) as ids_count 
FROM FLOWFILE 
WHERE status = 1 
OR ((stop_time*1000) >= ${now():toNumber():minus(2592000000)} 
OR (update_time*1000) >= ${now():toNumber():minus(2592000000)})
GROUP BY report_type, ids_type

内容的提问来源于stack exchange，提问作者fujidaon