SELECT
COALESCE(user_id, -1) AS user_id,
count(id) AS count_per_window,
sum(amount) AS charge_amount_per_window,
TUMBLE_START(rowtime, INTERVAL '2' YEAR) AS twindow_start,
TUMBLE_END(rowtime, INTERVAL '2' YEAR) AS twindow_end
FROM
event_charge_processed
WHERE capture=true
AND COALESCE(user_id, -1) <> -1
GROUP BY
TUMBLE(rowtime, INTERVAL '2' YEAR),
COALESCE(user_id, -1)
For '1' MONTH intervals it results in 1ms windows, 2 MONTH=2ms, 3 MONTH=3ms …. 1 YEAR=12ms, 2 YEAR=24ms! Which results in incorrect aggregations.
I found that org.apache.calcite.sql.SqlLiteral#getValueAs() treats MONTH/YEAR differently than DAY/HOUR etc. Perhaps the bug is somewhere there (?).
Is this a known issue? Has it been fixed in later versions?
Thanks,
Vinod