prestodb/presto · error · SemanticException

NOT_SUPPORTED

NOT_SUPPORTED

Error message

Materialized View definition does not support multiple instances of same table

What it means

Materialized view plans may reference the same base table only once. The codebase uses table names as map keys (e.g. partition mapping), which breaks if a table appears multiple times in the view definition. visitTable tracks visited tables in the context and throws NOT_SUPPORTED when a table is seen a second time.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/MaterializedViewPlanValidator.java:59

        extends DefaultTraversalVisitor<Void, MaterializedViewPlanValidator.MaterializedViewPlanValidatorContext>
{
    protected MaterializedViewPlanValidator()
    {}

    public static void validate(Query viewQuery)
    {
        new MaterializedViewPlanValidator().process(viewQuery, new MaterializedViewPlanValidatorContext());
    }

    @Override
    protected Void visitTable(Table node, MaterializedViewPlanValidatorContext context)
    {
        // Materialized View Definition does not support have multiple instances of same table. We have this assumption throughout our codebase as we use it
        // for keys in several maps. For e.g. Partition mapping logic would need to be rewritten by considering partitions from each instance
        // of base table separately. We will need to use (table name + node location) as an identifier in all such places. For now, we just
        // forbid it.
        if (!context.addTable(node)) {
            throw new SemanticException(NOT_SUPPORTED, node, "Materialized View definition does not support multiple instances of same table");
        }

        return super.visitTable(node, context);
    }

    @Override
    protected Void visitQuery(Query node, MaterializedViewPlanValidatorContext context)
    {
        if (node.getLimit().isPresent()) {
            throw new SemanticException(NOT_SUPPORTED, node, "LIMIT clause in materialized view is not supported.");
        }
        return super.visitQuery(node, context);
    }

    @Override
    protected Void visitQuerySpecification(QuerySpecification node, MaterializedViewPlanValidatorContext context)
    {
        if (node.getLimit().isPresent()) {

View on GitHub (pinned to 55bb57d202)

Solutions

Remove the self-join from the materialized view definition.
Materialize a copy/alias of the table (CREATE TABLE t2 AS SELECT * FROM t) and join t with t2 in the view.
Rewrite the logic without a self-join (e.g. window functions or aggregation) if possible.

Example fix

// before
CREATE MATERIALIZED VIEW mv AS SELECT a.k, b.v FROM t a JOIN t b ON a.k = b.parent;
// after
CREATE TABLE t_copy AS SELECT * FROM t;
CREATE MATERIALIZED VIEW mv AS SELECT a.k, b.v FROM t a JOIN t_copy b ON a.k = b.parent;

Defensive patterns

Strategy: validation

Validate before calling

// Ensure the MV definition references each table at most once:
const tables = [...mvDefinitionSql.matchAll(/\bFROM\s+([\w."]+)|\bJOIN\s+([\w."]+)/gi)].map(m => (m[1] || m[2]).toLowerCase());
const dupes = tables.filter((t, i) => tables.indexOf(t) !== i);
if (dupes.length) throw new Error("MV definition references the same table multiple times: " + dupes.join(", "));

Try / catch

catch (SemanticException e) {
  if (e.getCode() == SemanticErrorCode.NOT_SUPPORTED && e.getMessage().contains("multiple instances of same table")) {
    // clone the table (CREATE TABLE t_copy AS ...) and rewrite the self-join
  } else { throw e; }
}

Prevention

Avoid self-joins in materialized view definitions.
Detect duplicate table references during DDL linting.
Materialize a copy of the table when self-join logic is unavoidable.

When it happens

Trigger: MV definition referencing the same table twice, e.g. SELECT ... FROM t a JOIN t b ON ..., or FROM t, t — the second visitTable call fails context.addTable(node).

Common situations: Self-joins in view definitions (e.g. parent/child rows of the same table); auto-generated SQL duplicating tables; users porting self-join views from other engines.

Understand the failure class

Background: Presto NOT_SUPPORTED error: what "not supported" means and how to fix it — this error's family across 3 libraries.

Related errors

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