apache / apache/iotdb

[Bug] There is a calculation logic error in the aggregate window query for overall standard deviation.

Đang mở
#14,356 1 bình luận 0 reaction 0 người được giao Xem trên GitHub
Ngôn ngữ chính
Java
Star
6.4k
Fork
1.2k
Merge trung bình
1 ngày 23 giờ
Pull request đã merge (30 ngày)
115

Mô tả

### Search before asking

- [X] I searched in the [issues](https://github.com/apache/iotdb/issues) and found nothing similar.

### Version

version 1.3.3 (Build: ad95a7e)

### Describe the bug and provide the minimal reproduce step

```
DROP DATABASE root.db0
CREATE TIMESERIES root.db0.t1.c0 INT32;

INSERT INTO root.db0.t1(timestamp, c0) VALUES (1641024000000, 72), (1641024005000, 112), (1641024010000, 152), (1641024015000, 192), (1641024020000, 232), (1641024025000, 272), (1641024030000, 312), (1641024035000, 352), (1641024040000, 392), (1641024045000, 432), (1641024050000, 472), (1641024055000, 512), (1641024060000, 552), (1641024065000, 592), (1641024070000, 632), (1641024075000, 672), (1641024080000, 712), (1641024085000, 752), (1641024090000, 792), (1641024095000, 832), (1641024100000, 872), (1641024105000, 912), (1641024110000, 952), (1641024115000, 992), (1641024120000, 1032), (1641024125000, 1072), (1641024130000, 1112), (1641024135000, 1152), (1641024140000, null), (1641024145000, 1232);
INSERT INTO root.db0.t1(timestamp, c0) VALUES (1641024150000, 1272), (1641024155000, 1312), (1641024160000, 1352), (1641024165000, 1392), (1641024170000, 1432), (1641024175000, 1472), (1641024180000, 1512), (1641024185000, 1552), (1641024190000, 1592), (1641024195000, 1632), (1641024200000, 1672), (1641024205000, 1712), (1641024210000, null), (1641024215000, 1792), (1641024220000, 1832), (1641024225000, 1872), (1641024230000, 1912), (1641024235000, 1952), (1641024240000, 1992), (1641024245000, 2032), (1641024250000, 2072), (1641024255000, 2112), (1641024260000, 2152), (1641024265000, 2192), (1641024270000, 2232), (1641024275000, 2272), (1641024280000, 2312), (1641024285000, 2352), (1641024290000, 2392), (1641024295000, 2432);
INSERT INTO root.db0.t1(timestamp, c0) VALUES (1641024300000, 2472), (1641024305000, 2512), (1641024310000, 2552), (1641024315000, 2592), (1641024320000, 2632), (1641024325000, 2672), (1641024330000, 2712), (1641024335000, 2752), (1641024340000, 2792), (1641024345000, null), (1641024350000, 2872), (1641024355000, 2912), (1641024360000, 2952), (1641024365000, 2992), (1641024370000, 3032), (1641024375000, 3072), (1641024380000, 3112), (1641024385000, 3152), (1641024390000, 3192), (1641024395000, 3232), (1641024400000, 3272), (1641024405000, 3312), (1641024410000, 3352), (1641024415000, 3392), (1641024420000, 3432), (1641024425000, 3472), (1641024430000, 3512), (1641024435000, 3552), (1641024440000, 3592), (1641024445000, 3632);
INSERT INTO root.db0.t1(timestamp, c0) VALUES (1641024450000, 3672), (1641024455000, null), (1641024460000, 3752), (1641024465000, null), (1641024470000, 3832), (1641024475000, 3872), (1641024480000, 3912), (1641024485000, 3952), (1641024490000, 3992), (1641024495000, 4032), (1641024500000, 4072), (1641024505000, 4112), (1641024510000, 4152), (1641024515000, 4192), (1641024520000, 4232), (1641024525000, 4272), (1641024530000, 4312), (1641024535000, 4352), (1641024540000, 4392), (1641024545000, 4432), (1641024550000, 4472), (1641024555000, 4512), (1641024560000, 4552), (1641024565000, 4592), (1641024570000, 4632), (1641024575000, 4672), (1641024580000, 4712), (1641024585000, 4752), (1641024590000, 4792), (1641024595000, 4832);
INSERT INTO root.db0.t1(timestamp, c0) VALUES (1641024600000, 4872), (1641024605000, 4912), (1641024610000, 4952), (1641024615000, 4992), (1641024620000, 5032), (1641024625000, 5072), (1641024630000, 5112), (1641024635000, 5152), (1641024640000, 5192), (1641024645000, 5232), (1641024650000, 5272), (1641024655000, 5312), (1641024660000, 5352), (1641024665000, 5392), (1641024670000, 5432), (1641024675000, 5472), (1641024680000, 5512), (1641024685000, 5552), (1641024690000, 5592), (1641024695000, 5632), (1641024700000, 5672), (1641024705000, 5712), (1641024710000, 5752), (1641024715000, 5792), (1641024720000, 5832), (1641024725000, 5872), (1641024730000, 5912), (1641024735000, 5952), (1641024740000, 5992), (1641024745000, 6032);
INSERT INTO root.db0.t1(timestamp, c0) VALUES (1641024750000, 6072), (1641024755000, 6112), (1641024760000, 6152), (1641024765000, 6192), (1641024770000, 6232), (1641024775000, 6272), (1641024780000, 6312), (1641024785000, 6352), (1641024790000, 6392), (1641024795000, 6432), (1641024800000, 6472), (1641024805000, 6512), (1641024810000, 6552), (1641024815000, 6592), (1641024820000, 6632), (1641024825000, 6672), (1641024830000, 6712), (1641024835000, 6752), (1641024840000, 6792), (1641024845000, 6832), (1641024850000, 6872), (1641024855000, 6912), (1641024860000, 6952), (1641024865000, 6992), (1641024870000, 7032), (1641024875000, 7072), (1641024880000, 7112), (1641024885000, 7152), (1641024890000, 7192), (1641024895000, 7232);
INSERT INTO root.db0.t1(timestamp, c0) VALUES (1641024900000, 7272), (1641024905000, 7312), (1641024910000, 7352), (1641024915000, 7392), (1641024920000, null), (1641024925000, 7472), (1641024930000, 7512), (1641024935000, 7552), (1641024940000, 7592), (1641024945000, 7632), (1641024950000, 7672), (1641024955000, 7712), (1641024960000, 7752), (1641024965000, 7792), (1641024970000, 7832), (1641024975000, 7872), (1641024980000, 7912), (1641024985000, 7952), (1641024990000, 7992), (1641024995000, 8032), (1641025000000, 8072), (1641025005000, 8112), (1641025010000, 8152), (1641025015000, 8192), (1641025020000, 8232), (1641025025000, 8272), (1641025030000, 8312), (1641025035000, 8352), (1641025040000, 8392), (1641025045000, 8432);
INSERT INTO root.db0.t1(timestamp, c0) VALUES (1641025050000, 8472), (1641025055000, 8512), (1641025060000, 8552), (1641025065000, 8592), (1641025070000, 8632), (1641025075000, 8672), (1641025080000, 8712), (1641025085000, 8752), (1641025090000, 8792), (1641025095000, 8832), (1641025100000, 8872), (1641025105000, 8912), (1641025110000, 8952), (1641025115000, 8992), (1641025120000, 9032), (1641025125000, 9072), (1641025130000, 9112), (1641025135000, 9152), (1641025140000, null), (1641025145000, 9232), (1641025150000, 9272), (1641025155000, 9312), (1641025160000, 9352), (1641025165000, 9392), (1641025170000, 9432), (1641025175000, 9472), (1641025180000, 9512), (1641025185000, 9552), (1641025190000, 9592), (1641025195000, 9632);
INSERT INTO root.db0.t1(timestamp, c0) VALUES (1641025200000, 9672), (1641025205000, 9712), (1641025210000, 9752), (1641025215000, 9792), (1641025220000, 9832), (1641025225000, 9872), (1641025230000, 9912), (1641025235000, 9952), (1641025240000, 9992), (1641025245000, 10032), (1641025250000, 10072), (1641025255000, 10112), (1641025260000, 10152), (1641025265000, 10192), (1641025270000, 10232), (1641025275000, 10272), (1641025280000, 10312), (1641025285000, 10352), (1641025290000, 10392), (1641025295000, 10432), (1641025300000, 10472), (1641025305000, 10512), (1641025310000, 10552), (1641025315000, 10592), (1641025320000, 10632), (1641025325000, 10672), (1641025330000, 10712), (1641025335000, 10752), (1641025340000, 10792), (1641025345000, 10832);
INSERT INTO root.db0.t1(timestamp, c0) VALUES (1641025350000, 10872), (1641025355000, 10912), (1641025360000, 10952), (1641025365000, 10992), (1641025370000, 11032), (1641025375000, 11072), (1641025380000, 11112), (1641025385000, 11152), (1641025390000, 11192), (1641025395000, 11232), (1641025400000, 11272), (1641025405000, 11312), (1641025410000, 11352), (1641025415000, 11392), (1641025420000, 11432), (1641025425000, 11472), (1641025430000, 11512), (1641025435000, 11552), (1641025440000, 11592), (1641025445000, 11632), (1641025450000, 11672), (1641025455000, 11712), (1641025460000, 11752), (1641025465000, 11792), (1641025470000, 11832), (1641025475000, 11872), (1641025480000, 11912), (1641025485000, 11952), (1641025490000, 11992), (1641025495000, 12032);
INSERT INTO root.db0.t1(timestamp, c0) VALUES (1641025500000, 12072), (1641025505000, 12112), (1641025510000, 12152), (1641025515000, 12192), (1641025520000, 12232), (1641025525000, 12272), (1641025530000, 12312), (1641025535000, 12352), (1641025540000, 12392), (1641025545000, 12432), (1641025550000, 12472), (1641025555000, 12512), (1641025560000, 12552), (1641025565000, 12592), (1641025570000, 12632), (1641025575000, 12672), (1641025580000, 12712), (1641025585000, 12752), (1641025590000, 12792), (1641025595000, 12832), (1641025600000, 12872), (1641025605000, 12912), (1641025610000, 12952), (1641025615000, 12992), (1641025620000, 13032), (1641025625000, 13072), (1641025630000, 13112), (1641025635000, 13152), (1641025640000, 13192), (1641025645000, 13232);
INSERT INTO root.db0.t1(timestamp, c0) VALUES (1641025650000, null), (1641025655000, 13312), (1641025660000, 13352), (1641025665000, 13392), (1641025670000, null), (1641025675000, 13472), (1641025680000, 13512), (1641025685000, 13552), (1641025690000, 13592), (1641025695000, 13632), (1641025700000, 13672), (1641025705000, 13712), (1641025710000, 13752), (1641025715000, 13792), (1641025720000, 13832), (1641025725000, 13872), (1641025730000, 13912), (1641025735000, 13952);

# query 1
SELECT STDDEV_POP(c0) FROM root.db0.t1 GROUP BY ([1641023357064, 1641025073925),2545s,854s)

# query 2
SELECT STDDEV_POP(c0) FROM root.db0.t1 GROUP BY ([1641023357064, 1641025073925),2545s,1708s)

# query 3
SELECT STDDEV_POP(c0) FROM root.db0.t1 GROUP BY([1641025065064, 1641025073925),2545s)
```

### What did you expect to see?

The expected result set for Query 1 is: 2489.867388014679 1987.3425863588686 **0.0**

The expected result set for Query 2 is: 2489.867388014679 0.0

The expected result set for Query 3 is: 0.0

### What did you see instead?

The actual result set returned by Query 1 is: 2489.867388014679 1987.3425863588686 **NaN**

The actual result set returned by Query 2 is: 2489.867388014679 **3.1714797501871533E-4**

The actual result set returned by Query 3 is: 0.0

### Anything else?

Dear IoTDB team, In the three queries above, we perform **standard deviation window aggregation** on the `root.db0.t1.c1` time series.

- Query 1 has an aggregation start time of `1641023357064` and an end time of `1641025073925`, with each aggregation window size being `2545s` and a window sliding distance of `854s`.
- Query 2 uses the same aggregation time range as Query 1 but doubles the sliding distance of Query 1.
- Query 3 changes the start time to `1641025065064` (`1641025065064 = 1641023357064 + 854000 + 854000`).

By analyzing the actual result sets returned by the three queries, we found discrepancies in the aggregation values of the last window. Additionally, Query 1 triggered a **numeric overflow** error.

### Are you willing to submit a PR?

- [ ] I'm willing to submit a PR!

Hướng dẫn đóng góp

Mở hướng dẫn đóng góp

Hướng nghiên cứu

Bắt đầu bằng cách tái hiện ba truy vấn cửa sổ STDDEV_POP từ issue trên IoTDB 1.3.3 và so sánh kết quả của cửa sổ cuối cùng với các kỳ vọng đã nêu. Theo dõi đường đi của độ lệch chuẩn cửa sổ tổng hợp và xác minh rằng các cửa sổ có một giá trị trả về 0.0 mà không bị tràn số hoặc NaN; hoàn thành có nghĩa là cả ba truy vấn đều cho ra các kết quả mong đợi.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Đánh giá

Công nghệ
java, sql
Lĩnh vực
data, databases
Loại issue
Lỗi
Độ khó
4/5
Thời gian dự kiến
3-5 ngày
Mức độ hoạt động
Đình trệ
Độ rõ ràng
Khá rõ ràng
Mức phù hợp với người mới
35/100

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.