Skip to content

limitations.md

Latest commit

 

History

History
155 lines (130 loc) · 17.6 KB

limitations.md

File metadata and controls

155 lines (130 loc) · 17.6 KB
Raw

Limitations

Materialized views can be created using any SQL construct. Unsupported operators automatically fall back to full refresh (the entire view is recomputed from scratch on each PRAGMA refresh() call). This page consolidates all known limitations.

The IVM-compatibility check lives in src/core/incremental_checker.cpp (AnalyzeNode); anything it flags as incremental_compatible = false routes to RefreshType::FULL_REFRESH.

Constructs that trigger full refresh

Aggregate forms

Construct Why
<agg>(...) FILTER (WHERE predicate) Rewritten to AGG(CASE WHEN p THEN arg END) by RewriteAggregateFilters before the checker sees the plan. Fully incremental — no full refresh. Exception: a FILTER that survives normalization (e.g. on a DISTINCT aggregate) triggers full refresh; LIST(...) FILTER uses group recompute.
GROUPING SETS, CUBE, ROLLUP without visible group keys Our delta pipeline groups once; can't emit the cross-grouped subtotal rows. Detected via LogicalAggregate::grouping_sets.size() > 1. With visible group keys these views use GROUP_RECOMPUTE instead (see below). LPTS doesn't round-trip the ROLLUP annotation, so the parser substitutes the user's original SQL for both initial populate and recompute.
Aggregates not in SUPPORTED_AGGREGATES (STRING_AGG, LISTAGG, GROUP_CONCAT, MEDIAN, percentiles, APPROX_, QUANTILE_, ANY_VALUE, …) Order-dependent, holistic, or non-decomposable; no known delta formula. Supported set: count_star, count, sum, min, max, avg, list, stddev, stddev_samp, stddev_pop, variance, var_samp, var_pop, bool_and, bool_or, arg_min, and arg_max.
Correlation / regression aggregates: CORR, COVAR_POP, COVAR_SAMP, REGR_* (REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY) LPTS does not round-trip these aggregates back to SQL, so the delta plan can't be serialised. Routed to FULL_REFRESH.
HAVING referencing IS NULL / IS NOT NULL / a BOUND_AGGREGATE directly Detected by the HAVING rewriter; treated as group-recompute (same path as other partial-recompute HAVING views).
SELECT DISTINCT over aggregate results The DISTINCT key is an aggregate value, not a source key that affected-group recompute can recover (SelectRefreshType).
Aggregates over SEMI/ANTI joins without visible group keys No affected key can be derived. With group keys they use GROUP_RECOMPUTE.

Operators

Construct Why
Recursive CTEs Semi-naive evaluation not yet implemented.
MARK joins and semi/anti shapes outside the aux-state extractor Complex correlated shapes need state OpenIVM does not maintain. Supported SEMI JOIN, ANTI JOIN, EXISTS, NOT EXISTS, IN (subquery), and NULL-aware NOT IN (subquery) projection shapes use the aux-state path; see Semi and anti join.
POSITIONAL JOIN, TABLESAMPLE / USING SAMPLE Output depends on physical row positions or sampling, not on a local delta rule. A sample without a fixed seed is also volatile.
ASOF JOIN outside window or grouped-aggregate shapes ASOF joins are maintained by partition recompute (under a window) or group recompute (under a grouped aggregate); other shapes use full refresh.
LIMIT without deterministic ORDER BY (or with ties on the ORDER BY key) Row selection is non-deterministic between MV creation and recompute — the MV and the base query can legitimately return different subsets, so recompute and the EXCEPT ALL verify diverge. Not a code bug; add a unique ORDER BY to make the view deterministic.
Any operator the plan walk doesn't recognize (falls into the default: branch of the compatibility check) Conservatively treated as unsupported until a rewrite rule lands.
Any plan that LPTS (LogicalPlanToString) can't serialise back to SQL Caught at refresh-plan compile time; OpenIVM falls back to full recompute (DELETE FROM data; INSERT INTO data SELECT * FROM view_query). Subsumes ordered-set aggregates and a few other corner cases — covered without per-construct enumeration.

Note: ORDER BY + LIMIT k (top-k) is now supported — see the partial-recompute table below and operators/top-k.md.

Expressions

Construct Why
RANDOM(), UUID(), NOW() (when evaluated per-row), any function with FunctionStability::VOLATILE or NON_DETERMINISTIC Result depends on evaluation time, so the MV snapshot cannot equal a fresh recompute. Detected via HasVolatileExpression on filter / projection / union / distinct / aggregate nodes.
Non-additive scalar expression above an aggregate that references aggregate output (e.g. CASE WHEN SUM(x) > 1000 THEN 'big' ELSE 'small' END, string concat of aggregate results) Can't sum deltas of the scalar, can't pass through either (value depends on merged aggregate state). The compiler triggers group-recompute for any non-summable non-key column — see below.

Constructs that trigger partial recompute (not full refresh, but not full incremental either)

Construct Strategy
GROUP BY … ORDER BY col LIMIT k (aggregate top-k) All groups are maintained in openivm_data_<view> via the normal AGGREGATE_GROUP path; ORDER BY … LIMIT k is applied when the visible rows are published to openivm_visible_<view> after each refresh. Empty-delta skip avoids any work when no rows changed. See operators/top-k.md.
SELECT cols … ORDER BY col LIMIT k (projection top-k, no GROUP BY) Incremental maintenance over the unlimited SIMPLE_PROJECTION result. ORDER BY … LIMIT k is applied when the visible rows are published. See operators/top-k.md.
UNION ALL over per-branch aggregates Classified as SIMPLE_AGGREGATE when there is no reliable single group-key set. The MERGE formula applies delta sums over all output columns without a per-group key index. Z-set-correct, but less precise than a branch-aware aggregate path.
COUNT(DISTINCT x), SUM(DISTINCT x), other DISTINCT aggregates with GROUP BY GROUP_RECOMPUTE of the affected groups by default. With SET openivm_stateful_auxstate = true, a single-source grouped COUNT(DISTINCT x) uses COUNT_DISTINCT_INCREMENTAL with per-(group, value) multiplicity state.
GROUPING SETS, CUBE, ROLLUP with visible group keys GROUP_RECOMPUTE of the affected keys.
Inner DISTINCT directly inside a subquery feeding an outer AGGREGATE (e.g. SELECT g, SUM(c) FROM (SELECT DISTINCT g, m, c FROM t) GROUP BY g) Two paths are available. Default (GROUP_RECOMPUTE): for each base table with a non-empty delta, the LPTS view query is scoped to delta-touched rows; the affected-keys set drives DELETE + INSERT on openivm_data_<view>. Correct but does more work than strictly necessary. Aux-state path (openivm_distinct_aux_state = true, RefreshType::DISTINCT_INCREMENTAL): DBSP-correct Z-set maintenance via a per-tuple count auxiliary table openivm_distinct_count_<view>. Δdistinct fires only when the input count crosses zero (sgn(R[t])), driving ±1 into the parent SUM/COUNT MERGE. Strictly minimal delta; only v0 (single base-table DISTINCT, single SUM aggregate). Multi-source DISTINCT demotes to GROUP_RECOMPUTE.
LATERAL / correlated subquery shapes represented as DELIM_JOIN / DEPENDENT_JOIN Affected-key GROUP_RECOMPUTE: visible correlated output columns are used as recompute keys, then only those keys are deleted/reinserted from the view query. This supports correlated aggregate lateral shapes and scalar correlated subqueries planned as SINGLE DELIM_JOIN. It is correct and incremental, but less precise than a fully algebraic correlated delta.
Window functions (ROW_NUMBER, RANK, NTILE, LAG, LEAD, …) on a single table Partition-recompute: only partitions with delta rows are re-evaluated. See operators/window-functions.md. Caveat: NTILE / RANK / ROW_NUMBER with ties on the ORDER BY key are inherently non-deterministic — multiple recomputes of the same data may legitimately produce different bucket / rank assignments.
Window functions over JOINs Partition recompute when affected partition values can be derived from source lineage; otherwise full recompute.
LEFT JOIN / RIGHT JOIN aggregates Incremental MERGE for supported aggregate shapes when openivm_left_join_merge=true; group-recompute fallback for shapes where NULL-padding does not compose with delta MERGE. See operators/left-join.md.
FULL OUTER JOIN projection views Bidirectional key-based partial recompute (Zhang & Larson).
FULL OUTER JOIN aggregate views MERGE plus targeted recompute for unmatched changes when openivm_full_outer_merge=true; group-recompute fallback is available when the setting is disabled or the aggregate shape is unsafe.

Join limitations

  • Maximum 16 tables in a single join (openivm::MAX_JOIN_TABLES).
  • INNER JOIN, CROSS JOIN, and arbitrary-predicate joins use the inclusion-exclusion delta rule by default. Eligible SIMPLE_PROJECTION refresh SQL compiled for external engines uses the regular-table N-term rule instead, and joins whose leaves are all DuckLake scans use N-term telescoping. CROSS JOIN is treated as a join without a condition. See Inner join.
  • Partial-recompute strategies for LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN are documented in the partial-recompute table above.
  • SEMI JOIN, ANTI JOIN, EXISTS, and NOT EXISTS are incrementally maintained only for the projection/filter shapes documented in Semi and anti join. This includes IN and NOT IN subqueries. Ungrouped aggregates over semi/anti output fall back to full refresh; grouped ones use GROUP_RECOMPUTE.

DuckLake-specific limitations

  • No FK constraints. DuckLake does not support FOREIGN KEY constraints, so FK-aware pruning is not available.
  • No ART indexes. DuckLake does not support DuckDB-native index types. Group column identification uses metadata instead.

Supported aggregates and their maintenance strategy

Aggregate Incremental Strategy
SUM Yes Delta addition via MERGE.
COUNT, COUNT(*) Yes Delta addition via MERGE.
AVG Yes (1–2 ULP drift on DECIMAL) Decomposed to hidden SUM + COUNT; the visible AVG column is recomputed from the merged totals. DuckDB's native AVG(DECIMAL) uses internal compensated arithmetic that no SUM/COUNT decomposition reproduces bit-exactly — MV values can differ from the base query in the last 1–2 ULPs (e.g. 47.989999999999994884 vs 47.99000000000000199). Results are semantically correct; the rewriter benchmark verifies DOUBLE/FLOAT columns with printf('%.12g', col) (12 significant digits). Unit tests in test/sql/ stay strict — use a DOUBLE/INT base column or cast inside the aggregate (AVG(val::DOUBLE)) when writing tests that exercise AVG on DECIMAL. AVG inside a CTE that is then joined to another relation is also fully incremental — RewriteDerivedAggregates descends into nested PROJECTION→AGGREGATE shapes.
STDDEV, VARIANCE, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP Yes (same 1–2 ULP drift as AVG) Decomposed to hidden SUM + SUM(x*x) + COUNT; variance recomputed post-MERGE. The MERGE formula wraps in CASE WHEN count > threshold (sample: 1, population: 0) and clamps GREATEST(var, 0::DOUBLE) before sqrt, matching the CREATE-MV formula and preventing sqrt-of-negative crashes when floating-point reassociation drifts a flat-valued group's variance below zero. Like AVG, fully supported inside nested CTE-then-join patterns.
MIN, MAX Partial Insert-only deltas for a group: incremental via GREATEST/LEAST. Any delete touching a group: group-recompute (delete the affected groups from the data table, re-insert from the view query).
BOOL_AND, BOOL_OR Yes (via group-recompute) BOOLEAN is a non-summable type; detected in CompileAggregateGroups and routed to group-recompute. Z-set correct: BOOL_AND = false_count = 0, BOOL_OR = true_count > 0.
AGG(...) FILTER (WHERE p) Yes Rewritten by RewriteAggregateFilters to AGG(CASE WHEN p THEN arg END) before plan analysis. All COUNT/SUM/AVG/MIN/MAX/STDDEV variants work; group-recompute is used when the rewritten aggregate makes the column non-summable.
LIST, LIST(x ORDER BY y) Yes Numeric fixed-shape list-valued outputs can use element-wise list arithmetic. LIST(...) FILTER and non-summable list shapes use affected-group recompute so DuckDB's NULL-element semantics are preserved.
Any visible MV column with a non-summable type (VARCHAR literal, UPPER(group_col), CASE over aggregate, BOOLEAN predicate on aggregate, LIST) Yes (via group-recompute) The delta SUM(openivm_multiplicity * col) formula can't type-check for these columns; detected in CompileAggregateGroups via the delta-view column types and routed to group-recompute. Unified path with LIST and MIN/MAX above.
HAVING Partial Group-recompute for affected groups; openivm_having_merge=true (default) uses the MERGE path when the stored data table holds all groups.

Other limitations

  • DROP MATERIALIZED VIEW syntax is not supported because DuckDB's parser intercepts it before the extension can rewrite it. Use DROP VIEW <mv_name> instead. OpenIVM cleans the user-facing view, data table, MV delta table, base delta tables that are no longer shared, and metadata rows.

  • Schema evolution is supported for ADD COLUMN, DROP COLUMN, and RENAME COLUMN on base tables. RENAME TO and ALTER COLUMN TYPE are rejected on tracked sources. Dropping a referenced column is blocked with an error. Renaming a referenced column rewrites stored MV SQL and refresh metadata, including supported aux-state and lineage metadata.

  • Concurrency. Tracked source writes, refreshes, and MV lifecycle operations in one database are serialized by a single mutation gate, so unrelated OpenIVM writes also wait for each other; see Concurrency.

  • Separately placed metadata (openivm_metadata_catalog in another database than the view; see metadata placement):

    • CREATE, REFRESH, DROP VIEW, DROP TABLE ... CASCADE and RENAME COLUMN of a source that views depend on are rejected inside explicit transactions, because DuckDB cannot commit two databases atomically. Run them in autocommit mode, or keep the metadata schema in the view's own database.
    • A refresh interrupted between its data and watermark commits makes the next refresh a full recompute.
    • A database that already has views registered in another location cannot adopt a configured location until those views are migrated or dropped.
    • Remote metadata supports native DuckDB files and PostgreSQL (TYPE postgres) only. Its integration test runs against a local PostgreSQL 16 service container in CI; managed providers are untested. Clients sharing a remote metadata schema serialize refreshes per view through a renewed lease.
    • A DuckLake source change that another process commits while a refresh runs is neither lost nor applied twice, but it makes that refresh retry, and a DuckLake view's retry is a full recompute. A source that keeps changing through three attempts fails the refresh with changed while refreshing; see Concurrency.
    • Lease times are PostgreSQL server times, so client clock offsets do not matter. A client stops writing view data a third of openivm_metadata_lease_seconds before its lease can expire on the server's clock. This assumes the server's clock is not stepped forward by more than that margin during a refresh. A single refresh statement running longer than the margin can still commit after another client took over; if PostgreSQL is then unreachable, the error says could not mark the view for recomputation, and the view must be repaired with a full refresh (SET openivm_refresh_mode = 'full').
    • Materialized views cannot be created inside a PostgreSQL metadata catalog.
    • The view-matching signature columns are not stored in remote metadata catalogs.

  • Refresh in a multi-statement query string. PRAGMA refresh resolves view metadata when DuckDB expands the pragma, before earlier statements in the same query string have run. A single duckdb -c "CREATE MATERIALIZED VIEW ...; PRAGMA refresh(...)" call therefore fails with "Table with name openivm_views does not exist" on a fresh database. Run the statements separately, from a script, or at the prompt.

  • Window functions over DuckLake with non-output partition keys or unsupported lineage shapes fall back to full recompute (DELETE FROM data; INSERT INTO data SELECT * FROM view_query). Partition-level recompute is used when changed partition values can be derived from DuckLake snapshot diffs and lineage metadata. When the PARTITION BY column is dropped by the outer SELECT (e.g. WITH cte AS (… PARTITION BY a, b, c …) SELECT a, c, … FROM cte where b is projected out), the outer WHERE <b> IN (…) can't resolve the column, so OpenIVM can't identify affected rows in the data table. A proper incremental fix requires rewriting the view query to push the partition filter into the base scan; not yet implemented.