griddb

Issue for nested SQL with GroupByRange in GridDB

Found an issue for nested SQL with GroupByRange. It seems issue related to the position calculation (aggregated) columns

Please check

CREATE TABLE trend_data2 (
ts TIMESTAMP PRIMARY KEY,
value INTEGER
);

INSERT INTO trend_data2 VALUES(TIMESTAMP('2023-01-01T00:00:00Z'), 5);
INSERT INTO trend_data2 VALUES(TIMESTAMP('2023-01-01T00:00:10Z'), 10);
INSERT INTO trend_data2 VALUES(TIMESTAMP('2023-01-01T00:00:20Z'), 15);
INSERT INTO trend_data2 VALUES(TIMESTAMP('2023-01-01T00:00:40Z'), 25);

SELECT COUNT(*) FROM (
SELECT * FROM trend_data2
WHERE
ts BETWEEN TIMESTAMP('2023-01-01T00:00:00Z') AND
TIMESTAMP('2023-01-01T00:00:40Z')
GROUP BY RANGE (ts) EVERY (10,SECOND) FILL (LINEAR) ) dummy
Solution

  • When aggregating columns in a nested query, the position calculation for the outer aggregation might not account for the filled or interpolated rows correctly. Try performing the aggregation and counting separately.

    WITH filled_data AS (
  SELECT ts, value FROM trend_data2
  WHERE ts BETWEEN TIMESTAMP('2023-01-01T00:00:00Z') AND TIMESTAMP('2023-01-01T00:00:40Z')
  GROUP BY RANGE (ts) EVERY (10, SECOND) FILL (LINEAR)
)
SELECT COUNT(*) FROM filled_data;

    Let me know if this works.