Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Broken time series with difference/derivative function #8175

Closed
maxsivanov opened this issue Mar 21, 2017 · 3 comments
Closed

Broken time series with difference/derivative function #8175

maxsivanov opened this issue Mar 21, 2017 · 3 comments
Assignees
@jsternberg
Labels
area/queries kind/bug review

Comments

@maxsivanov
Copy link

maxsivanov commented Mar 21, 2017 •
edited
Loading

Bug report

System info:

Steps to reproduce:

  1. Fill database with hour by hour data
PARAMETER,game=all value=45459 1489219200000000000
PARAMETER,game=all value=45854 1489222800000000000
PARAMETER,game=all value=46280 1489226400000000000
PARAMETER,game=all value=46754 1489230000000000000
PARAMETER,game=all value=47217 1489233600000000000
PARAMETER,game=all value=47732 1489237200000000000
PARAMETER,game=all value=48329 1489240800000000000
PARAMETER,game=all value=48963 1489244400000000000
PARAMETER,game=all value=49614 1489248000000000000
PARAMETER,game=all value=50231 1489251600000000000
PARAMETER,game=all value=50835 1489255200000000000
PARAMETER,game=all value=51564 1489258800000000000
PARAMETER,game=all value=52190 1489262400000000000
PARAMETER,game=all value=52816 1489266000000000000
PARAMETER,game=all value=53454 1489269600000000000
PARAMETER,game=all value=54034 1489273200000000000
PARAMETER,game=all value=54505 1489276800000000000
PARAMETER,game=all value=54820 1489280400000000000
PARAMETER,game=all value=55033 1489284000000000000
PARAMETER,game=all value=55178 1489287600000000000
PARAMETER,game=all value=55328 1489291200000000000
PARAMETER,game=all value=55447 1489294800000000000
PARAMETER,game=all value=55609 1489298400000000000
PARAMETER,game=all value=55753 1489302000000000000
PARAMETER,game=all value=55946 1489305600000000000
PARAMETER,game=all value=56268 1489309200000000000
PARAMETER,game=all value=56669 1489312800000000000
PARAMETER,game=all value=57111 1489316400000000000
PARAMETER,game=all value=57608 1489320000000000000
PARAMETER,game=all value=58066 1489323600000000000
PARAMETER,game=all value=58512 1489327200000000000
PARAMETER,game=all value=58970 1489330800000000000
PARAMETER,game=all value=59429 1489334400000000000
PARAMETER,game=all value=59951 1489338000000000000
PARAMETER,game=all value=60476 1489341600000000000
PARAMETER,game=all value=61150 1489345200000000000
PARAMETER,game=all value=61799 1489348800000000000
PARAMETER,game=all value=62441 1489352400000000000
PARAMETER,game=all value=62970 1489356000000000000
PARAMETER,game=all value=63495 1489359600000000000
PARAMETER,game=all value=63917 1489363200000000000
PARAMETER,game=all value=64216 1489366800000000000
PARAMETER,game=all value=64398 1489370400000000000
PARAMETER,game=all value=64558 1489374000000000000
PARAMETER,game=all value=64698 1489377600000000000
PARAMETER,game=all value=64828 1489381200000000000
PARAMETER,game=all value=65003 1489384800000000000
PARAMETER,game=all value=65230 1489388400000000000
PARAMETER,game=all value=65485 1489392000000000000
PARAMETER,game=all value=65804 1489395600000000000
PARAMETER,game=all value=66148 1489399200000000000
PARAMETER,game=all value=66580 1489402800000000000
PARAMETER,game=all value=67060 1489406400000000000
PARAMETER,game=all value=67615 1489410000000000000
PARAMETER,game=all value=68211 1489413600000000000
PARAMETER,game=all value=68786 1489417200000000000
PARAMETER,game=all value=69352 1489420800000000000
PARAMETER,game=all value=69970 1489424400000000000
PARAMETER,game=all value=70586 1489428000000000000
PARAMETER,game=all value=71257 1489431600000000000
PARAMETER,game=all value=71964 1489435200000000000
PARAMETER,game=all value=72645 1489438800000000000
PARAMETER,game=all value=73448 1489442400000000000
PARAMETER,game=all value=74256 1489446000000000000
PARAMETER,game=all value=74736 1489449600000000000
PARAMETER,game=all value=75073 1489453200000000000
PARAMETER,game=all value=75365 1489456800000000000
PARAMETER,game=all value=75507 1489460400000000000
PARAMETER,game=all value=75657 1489464000000000000
PARAMETER,game=all value=75812 1489467600000000000
PARAMETER,game=all value=75993 1489471200000000000
PARAMETER,game=all value=76209 1489474800000000000
PARAMETER,game=all value=76582 1489478400000000000
PARAMETER,game=all value=76968 1489482000000000000
PARAMETER,game=all value=77412 1489485600000000000
PARAMETER,game=all value=77958 1489489200000000000
PARAMETER,game=all value=78540 1489492800000000000
PARAMETER,game=all value=79104 1489496400000000000
PARAMETER,game=all value=79628 1489500000000000000
PARAMETER,game=all value=80249 1489503600000000000
PARAMETER,game=all value=80884 1489507200000000000
PARAMETER,game=all value=81569 1489510800000000000
PARAMETER,game=all value=82281 1489514400000000000
PARAMETER,game=all value=83008 1489518000000000000
PARAMETER,game=all value=83761 1489521600000000000
PARAMETER,game=all value=84458 1489525200000000000
PARAMETER,game=all value=85197 1489528800000000000
PARAMETER,game=all value=86045 1489532400000000000
PARAMETER,game=all value=86549 1489536000000000000
PARAMETER,game=all value=86873 1489539600000000000
PARAMETER,game=all value=87128 1489543200000000000
PARAMETER,game=all value=87344 1489546800000000000
PARAMETER,game=all value=87541 1489550400000000000
PARAMETER,game=all value=87720 1489554000000000000
PARAMETER,game=all value=87937 1489557600000000000
PARAMETER,game=all value=88175 1489561200000000000
PARAMETER,game=all value=88564 1489564800000000000
PARAMETER,game=all value=88997 1489568400000000000
PARAMETER,game=all value=89491 1489572000000000000
PARAMETER,game=all value=89967 1489575600000000000
PARAMETER,game=all value=90484 1489579200000000000
PARAMETER,game=all value=91040 1489582800000000000
PARAMETER,game=all value=91681 1489586400000000000
PARAMETER,game=all value=92304 1489590000000000000
PARAMETER,game=all value=92995 1489593600000000000
PARAMETER,game=all value=93761 1489597200000000000
PARAMETER,game=all value=94507 1489600800000000000
PARAMETER,game=all value=95315 1489604400000000000
PARAMETER,game=all value=96111 1489608000000000000
PARAMETER,game=all value=96847 1489611600000000000
PARAMETER,game=all value=97643 1489615200000000000
PARAMETER,game=all value=98414 1489618800000000000
PARAMETER,game=all value=98846 1489622400000000000
PARAMETER,game=all value=99408 1489629600000000000
PARAMETER,game=all value=99618 1489633200000000000
PARAMETER,game=all value=99776 1489636800000000000
PARAMETER,game=all value=99941 1489640400000000000
PARAMETER,game=all value=100114 1489644000000000000
PARAMETER,game=all value=100340 1489647600000000000
PARAMETER,game=all value=100588 1489651200000000000
PARAMETER,game=all value=100962 1489654800000000000
PARAMETER,game=all value=101457 1489658400000000000
PARAMETER,game=all value=101984 1489662000000000000
PARAMETER,game=all value=102509 1489665600000000000
PARAMETER,game=all value=103070 1489669200000000000
PARAMETER,game=all value=103697 1489672800000000000
PARAMETER,game=all value=104320 1489676400000000000
PARAMETER,game=all value=104933 1489680000000000000
PARAMETER,game=all value=105584 1489683600000000000
PARAMETER,game=all value=106290 1489687200000000000
PARAMETER,game=all value=106996 1489690800000000000
PARAMETER,game=all value=107648 1489694400000000000
PARAMETER,game=all value=108344 1489698000000000000
PARAMETER,game=all value=109012 1489701600000000000
PARAMETER,game=all value=109585 1489705200000000000
PARAMETER,game=all value=110088 1489708800000000000
PARAMETER,game=all value=110464 1489712400000000000
PARAMETER,game=all value=110727 1489716000000000000
PARAMETER,game=all value=110943 1489719600000000000
PARAMETER,game=all value=111082 1489723200000000000
PARAMETER,game=all value=111212 1489726800000000000
PARAMETER,game=all value=111447 1489730400000000000
PARAMETER,game=all value=111755 1489734000000000000
PARAMETER,game=all value=112113 1489737600000000000
PARAMETER,game=all value=112486 1489741200000000000
PARAMETER,game=all value=113057 1489744800000000000
PARAMETER,game=all value=113549 1489748400000000000
PARAMETER,game=all value=114018 1489752000000000000
PARAMETER,game=all value=114668 1489755600000000000
PARAMETER,game=all value=115263 1489759200000000000
PARAMETER,game=all value=115905 1489762800000000000
PARAMETER,game=all value=116538 1489766400000000000
PARAMETER,game=all value=117185 1489770000000000000
PARAMETER,game=all value=117839 1489773600000000000
PARAMETER,game=all value=118535 1489777200000000000
PARAMETER,game=all value=119188 1489780800000000000
PARAMETER,game=all value=119967 1489784400000000000
PARAMETER,game=all value=120823 1489788000000000000
PARAMETER,game=all value=121500 1489791600000000000
PARAMETER,game=all value=122014 1489795200000000000
PARAMETER,game=all value=122394 1489798800000000000
PARAMETER,game=all value=122700 1489802400000000000
PARAMETER,game=all value=122912 1489806000000000000
PARAMETER,game=all value=123084 1489809600000000000
PARAMETER,game=all value=123231 1489813200000000000
PARAMETER,game=all value=123409 1489816800000000000
PARAMETER,game=all value=123589 1489820400000000000
PARAMETER,game=all value=123830 1489824000000000000
PARAMETER,game=all value=124147 1489827600000000000
PARAMETER,game=all value=124546 1489831200000000000
PARAMETER,game=all value=124974 1489834800000000000
PARAMETER,game=all value=125489 1489838400000000000
PARAMETER,game=all value=125990 1489842000000000000
PARAMETER,game=all value=126550 1489845600000000000
PARAMETER,game=all value=127183 1489849200000000000
PARAMETER,game=all value=127739 1489852800000000000
PARAMETER,game=all value=128385 1489856400000000000
PARAMETER,game=all value=128975 1489860000000000000
PARAMETER,game=all value=129659 1489863600000000000
PARAMETER,game=all value=130323 1489867200000000000
PARAMETER,game=all value=131053 1489870800000000000
PARAMETER,game=all value=131781 1489874400000000000
PARAMETER,game=all value=132424 1489878000000000000
PARAMETER,game=all value=132927 1489881600000000000
PARAMETER,game=all value=133369 1489885200000000000
PARAMETER,game=all value=133667 1489888800000000000
PARAMETER,game=all value=133866 1489892400000000000
PARAMETER,game=all value=134055 1489896000000000000
PARAMETER,game=all value=134240 1489899600000000000
PARAMETER,game=all value=134445 1489903200000000000
PARAMETER,game=all value=134662 1489906800000000000
PARAMETER,game=all value=134900 1489910400000000000
PARAMETER,game=all value=135227 1489914000000000000
PARAMETER,game=all value=135626 1489917600000000000
PARAMETER,game=all value=136054 1489921200000000000
PARAMETER,game=all value=136473 1489924800000000000
PARAMETER,game=all value=137070 1489928400000000000
PARAMETER,game=all value=137669 1489932000000000000
PARAMETER,game=all value=138233 1489935600000000000
PARAMETER,game=all value=138838 1489939200000000000
PARAMETER,game=all value=139466 1489942800000000000
PARAMETER,game=all value=140058 1489946400000000000
PARAMETER,game=all value=140696 1489950000000000000
PARAMETER,game=all value=141400 1489953600000000000
PARAMETER,game=all value=141985 1489957200000000000
PARAMETER,game=all value=142605 1489960800000000000
PARAMETER,game=all value=143124 1489964400000000000
PARAMETER,game=all value=143537 1489968000000000000
PARAMETER,game=all value=143832 1489971600000000000
PARAMETER,game=all value=144066 1489975200000000000
PARAMETER,game=all value=144248 1489978800000000000
PARAMETER,game=all value=144390 1489982400000000000
PARAMETER,game=all value=144527 1489986000000000000
PARAMETER,game=all value=144743 1489989600000000000
PARAMETER,game=all value=144963 1489993200000000000
PARAMETER,game=all value=145245 1489996800000000000
PARAMETER,game=all value=145563 1490000400000000000
PARAMETER,game=all value=145959 1490004000000000000
PARAMETER,game=all value=146497 1490007600000000000
PARAMETER,game=all value=147061 1490011200000000000
PARAMETER,game=all value=147627 1490014800000000000
PARAMETER,game=all value=148245 1490018400000000000
PARAMETER,game=all value=148912 1490022000000000000
PARAMETER,game=all value=149677 1490025600000000000
PARAMETER,game=all value=150407 1490029200000000000
PARAMETER,game=all value=151140 1490032800000000000
PARAMETER,game=all value=151874 1490036400000000000
PARAMETER,game=all value=152619 1490040000000000000
PARAMETER,game=all value=153290 1490043600000000000
PARAMETER,game=all value=154048 1490047200000000000
PARAMETER,game=all value=154597 1490050800000000000
PARAMETER,game=all value=155080 1490054400000000000
PARAMETER,game=all value=155380 1490058000000000000
PARAMETER,game=all value=155583 1490061600000000000
PARAMETER,game=all value=155734 1490065200000000000
PARAMETER,game=all value=155867 1490068800000000000
PARAMETER,game=all value=155985 1490072400000000000
PARAMETER,game=all value=156137 1490076000000000000
  1. Make select using DIFFERENCE() or NON_NEGATIVE_DERIVATIVE() or DERIVATIVE(), does not matter
SELECT difference("value") FROM "PARAMETER" WHERE "game" =~ /^all$/ AND time > 1488319200000ms and time < 1490993999999ms
name: PARAMETER
time                difference
----                ----------
1489222800000000000 395
1489226400000000000 426
1489230000000000000 474
1489233600000000000 463
1489237200000000000 515
1489240800000000000 597
1489244400000000000 634
1489248000000000000 651
1489251600000000000 617
1489255200000000000 604
1489258800000000000 729
1489262400000000000 626
1489266000000000000 626
1489269600000000000 638
1489273200000000000 580
1489276800000000000 471
1489280400000000000 315
1489284000000000000 213
1489287600000000000 145
1489291200000000000 150
1489294800000000000 119
1489298400000000000 162
1489302000000000000 144
1489305600000000000 193
1489309200000000000 322
1489312800000000000 401
1489316400000000000 442
1489320000000000000 497
1489323600000000000 458
1489327200000000000 446
1489330800000000000 458
1489334400000000000 459
1489338000000000000 522
1489341600000000000 525
1489345200000000000 674
1489348800000000000 649
1489352400000000000 642
1489356000000000000 529
1489359600000000000 525
1489968000000000000 80042 <!---------------------------
1489971600000000000 295
1489975200000000000 234
1489978800000000000 182
1489982400000000000 142
1489986000000000000 137
1489989600000000000 216
1489993200000000000 220
1489996800000000000 282
1490000400000000000 318
1490004000000000000 396
1490007600000000000 538
1490011200000000000 564
1490014800000000000 566
1490018400000000000 618
1490022000000000000 667
1490025600000000000 765
1490029200000000000 730
1490032800000000000 733
1490036400000000000 734
1490040000000000000 745
1490043600000000000 671
1490047200000000000 758
1490050800000000000 549
1490054400000000000 483
1490058000000000000 300
1490061600000000000 203
1490065200000000000 151
1490068800000000000 133
1490072400000000000 118
1490076000000000000 152
1489363200000000000 -92220 <!---------------------------
1489366800000000000 299
1489370400000000000 182
1489374000000000000 160
1489377600000000000 140
1489381200000000000 130
1489384800000000000 175
1489388400000000000 227
1489392000000000000 255
1489395600000000000 319
1489399200000000000 344
1489402800000000000 432
1489406400000000000 480
1489410000000000000 555
1489413600000000000 596
1489417200000000000 575
1489420800000000000 566
1489424400000000000 618
1489428000000000000 616
1489431600000000000 671
1489435200000000000 707
1489438800000000000 681
1489442400000000000 803
1489446000000000000 808
1489449600000000000 480
1489453200000000000 337
1489456800000000000 292
1489460400000000000 142
1489464000000000000 150
1489467600000000000 155
1489471200000000000 181
1489474800000000000 216
1489478400000000000 373
1489482000000000000 386
1489485600000000000 444
1489489200000000000 546
1489492800000000000 582
1489496400000000000 564
1489500000000000000 524
1489503600000000000 621
1489507200000000000 635
1489510800000000000 685
1489514400000000000 712
1489518000000000000 727
1489521600000000000 753
1489525200000000000 697
1489528800000000000 739
1489532400000000000 848
1489536000000000000 504
1489539600000000000 324
1489543200000000000 255
1489546800000000000 216
1489550400000000000 197
1489554000000000000 179
1489557600000000000 217
1489561200000000000 238
1489564800000000000 389
1489568400000000000 433
1489572000000000000 494
1489575600000000000 476
1489579200000000000 517
1489582800000000000 556
1489586400000000000 641
1489590000000000000 623
1489593600000000000 691
1489597200000000000 766
1489600800000000000 746
1489604400000000000 808
1489608000000000000 796
1489611600000000000 736
1489615200000000000 796
1489618800000000000 771
1489622400000000000 432
1489629600000000000 562
1489633200000000000 210
1489636800000000000 158
1489640400000000000 165
1489644000000000000 173
1489647600000000000 226
1489651200000000000 248
1489654800000000000 374
1489658400000000000 495
1489662000000000000 527
1489665600000000000 525
1489669200000000000 561
1489672800000000000 627
1489676400000000000 623
1489680000000000000 613
1489683600000000000 651
1489687200000000000 706
1489690800000000000 706
1489694400000000000 652
1489698000000000000 696
1489701600000000000 668
1489705200000000000 573
1489708800000000000 503
1489712400000000000 376
1489716000000000000 263
1489719600000000000 216
1489723200000000000 139
1489726800000000000 130
1489730400000000000 235
1489734000000000000 308
1489737600000000000 358
1489741200000000000 373
1489744800000000000 571
1489748400000000000 492
1489752000000000000 469
1489755600000000000 650
1489759200000000000 595
1489762800000000000 642
1489766400000000000 633
1489770000000000000 647
1489773600000000000 654
1489777200000000000 696
1489780800000000000 653
1489784400000000000 779
1489788000000000000 856
1489791600000000000 677
1489795200000000000 514
1489798800000000000 380
1489802400000000000 306
1489806000000000000 212
1489809600000000000 172
1489813200000000000 147
1489816800000000000 178
1489820400000000000 180
1489824000000000000 241
1489827600000000000 317
1489831200000000000 399
1489834800000000000 428
1489838400000000000 515
1489842000000000000 501
1489845600000000000 560
1489849200000000000 633
1489852800000000000 556
1489856400000000000 646
1489860000000000000 590
1489863600000000000 684
1489867200000000000 664
1489870800000000000 730
1489874400000000000 728
1489878000000000000 643
1489881600000000000 503
1489885200000000000 442
1489888800000000000 298
1489892400000000000 199
1489896000000000000 189
1489899600000000000 185
1489903200000000000 205
1489906800000000000 217
1489910400000000000 238
1489914000000000000 327
1489917600000000000 399
1489921200000000000 428
1489924800000000000 419
1489928400000000000 597
1489932000000000000 599
1489935600000000000 564
1489939200000000000 605
1489942800000000000 628
1489946400000000000 592
1489950000000000000 638
1489953600000000000 704
1489957200000000000 585
1489960800000000000 620
1489964400000000000 519

Expected behavior:

Time series should be sorted ascending and difference should be done after.

Actual behavior:

Data from 2017-03-20 is between 2017-03-12 and 2017-03-13. Looks like incorrect bucketing.

Additional info:

  • Request without DIFFERENCE like functions will not produce such a glitch.
  • Request do not includes 2017-03-20 works fine. For example:

SELECT difference("value") FROM "PARAMETER" WHERE "game" =~ /^all$/ AND time > 1488319200000ms and time < 1489967999000ms
@jsternberg
Copy link
Contributor

Can you run SHOW SHARDS for me? I would like to know if the time spans that are wrongly ordered are between shard groups. It seems to me that since the span of correct times is 7 days, the chance of that being true is very high.

Thanks.

@jsternberg jsternberg self-assigned this Mar 27, 2017
@jsternberg
Copy link
Contributor

@rbetts this issue, when we have a fix, will likely require a backport. I think it's directly related to some of the changes made to accommodate subqueries.

@jsternberg jsternberg mentioned this issue Mar 27, 2017
Merged
3 tasks
@maxsivanov
Copy link
Author

maxsivanov commented Mar 28, 2017 •
edited
Loading 

name: collected
id database  retention_policy shard_group start_time           end_time             expiry_time          owners
-- --------  ---------------- ----------- ----------           --------             -----------          ------
26 collected autogen          26          2017-03-06T00:00:00Z 2017-03-13T00:00:00Z 2017-03-13T00:00:00Z
27 collected autogen          27          2017-03-13T00:00:00Z 2017-03-20T00:00:00Z 2017-03-20T00:00:00Z
25 collected autogen          25          2017-03-20T00:00:00Z 2017-03-27T00:00:00Z 2017-03-27T00:00:00Z
35 collected autogen          35          2017-03-27T00:00:00Z 2017-04-03T00:00:00Z 2017-04-03T00:00:00Z

That's it.

@ghost ghost added the review label Apr 2, 2017
@jsternberg jsternberg mentioned this issue Apr 14, 2017
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@jsternberg jsternberg

Labels
area/queries kind/bug review
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@jsternberg @maxsivanov