prestodb/presto · error · SemanticException

MATERIALIZED_VIEW_IS_RECURSIVE

MATERIALIZED_VIEW_IS_RECURSIVE

Error message

Materialized view is recursive

What it means

Presto throws this semantic error when analysis of a materialized view detects that the view references itself, directly or transitively, through another materialized view. The analyzer tracks visited materialized views via MaterializedViewAnalysisState; encountering a table already marked visited means a cycle exists. Materialized view definitions must form a DAG, so recursion is rejected at analysis time.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java:2453

                            NOT_SUPPORTED,
                            table,
                            "INSERT into table %s by selecting from materialized view %s is not supported because %s is a base table of the materialized view",
                            targetTable,
                            optionalMaterializedView.get().getTable(),
                            targetTable);
                }
            }
            Statement statement = analysis.getStatement();
            if (optionalMaterializedView.isPresent() && (statement instanceof Query || statement instanceof Insert || statement instanceof CreateTableAsSelect)) {
                if (isMaterializedViewDataConsistencyEnabled(session) || !isLegacyMaterializedViews(session)) {
                    // When the materialized view has already been expanded, do not process it. Just use it as a table.
                    MaterializedViewAnalysisState materializedViewAnalysisState = analysis.getMaterializedViewAnalysisState(table);

                    if (materializedViewAnalysisState.isNotVisited()) {
                        return processMaterializedView(table, name, scope, optionalMaterializedView.get());
                    }
                    if (materializedViewAnalysisState.isVisited()) {
                        throw new SemanticException(MATERIALIZED_VIEW_IS_RECURSIVE, table, "Materialized view is recursive");
                    }
                }
                else {
                    // when stitching is not enabled, still check permission of each base table
                    MaterializedViewDefinition materializedViewDefinition = optionalMaterializedView.get();
                    analysis.getViewDefinitionReferences().addMaterializedViewDefinitionReference(name, materializedViewDefinition);

                    Query viewQuery = (Query) sqlParser.createStatement(
                            materializedViewDefinition.getOriginalSql(),
                            createParsingOptions(session, warningCollector));

                    analysis.registerMaterializedViewForAnalysis(name, table, materializedViewDefinition.getOriginalSql());
                    process(viewQuery, scope);
                    analysis.unregisterMaterializedViewForAnalysis(table);
                }
            }

            TableColumnMetadata tableColumnsMetadata = getTableColumnsMetadata(session, metadataResolver, analysis.getMetadataHandle(), name);

View on GitHub (pinned to 55bb57d202)

Solutions

1. Rewrite the materialized view definition to reference only base tables or non-recursive views/materialized views
2. If a name collision caused the cycle, rename the materialized view or qualify the base table reference explicitly (schema.table)
3. Break the dependency chain: drop the materialized view that loops back, or build an intermediate materialized view that does not cycle
4. Use DROP MATERIALIZED VIEW and recreate with an acyclic definition

Example fix

// before
CREATE MATERIALIZED VIEW sales_daily AS SELECT * FROM sales_daily;
// after
CREATE MATERIALIZED VIEW sales_daily AS SELECT date, sum(amount) FROM sales GROUP BY date;

-- before creating, ensure the definition does not reference any materialized view that (transitively) references this one
-- check references:
SELECT * FROM system.metadata.materialized_views WHERE name = 'sales_daily';
-- rewrite definition to use base tables only

try {
    session.execute(createMaterializedViewSql);
} catch (SemanticException e) {
    if (e.getCode() == MATERIALIZED_VIEW_IS_RECURSIVE) {
        throw new IllegalStateException("MV definition is cyclic; rewrite to reference base tables");
    }
    throw e;
}

When it happens

Trigger: Creating or querying a materialized view whose definition query selects from itself, or from a chain of materialized views that loops back to it (e.g. CREATE MATERIALIZED VIEW mv AS SELECT * FROM mv). The check fires when the same materialized view is entered a second time during Visitor.processTable traversal while stitching is enabled.

Common situations: Accidental self-reference when a view name shadows a base table name; re-creating a materialized view with a definition copied from the old version that referenced the old view; incremental refactorings where a base table was replaced by a materialized view of the same name, closing a cycle.

Related errors

• VIEW_IS_RECURSIVE
• NOT_SUPPORTED
• INVALID_TABLE_PROPERTY
• NOT_SUPPORTED
• NOT_SUPPORTED

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/11d6bdef78419c1e. Report an issue: GitHub.