SAMPLE BY puts a NULL designated timestamp that isn't in the first row into the wrong bucket on the SAMPLE BY cursor path #7834

To reproduce

TIMESTAMP(col) can designate a column that holds NULL. On the SAMPLE BY cursor path, a row with a NULL timestamp that follows a row with a timestamp is counted in the bucket in progress. Under a time zone west of UTC it opens a bucket labelled in year 294247 instead, and with FILL(LINEAR) the cursor builds buckets up to year 294247. No error is raised. The GROUP BY path sorts its input and returns the row as a NULL bucket.

CREATE TABLE orders (ts TIMESTAMP, filled_at TIMESTAMP, sym SYMBOL, qty DOUBLE) TIMESTAMP(ts) PARTITION BY DAY;
INSERT INTO orders VALUES
    ('2024-01-01T00:00:00Z', '2024-01-01T00:05:00Z', 'a', 1),
    ('2024-01-01T01:00:00Z', NULL, 'b', 2),
    ('2024-01-01T02:00:00Z', '2024-01-01T02:30:00Z', 'a', 4);

-- 1. GROUP BY path
SELECT filled_at, count() c, sum(qty) s FROM orders TIMESTAMP(filled_at) SAMPLE BY 1h;
-- 2. cursor path
SELECT filled_at, count() c, sum(qty) s FROM orders TIMESTAMP(filled_at) SAMPLE BY 1h ALIGN TO FIRST OBSERVATION;
-- 3. cursor path, zone west of UTC
SELECT filled_at, count() c, sum(qty) s
FROM ((SELECT filled_at, qty FROM orders) TIMESTAMP(filled_at))
SAMPLE BY 1h ALIGN TO CALENDAR TIME ZONE 'America/New_York';
-- 4. FILL(LINEAR)
SELECT count() n, min(filled_at) lo, max(filled_at) hi
FROM (SELECT filled_at, sum(qty) s FROM orders TIMESTAMP(filled_at) SAMPLE BY 100d FILL(LINEAR));
-- 5. keyed, year stride, zone west of UTC
SELECT filled_at, sym, count() c, sum(qty) s
FROM ((SELECT filled_at, sym, qty FROM orders) TIMESTAMP(filled_at))
SAMPLE BY 1y ALIGN TO CALENDAR TIME ZONE 'America/New_York';

Statement 4 uses a 100d stride to stay small; a shorter stride means more buckets, and it hasn't been run.

Cause

The cursors read the timestamp of every row after the first without a NULL check.

• AbstractNoRecordSampleByCursor.getBaseRecordTimestamp() returns baseRecord.getTimestamp(timestampIndex) + tzOffset (AbstractNoRecordSampleByCursor.java:209-211). For a NULL, Long.MIN_VALUE plus a zero or positive offset is below the end of the bucket in progress, so notKeyedLoop() (line 289) and SampleByFillNoneRecordCursor.buildMap() (SampleByFillNoneRecordCursor.java:146-147) add the row to that bucket. With a negative offset the sum wraps to a value near Long.MAX_VALUE, and the row opens a bucket there.
• SampleByInterpolateRecordCursorFactory.fillMap() rounds the timestamp of each row with the sampler (SampleByInterpolateRecordCursorFactory.java:649), and the cursor then interpolates over the range between the lowest and the highest bucket.
• SqlCodeGenerator.generateSampleBy() requires a forward scan direction from the base (SqlCodeGenerator.java:9043-9054), but it doesn't check or enforce the order of an explicit TIMESTAMP(col) on the cursor path.

A timestamp that is out of order but not NULL is merged into the bucket in progress in the same way. Only a NULL gives the year 294247 label and the FILL(LINEAR) range.

Scope

• Affected: the SAMPLE BY cursor path over a base that isn't sorted by the designated column. That is FROM t TIMESTAMP(col) with ALIGN TO FIRST OBSERVATION or FILL(LINEAR), and ((sub-query) TIMESTAMP(col)) without an ORDER BY on that column. A sub-query that takes its timestamp from the right-hand side of an ASOF JOIN ... TOLERANCE gives such a base with otherwise ascending values, because the unmatched rows carry NULL.
• Not affected: the GROUP BY path.
• The documentation of the dynamic TIMESTAMP(col) clause asks for data sorted by that column, so these statements are outside the documented contract. The problem is the failure mode: wrong counts, a bucket in year 294247 and a FILL(LINEAR) range up to year 294247, with no error.
• A base sorted by the designated column puts every NULL first, which is a separate case that fix(sql): breaking change 💥 - fix wrong results, failures, hangs and a crash in HORIZON JOIN, WINDOW JOIN, SAMPLE BY, ORDER BY and dateadd() queries #7681 handles. On master, such a base returns one merged row labelled in year 294247 for strides up to 3w. With fix(sql): breaking change 💥 - fix wrong results, failures, hangs and a crash in HORIZON JOIN, WINDOW JOIN, SAMPLE BY, ORDER BY and dateadd() queries #7681, FILL statements fail with SAMPLE BY designated timestamp cannot be NULL, and statements without FILL return the leading NULL rows as a NULL bucket.
• fix(sql): breaking change 💥 - fix wrong results, failures, hangs and a crash in HORIZON JOIN, WINDOW JOIN, SAMPLE BY, ORDER BY and dateadd() queries #7681 doesn't change statements 2, 3 and 4 above (checked at 24b9ffb). Statement 5 fails there with an AssertionError at SampleByFillNoneRecordCursor.buildMap(), which fix(sql): breaking change 💥 - fix wrong results, failures, hangs and a crash in HORIZON JOIN, WINDOW JOIN, SAMPLE BY, ORDER BY and dateadd() queries #7681 adds, where master ends the result early.

Two ways to resolve it: reject a NULL or out-of-order timestamp in the row loops with an error, or sort an explicit TIMESTAMP(col) base on the cursor path as the GROUP BY path does.

QuestDB version

master at 6c63556

OS, in case of Docker specify Docker and the Host OS

Linux

File System, in case of Docker specify Host File System

ext4

Additional context

Found while reviewing #7681. Reproduced through the test harness (AbstractCairoTest) with assertions enabled.