Clickhouse Baseline vs ASAPQuery asymmetry -> SQL `BETWEEN` is inclusive but ASAP maps a 1-second span to a single tumbling window

[akanksha-akkihal]

Summary

For SQL queries of the form:

SELECT quantile(0.95)(pkt_len) FROM netflow_table
WHERE time BETWEEN DATEADD(s, -1, 'T') AND 'T'
GROUP BY srcip

ClickHouse and ASAP/sketchdb do not answer the same question. ClickHouse scans two inclusive seconds of data; sketchdb reads one 1-second precompute window. That produces different GROUP BY cardinality, different aggregates, and unfair latency comparisons.

Root cause

ClickHouse: BETWEEN is inclusive on both ends
a BETWEEN b AND c ≡ a >= b AND a <= c.

ASAP: duration drives a single spatial window, not SQL BETWEEN semantics.
In sqlpattern_parser.rs, time is parsed as:
duration = end - start; // 1 second for T-1 .. T

Impact

• Correctness: SketchDB may produce aggregate results that differ from ClickHouse for the same SQL query, leading to incorrect or unexpected outputs.
• Benchmark Validity: Latency and fidelity comparisons between SketchDB and ClickHouse are not meaningful unless both systems execute semantically equivalent queries.
• Planner/Query Inference: Query templates using patterns such as BETWEEN DATEADD(s, -N, NOW()) AND NOW() may not align with ClickHouse's runtime timestamp semantics, resulting in behavior that differs from user expectations.