{"record":{"id":"0909300a977551fd","repo":"prestodb/presto","slug":"not-supported-090930","errorCode":"NOT_SUPPORTED","errorMessage":"Materialized View definition does not support multiple instances of same table","messagePattern":"Materialized View definition does not support multiple instances of same table","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/MaterializedViewPlanValidator.java","lineNumber":59,"sourceCode":"        extends DefaultTraversalVisitor<Void, MaterializedViewPlanValidator.MaterializedViewPlanValidatorContext>\n{\n    protected MaterializedViewPlanValidator()\n    {}\n\n    public static void validate(Query viewQuery)\n    {\n        new MaterializedViewPlanValidator().process(viewQuery, new MaterializedViewPlanValidatorContext());\n    }\n\n    @Override\n    protected Void visitTable(Table node, MaterializedViewPlanValidatorContext context)\n    {\n        // Materialized View Definition does not support have multiple instances of same table. We have this assumption throughout our codebase as we use it\n        // for keys in several maps. For e.g. Partition mapping logic would need to be rewritten by considering partitions from each instance\n        // of base table separately. We will need to use (table name + node location) as an identifier in all such places. For now, we just\n        // forbid it.\n        if (!context.addTable(node)) {\n            throw new SemanticException(NOT_SUPPORTED, node, \"Materialized View definition does not support multiple instances of same table\");\n        }\n\n        return super.visitTable(node, context);\n    }\n\n    @Override\n    protected Void visitQuery(Query node, MaterializedViewPlanValidatorContext context)\n    {\n        if (node.getLimit().isPresent()) {\n            throw new SemanticException(NOT_SUPPORTED, node, \"LIMIT clause in materialized view is not supported.\");\n        }\n        return super.visitQuery(node, context);\n    }\n\n    @Override\n    protected Void visitQuerySpecification(QuerySpecification node, MaterializedViewPlanValidatorContext context)\n    {\n        if (node.getLimit().isPresent()) {","sourceCodeStart":41,"sourceCodeEnd":77,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/MaterializedViewPlanValidator.java#L41-L77","documentation":"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.","triggerScenarios":"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).","commonSituations":"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.","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."],"exampleFix":"// before\nCREATE MATERIALIZED VIEW mv AS SELECT a.k, b.v FROM t a JOIN t b ON a.k = b.parent;\n// after\nCREATE TABLE t_copy AS SELECT * FROM t;\nCREATE MATERIALIZED VIEW mv AS SELECT a.k, b.v FROM t a JOIN t_copy b ON a.k = b.parent;","handlingStrategy":"validation","validationCode":"// Ensure the MV definition references each table at most once:\nconst tables = [...mvDefinitionSql.matchAll(/\\bFROM\\s+([\\w.\"]+)|\\bJOIN\\s+([\\w.\"]+)/gi)].map(m => (m[1] || m[2]).toLowerCase());\nconst dupes = tables.filter((t, i) => tables.indexOf(t) !== i);\nif (dupes.length) throw new Error(\"MV definition references the same table multiple times: \" + dupes.join(\", \"));","typeGuard":null,"tryCatchPattern":"catch (SemanticException e) {\n  if (e.getCode() == SemanticErrorCode.NOT_SUPPORTED && e.getMessage().contains(\"multiple instances of same table\")) {\n    // clone the table (CREATE TABLE t_copy AS ...) and rewrite the self-join\n  } else { throw e; }\n}","preventionTips":["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."],"tags":["materialized-view","self-join","plan-validation","not-supported"],"backgroundTag":"mv-duplicate-base-table","analyzedSha":"55bb57d202de3b926896fa966c2c4a44c779634e","analyzedAt":"2026-09-04T12:50:26.162Z","contentChangedAt":"2026-09-04T12:50:26.162Z","schemaVersion":2},"datasetVersion":"2026-09-11T21:17:09.523Z"}