apache / apache/iotdb

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

Ouverte
#14,356 1 commentaire 0 réactions 0 personnes assignées Voir sur GitHub
Langage dominant
Java
Étoiles
6.4k
Forks
1.2k
Merge moyen
1 j 23 h
PR mergées (30 j)
115

Description

### 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!

Guide de contribution

Ouvrir le guide de contribution

Piste de recherche

Commencez par reproduire les trois requêtes de fenêtre STDDEV_POP de l’issue sur IoTDB 1.3.3 et comparez les résultats de la fenêtre finale aux attentes indiquées. Suivez le chemin de l’écart type de la fenêtre d’agrégation et vérifiez que les fenêtres à valeur unique renvoient 0.0 sans dépassement numérique ni NaN ; le travail est terminé lorsque les trois requêtes produisent les résultats attendus.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Évaluation

Stack technique
java, sql
Domaine
data, databases
Type d'issue
Bug
Difficulté
4/5
Temps estimé
3-5 jours
Activité
À l'abandon
Clarté
Plutôt claire
Accessibilité débutants
35/100

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.