Extra record while doing roll-up and aggregations

[akshah1997]

Hi team,

I have xyz stream with the following data -

TIMESTAMP	SYMBOL	NOTIONAL
2022-08-25 09:22:18	USDINR	100.0
2022-08-25 09:22:52	USDINR	50.0

When we run this query - select sum{} (notional)/1000000 as notional from xyz over TIME (1m) where timestamp between 1661418420000 and 1661471999999; we get the following output -

TIMESTAMP	SYMBOL	NOTIONAL
2022-08-25 09:23:00	USDINR	150.0
2022-08-25 23:59:00	USDINR	150.0

As seen, we are getting the extra record for 23:59:00 which should not exist. Can you please help us with the same?

The same behaviour is not seen when we run select notional from reverse(xyz) where timestamp > '2022-08-25 09:07:00'd -

TIMESTAMP	SYMBOL	NOTIONAL
2022-08-25 09:22:52	USDINR	50.0
2022-08-25 09:22:18	USDINR	100.0

[alex-karpovich]

Greetings, @akshah1997
you need to add RESET keyword to skip synthetic messages which is generated per each interval

select sum{} (notional)/1000000 as notional from xyz RESET over TIME (1m) where timestamp between 1661418420000 and 1661471999999

[ShivaniSK]

Hi @alex-karpovich even after using the "RESET" keyword , we are still getting the same extra record for 23:59:00.

[alex-karpovich]

@ShivaniSK I cannot reproduce this on my dataset for TimeBase version 6.1.17

could you please remind me your TimeBase version?

[alex-karpovich]

we are trying to reproduce this issue

[ShivaniSK]

Hello @alex-karpovich ,
Please follow below steps in order to replicate the issue.

We created a stream "roll_up_bug" with below schema

DURABLE STREAM "roll_up_bug" (
    CLASS "jupiter_pipeline.message.roll_up_bug_message.RollUpBugMessage" 'Roll up' (
        "book_name" 'Book Name' VARCHAR,
        "product_type" 'Product Type' VARCHAR,
        "notional" 'Notional' FLOAT DECIMAL64
    );
)
OPTIONS (FIXEDTYPE; PERIODICITY = 'IRREGULAR'; HIGHAVAILABILITY = FALSE; UNIQUE = FALSE; STORAGEVERSION = '5')
COMMENT 'roll_up_bug'

The stream has below 20 records in it

==> select * from roll_up_bug
>jupiter_pipeline.message.roll_up_bug_message.RollUpBugMessage,TIMESTAMP,SYMBOL,TYPE,book_name,product_type,notional
0,2022-08-25 00:37:44.105,EURUSD,BOOK1,A,1146000
1,2022-08-25 01:05:04.631,EURUSD,BOOK1,A,2000000
2,2022-08-25 01:31:56.485,EURUSD,BOOK1,A,1214000
3,2022-08-25 01:35:06.503,EURUSD,BOOK1,A,1200000
4,2022-08-25 02:00:08.900,EURUSD,BOOK1,A,296000
5,2022-08-25 02:00:09.037,EURUSD,BOOK1,A,2000000
6,2022-08-25 02:00:09.231,EURUSD,BOOK1,A,1500000
7,2022-08-25 02:00:09.447,EURUSD,BOOK1,A,2725800
8,2022-08-25 02:00:09.761,EURUSD,BOOK1,A,1014000
9,2022-08-25 02:00:09.843,EURUSD,BOOK1,A,2802000
10,2022-08-25 02:33:35,EURUSD,BOOK2,A,5000000
11,2022-08-25 02:33:35,EURUSD,BOOK2,A,1000000
12,2022-08-25 02:33:48,EURUSD,BOOK2,A,4000000
13,2022-08-25 02:33:50,EURUSD,BOOK2,A,1000000
14,2022-08-25 02:58:23,EURUSD,BOOK2,A,3700000
15,2022-08-25 06:01:23,EURUSD,BOOK2,A,200000
16,2022-08-25 06:31:12,EURUSD,BOOK2,A,1000000
17,2022-08-25 06:31:12,EURUSD,BOOK2,A,5000000
18,2022-08-25 06:31:14,EURUSD,BOOK2,A,3000000
19,2022-08-25 07:18:31,EURUSD,BOOK2,A,200000

Now on executing below query for rolling up data over 1 min for "BOOK2", we get correct response

==> select * from roll_up_bug RESET over TIME (1m) where timestamp between '2022-08-25 00:00:00.000'd and '2022-08-25 23:59:59.999'd and book_name == 'BOOK2' and symbol == 'EURUSD' and product_type == 'A'
>jupiter_pipeline.message.roll_up_bug_message.RollUpBugMessage,TIMESTAMP,SYMBOL,TYPE,book_name,product_type,notional
0,2022-08-25 02:34:00,EURUSD,BOOK2,A,1000000
1,2022-08-25 02:59:00,EURUSD,BOOK2,A,3700000
2,2022-08-25 06:02:00,EURUSD,BOOK2,A,200000
3,2022-08-25 06:32:00,EURUSD,BOOK2,A,3000000
4,2022-08-25 07:19:00,EURUSD,BOOK2,A,200000

However, on executing the same query as above for "BOOK1" we are getting one extra record in the end with the timestamp same as the last record of the stream which is 2022-08-25 07:19:00

==> select * from roll_up_bug RESET over TIME (1m) where timestamp between '2022-08-25 00:00:00.000'd and '2022-08-25 23:59:59.999'd and book_name == 'BOOK1' and symbol == 'EURUSD' and product_type == 'A'
>jupiter_pipeline.message.roll_up_bug_message.RollUpBugMessage,TIMESTAMP,SYMBOL,TYPE,book_name,product_type,notional
0,2022-08-25 00:38:00,EURUSD,BOOK1,A,1146000
1,2022-08-25 01:06:00,EURUSD,BOOK1,A,2000000
2,2022-08-25 01:32:00,EURUSD,BOOK1,A,1214000
3,2022-08-25 01:36:00,EURUSD,BOOK1,A,1200000
4,2022-08-25 02:01:00,EURUSD,BOOK1,A,2802000
5,2022-08-25 07:19:00,EURUSD,BOOK1,A,2802000

Do let us know if you need any other info related to the stream. Also the data is currently stored using spaces.

Regards,
Shivani

[alex-karpovich]

thanks! @ShivaniSK
We try to reproduce this week

[Romkisel]

Hi, @ShivaniSK,
just to make sure that stream has 20 messages in it, please, do select count{}() from roll_up_bug and send us the result.

[ShivaniSK]

Hello @Romkisel ,
Please find the result as requested

==> select count{} () from roll_up_bug
>_,TIMESTAMP,SYMBOL,TYPE,COUNT{}()
0,2022-08-25 07:18:31,EURUSD,20

[alex-karpovich]

@ShivaniSK please use https://github.com/finos/TimeBase-CE/releases/tag/6.1.19

[ShivaniSK]

Thanks @alex-karpovich . Issues looks resolved now. Upgraded timebase to 6.1.19.