CallisHub opened a new issue, #18988:
URL: https://github.com/apache/iotdb/issues/18988

   ### Search before asking
   
   - [x] I searched in the [issues](https://github.com/apache/iotdb/issues) and 
found nothing similar.
   
   
   ### Version
   
   IoTDB 版本:[2.0.11]
   部署方式:单机 
   操作入口:CLI / JDBC(表模型)
   
   ### Describe the bug and provide the minimal reproduce step
   
   问题描述
   使用 date_bin_gapfill 配合 FILL METHOD PREVIOUS 时,查询范围末尾的时间窗口生成不稳定,存在两个异常:
   
   是否加 device_id 过滤会改变结果:不加过滤时某设备有末尾窗口,加上该设备过滤后末尾窗口消失。
   
   上界运算符(< / <=)影响末尾窗口:< 2025-01-01 时缺 2024-12 窗口;<= 2025-01-01 时不仅补出 
2024-12,还多出 2025-01 窗口;<= 2024-12-31T23:59:59.999 时又缺失 2024-12 窗口。
   
   复现步骤
   1. 建表与测试数据
    `CREATE TABLE "table1" ("time" TIMESTAMP TIME,
                          "region" STRING TAG,
                          "plant_id" STRING TAG,
                          "device_id" STRING TAG,
                          "model_id" STRING ATTRIBUTE,
                          "maintenance" STRING ATTRIBUTE,
                          "temperature" FLOAT FIELD,
                          "humidity" FLOAT FIELD,
                          "status" BOOLEAN FIELD,
                          "arrival_time" TIMESTAMP FIELD)
   WITH (ttl = 'INF');`
   
   -- 插入测试数据
   `INSERT INTO table1(time, region, plant_id, device_id, model_id, 
maintenance, temperature, humidity, status, arrival_time) VALUES 
   (2024-11-30T09:30:00.000+08:00, '上海', '3002', '101', 'F', '360', 35.2, 90, 
true, NULL),
   (2024-11-30T14:30:00.000+08:00, '上海', '3002', '101', 'F', '360', 34.8, 90, 
true, 2024-11-30T14:30:17.000+08:00),
   (2024-11-29T10:00:00.000+08:00, '上海', '3001', '101', 'D', '360', NULL, 85, 
NULL, 2024-11-29T10:00:13.000+08:00),
   (2024-11-27T16:38:00.000+08:00, '北京', '1001', '101', 'B', '180', 35.1, NULL, 
true, 2024-11-27T16:37:01.000+08:00),
   (2024-11-27T16:39:00.000+08:00, '北京', '1001', '101', 'B', '180', 35.3, 85, 
NULL, NULL),
   (2024-11-27T16:40:00.000+08:00, '北京', '1001', '101', 'B', '180', NULL, 85, 
NULL, 2024-11-27T16:37:03.000+08:00),
   (2024-11-27T16:41:00.000+08:00, '北京', '1001', '101', 'B', '180', NULL, 85, 
NULL, 2024-11-27T16:37:04.000+08:00),
   (2024-11-27T16:42:00.000+08:00, '北京', '1001', '101', 'B', '180', 35.2, NULL, 
false, NULL),
   (2024-11-27T16:43:00.000+08:00, '北京', '1001', '101', 'B', '180', NULL, NULL, 
false, NULL),
   (2024-11-27T16:44:00.000+08:00, '北京', '1001', '101', 'B', '180', NULL, NULL, 
false, 2024-11-27T16:37:08.000+08:00),
   (2024-11-29T11:00:00.000+08:00, '上海', '3002', '100', 'E', '180', 45.1, NULL, 
true, NULL),
   (2024-11-29T18:30:00.000+08:00, '上海', '3002', '100', 'E', '180', 35.4, 90, 
true, 2024-11-29T18:30:15.000+08:00),
   (2024-11-28T08:00:00.000+08:00, '上海', '3001', '100', 'C', '90', NULL, 85, 
NULL, 2024-11-28T08:00:09.000+08:00),
   (2024-11-28T09:00:00.000+08:00, '上海', '3001', '100', 'C', '90', 40.9, NULL, 
true, NULL),
   (2024-11-28T10:00:00.000+08:00, '上海', '3001', '100', 'C', '90', 35.2, 85, 
NULL, 2024-11-28T10:00:11.000+08:00),
   (2024-11-28T11:00:00.000+08:00, '上海', '3001', '100', 'C', '90', 45.1, 88, 
true, 2024-11-28T11:00:12.000+08:00),
   (2024-11-26T13:37:00.000+08:00, '北京', '1001', '100', 'A', '180', 35.1, 90, 
true, 2024-11-26T13:37:34.000+08:00),
   (2024-11-26T13:38:00.000+08:00, '北京', '1001', '100', 'A', '180', 35.1, 90, 
true, 2024-11-26T13:38:25.000+08:00);`
   
   2. 查询用例与现象
   
   用例 1:不加 device_id 过滤
   
   `SELECT
       date_bin_gapfill(1mo, time, 2001-01-01T00:00:00+08:00) AS window_time,
       device_id,
       AVG(temperature) AS avg_temp
   FROM table1
   WHERE time >= 2024-01-01T00:00:00+08:00
     AND time < 2025-01-01T00:00:00+08:00
   GROUP BY 1, device_id 
   fill method PREVIOUS FILL_GROUP 2;`
   现象: 设备 100 有 2024-12-01 窗口,设备 101 没有。
   
   用例 2:加 device_id = '100',上界用 <
   -- SELECT 与 GROUP BY 同上
   WHERE time >= 2024-01-01T00:00:00+08:00
     AND time < 2025-01-01T00:00:00+08:00
     AND device_id = '100'
   现象: 2024-12-01 窗口消失,结果只到 2024-11-01。而不加过滤时该设备明明有这个窗口。
   
   用例 3:加 device_id = '101',上界改为 <= 2025-01-01
   -- SELECT 与 GROUP BY 同上
   WHERE time >= 2024-01-01T00:00:00+08:00
     AND time <= 2025-01-01T00:00:00+08:00
     AND device_id = '101'
   现象: 结果不仅补出了 2024-12-01,还多出 2025-01-01 窗口。
   
   用例 4:加 device_id = '101',上界用 <= 2024-12-31T23:59:59.999
   -- SELECT 与 GROUP BY 同上
   WHERE time >= 2024-01-01T00:00:00+08:00
     AND time <= 2024-12-31T23:59:59.999+08:00
     AND device_id = '101'
   现象: 2024-12-01 窗口再次消失。
   
   
   
   ### What did you expect to see?
   
   对于固定查询范围 [2024-01-01, 2025-01-01)、步长 1mo,date_bin_gapfill 应稳定生成 2024-01 到 
2024-12 共 12 个窗口,不因以下因素变化:
   
   WHERE 中是否加了具体 device_id 过滤;
   
   上界写作 < 2025-01-01 还是 <= 2024-12-31T23:59:59.999。
   
   末尾窗口不应整行丢失,也不应在边界处凭空多出下一窗口。
   
   补充说明
   FILL_GROUP 2 能正确按设备隔离填充,但窗口生成本身(gapfill)似乎在上界处处理不一致。
   期望得到的结果
   2024-01-01 00:00:00.000      101     
   2024-02-01 00:00:00.000      101     
   2024-03-01 00:00:00.000      101     
   2024-04-01 00:00:00.000      101     
   2024-05-01 00:00:00.000      101     
   2024-06-01 00:00:00.000      101     
   2024-07-01 00:00:00.000      101     
   2024-08-01 00:00:00.000      101     
   2024-09-01 00:00:00.000      101     
   2024-10-01 00:00:00.000      101     
   2024-11-01 00:00:00.000      101     35.119999694824216
   2024-12-01 00:00:00.000      101     35.119999694824216
   2024-01-01 00:00:00.000      100     
   2024-02-01 00:00:00.000      100     
   2024-03-01 00:00:00.000      100     
   2024-04-01 00:00:00.000      100     
   2024-05-01 00:00:00.000      100     
   2024-06-01 00:00:00.000      100     
   2024-07-01 00:00:00.000      100     
   2024-08-01 00:00:00.000      100     
   2024-09-01 00:00:00.000      100     
   2024-10-01 00:00:00.000      100     
   2024-11-01 00:00:00.000      100     38.84285681588309
   2024-12-01 00:00:00.000      100     38.84285681588309
   
   ### What did you see instead?
   
   见:复现步骤
   
   ### Anything else?
   
   _No response_
   
   ### Are you willing to submit a PR?
   
   - [ ] I'm willing to submit a PR!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to