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

[LingweiKuang]

Search before asking

• I searched in the 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!

[LingweiKuang]