Materialized view refresh fails on subqueries reading the base table

Problem

A materialized view whose WHERE clause contains a scalar subquery reading the same base table can pass CREATE validation but fail every refresh. The refresh leaves the view empty and marks it invalid with a NullPointerException in NativeTimestampFinder.minTimestampExact().

This defect predates #7263. I reproduced it with an aggregating materialized view using SAMPLE BY 1d on that PR's base revision, f2e1a8de1ab1201f57ca144b71c36ea0f1358736, and on its reviewed head, 969d1d1ec2b855defd1829548baf1f9ab2f8a512. PR #7263 additionally accepts non-aggregating (passthrough) views that reach the same failure. Neither reproduction needs an EXPIRE ROWS policy.

Follow-up to review finding 1. The scope decision for #7263 defers the shared problem here, including its exposure through the new passthrough feature, rather than adding a passthrough-only workaround.

Reproduction on the base revision and PR head

Run with materialized views and WAL apply enabled:

CREATE TABLE base (ts TIMESTAMP, v INT)
TIMESTAMP(ts) PARTITION BY DAY WAL;

INSERT INTO base VALUES
    ('2024-01-01T00:00:00Z', 100),
    ('2024-01-02T00:00:00Z', 200);

Wait until SELECT count() FROM base returns 2, then run:

CREATE MATERIALIZED VIEW sample_mv
WITH BASE base REFRESH MANUAL AS (
    SELECT ts, sum(v) AS s
    FROM base
    WHERE v = (SELECT max(v) FROM base)
    SAMPLE BY 1d
) PARTITION BY DAY;

REFRESH MATERIALIZED VIEW sample_mv FULL;

After the refresh worker processes the request:

SELECT view_name, view_status, invalidation_reason
FROM materialized_views();

SELECT * FROM sample_mv ORDER BY ts;

Actual result

CREATE and the refresh request return success. The refresh worker fails, the view contains no rows, and its status is invalid with this reason:

Cannot invoke "io.questdb.cairo.vm.api.MemoryR.getLong(long)" because "this.column" is null

The stack includes:

io.questdb.cairo.NativeTimestampFinder.minTimestampExact(NativeTimestampFinder.java:73)
io.questdb.cairo.IntervalFwdPartitionFrameCursor.next(IntervalFwdPartitionFrameCursor.java:183)
io.questdb.griffin.engine.table.FwdTableReaderPageFrameCursor.next(FwdTableReaderPageFrameCursor.java:182)

Running the defining SELECT directly succeeds and returns:

ts                          s
2024-01-02T00:00:00.000000Z   200

A control view defined as SELECT ts, sum(v) AS s FROM base SAMPLE BY 1d, without the scalar subquery, refreshes successfully on both revisions.

Additional exposure in #7263

On the reviewed PR head, the same failure occurs with:

CREATE MATERIALIZED VIEW passthrough_mv
WITH BASE base REFRESH MANUAL AS (
    SELECT * FROM base WHERE v = (SELECT max(v) FROM base)
) PARTITION BY DAY;

REFRESH MATERIALIZED VIEW passthrough_mv FULL;

The base revision rejects this non-aggregating definition at CREATE time. It does not reject the SAMPLE BY definition above.

Cause and why removing the NPE alone is insufficient

MatViewRefreshSqlExecutionContext.overrideWhereIntrinsics() injects refresh timestamp bounds into references to the base table, including the inner SELECT max(v) FROM base. IntervalPartitionFrameCursorFactory.getCursor() activates the requested columns, but the inner query requests only v. The interval scan subsequently needs the timestamp column that column pruning left unopened.

I tested a temporary diagnostic patch that adds the interval scan's timestamp column to its active-column set. This removed the NPE for both view types, but did not make their results correct:

• With cairo.mat.view.rows.per.query.estimate=1, FULL refresh processed the two days in separate chunks. Both views contained the rows for 100 and 200, instead of only 200. The injected bounds made the inner query calculate each chunk's maximum rather than the whole base table's maximum.
• With the default chunk estimate, the initial FULL refresh correctly returned only 200. After inserting ('2024-01-03T00:00:00Z', 300), waiting for WAL apply, and requesting REFRESH MATERIALIZED VIEW <view> INCREMENTAL, both views retained 200 alongside 300. The defining SELECT returned only 300.

These wrong-result observations apply to the diagnostic patch, not to the unmodified revisions, which fail with the NPE. They show why a crash-only fix is not sufficient.

Expected resolution

Choose a shared policy for both aggregating and passthrough materialized views:

• Either reject unsupported same-base-table expression subqueries with a clear SQL error, including a meaningful failure when existing stored definitions compile for refresh;
• Or support them with correct subquery scope and a refresh strategy that revisits older output when a global subquery result changes. Whole-view recomputation may be necessary for these definitions.

If supporting the queries, fix timestamp-column activation without introducing per-cursor allocations. Preserve supported subqueries over other tables rather than banning all expression subqueries.

Validation

• Run the reproduction against both aggregating and passthrough views.
• Cover CREATE, repeated FULL refresh, multi-chunk FULL refresh, and incremental refresh after a new global maximum arrives.
• Compare stored rows with the defining SELECT; a valid status alone is not sufficient.
• Cover aliases/nested subqueries, empty inputs and NULL aggregate values, execution modes, and native-memory cleanup.

Runtime investigation used a live ServerMain, Java 25.0.2 on macOS arm64, and assertions enabled (-ea). I compiled the base revision's Java sources and ran it separately from the PR head. The diagnostic patch remained outside the repository working tree.