Roll up OVER (1m) with GROUP BY giving incorrect records

[ShivaniSK]

Hi @alex-karpovich ,
Currently we are executing below query where we want to roll up data at minute level. Post which we want to group by venue within each minute and sum up the volume.

Below is a sample stream with data.

DURABLE STREAM "market_trades" (
    CLASS "com.db.jupiter.models.MarketTrades" 'Market Trades' (
        "venue" 'Venue' VARCHAR,
        "volume" 'Volume' FLOAT DECIMAL64
    );
)
OPTIONS (FIXEDTYPE; PERIODICITY = 'IRREGULAR'; HIGHAVAILABILITY = FALSE; UNIQUE = FALSE; STORAGEVERSION = '5')
COMMENT 'market_trades_stream'

<style> </style>

symbol	timestamp	venue	volume
ABC	2023-10-06T10:05:07.364Z	Venue1	9000
ABC	2023-10-06T10:55:35.949Z	Venue2	12
ABC	2023-10-06T10:55:35.949Z	Venue2	15
ABC	2023-10-06T10:55:35.949Z	Venue2	5
ABC	2023-10-06T10:55:35.949Z	Venue2	10
ABC	2023-10-06T10:55:35.949Z	Venue2	16
ABC	2023-10-06T10:55:36.011Z	Venue2	12
ABC	2023-10-06T10:55:36.050Z	Venue3	12
ABC	2023-10-06T12:39:29.000Z	Venue1	130000
ABC	2023-10-06T15:03:22.616Z	Venue1	40000
ABC	2023-10-06T15:13:16.913Z	Venue1	130000
ABC	2023-10-06T15:14:15.779Z	Venue1	10000
ABC	2023-10-06T15:15:05.000Z	Venue1	600000
ABC	2023-10-06T15:15:33.537Z	Venue1	10000
ABC	2023-10-06T15:36:30.093Z	Venue1	607000
ABC	2023-10-06T16:17:46.111Z	Venue1	510000

The timebase query

select SUM{} (volume) as volumeAgg, venue 
from market_trades OVER TIME (1m) 
where timestamp BETWEEN '2023-10-06 10:00:00.000'd AND '2023-10-06 23:59:59.999'd 
AND SYMBOL IN ('ABC') 
AND volume > 0.0 GROUP BY venue

Expected Output:

<style> </style>

symbol	timestamp	VOLUMEAGG	venue
ABC	2023-10-06T10:06:00.000Z	9000	Venue1
ABC	2023-10-06T10:56:00.000Z	70	Venue2
ABC	2023-10-06T10:56:00.000Z	12	Venue3
ABC	2023-10-06T12:40:00.000Z	130000	Venue1
ABC	2023-10-06T15:04:00.000Z	40000	Venue1
ABC	2023-10-06T15:14:00.000Z	130000	Venue1
ABC	2023-10-06T15:15:00.000Z	10000	Venue1
ABC	2023-10-06T15:16:00.000Z	610000	Venue1
ABC	2023-10-06T15:37:00.000Z	607000	Venue1
ABC	2023-10-06T16:18:00.000Z	510000	Venue1

Actual Output:

<style> </style>

symbol	timestamp	VOLUMEAGG	venue
ABC	2023-10-06T10:06:00.000Z	9000	Venue1
ABC	2023-10-06T10:56:00.000Z	70	Venue2
ABC	2023-10-06T10:56:00.000Z	12	Venue3
ABC	2023-10-06T12:40:00.000Z	130000	Venue1
ABC	2023-10-06T15:04:00.000Z	40000	Venue1
ABC	2023-10-06T15:14:00.000Z	130000	Venue1
ABC	2023-10-06T15:15:00.000Z	10000	Venue1
ABC	2023-10-06T15:16:00.000Z	610000	Venue1
ABC	2023-10-06T15:37:00.000Z	607000	Venue1
ABC	2023-10-06T16:18:00.000Z	510000	Venue1
ABC	2023-10-06T16:18:00.000Z	70	Venue2
ABC	2023-10-06T16:18:00.000Z	12	Venue3

As can be seen the expected and actual output does not match.
There are 2 extra records at the end
ABC | 2023-10-06T16:18:00.000Z | 70 | Venue2
ABC | 2023-10-06T16:18:00.000Z | 12 | Venue3
which are not a part of original stream.

Can you please look into the issue.
Also we noticed that the issue occurs when the timebase query has both OVER and GROUP BY functionalities.

Regards,
Shivani

[alex-karpovich]

fixed in https://github.com/finos/TimeBase-CE/releases/tag/6.1.24

[ankitsoin]

Hi @alex-karpovich, the new version which was mentioned here https://github.com/finos/TimeBase-CE/releases/tag/6.1.24 , does this supports incremental backup/restore using spaces?