{"record":{"id":"3a27f76b6d2cc904","repo":"prestodb/presto","slug":"not-supported-3a27f7","errorCode":"NOT_SUPPORTED","errorMessage":"Limit clause is not supported in query optimizer","messagePattern":"Limit clause is not supported in query optimizer","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/MaterializedViewInformationExtractor.java","lineNumber":51,"sourceCode":"import java.util.Map;\nimport java.util.Optional;\nimport java.util.Set;\n\nimport static com.facebook.presto.sql.ExpressionUtils.removeGroupingElementPrefix;\nimport static com.facebook.presto.sql.ExpressionUtils.removeSingleColumnPrefix;\nimport static com.facebook.presto.sql.analyzer.SemanticErrorCode.NOT_SUPPORTED;\nimport static com.google.common.base.Preconditions.checkState;\n\npublic class MaterializedViewInformationExtractor\n        extends DefaultTraversalVisitor<Void, Void>\n{\n    private final MaterializedViewInfo materializedViewInfo = new MaterializedViewInfo();\n\n    @Override\n    protected Void visitQuerySpecification(QuerySpecification node, Void context)\n    {\n        if (node.getLimit().isPresent()) {\n            throw new SemanticException(NOT_SUPPORTED, node, \"Limit clause is not supported in query optimizer\");\n        }\n        if (node.getHaving().isPresent()) {\n            throw new SemanticException(NOT_SUPPORTED, node, \"Having clause is not supported in query optimizer\");\n        }\n        if (!node.getFrom().isPresent()) {\n            throw new SemanticException(NOT_SUPPORTED, node, \"Materialized view with no From clause is not supported in query optimizer\");\n        }\n        materializedViewInfo.setBaseTable(node.getFrom().get());\n        materializedViewInfo.setWhereClause(node.getWhere());\n        return super.visitQuerySpecification(node, context);\n    }\n\n    protected Void visitSelect(Select node, Void context)\n    {\n        super.visitSelect(node, context);\n        materializedViewInfo.setDistinct(node.isDistinct());\n        return null;\n    }","sourceCodeStart":33,"sourceCodeEnd":69,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/MaterializedViewInformationExtractor.java#L33-L69","documentation":"When extracting metadata from a materialized view's SQL definition, MaterializedViewInformationExtractor walks the parsed AST. A LIMIT clause in the view definition breaks the assumption that the view is a pure, unbounded derivation of base-table data, so rewriting queries onto it could return wrong rows. The extractor therefore rejects the definition up front with NOT_SUPPORTED.","triggerScenarios":"CREATE MATERIALIZED VIEW ... AS SELECT ... FROM ... LIMIT n — visitQuerySpecification sees node.getLimit().isPresent() during MV metadata extraction (at view creation or refresh).","commonSituations":"Copy-pasting a top-N reporting query into a materialized view definition; porting views from databases that allow LIMIT in views; users trying to cap MV size with LIMIT.","solutions":["Remove the LIMIT clause from the materialized view definition.","If a row cap is needed, filter in the WHERE clause instead (e.g. date-range predicate).","Enforce top-N semantics in the consumer query at read time, not in the view."],"exampleFix":"// before\nCREATE MATERIALIZED VIEW mv AS SELECT * FROM orders ORDER BY ts LIMIT 1000;\n// after\nCREATE MATERIALIZED VIEW mv AS SELECT * FROM orders; -- apply ORDER BY/LIMIT in the reading query","handlingStrategy":"validation","validationCode":"// Validate the MV definition before DDL: the query must not contain LIMIT.\nfunction validateMvDefinition(sql) {\n  if (/\\bLIMIT\\b/i.test(extractSelectBody(sql))) {\n    throw new Error(\"Materialized view definition must not contain LIMIT\");\n  }\n}","typeGuard":null,"tryCatchPattern":"catch (SemanticException e) {\n  if (e.getCode() == SemanticErrorCode.NOT_SUPPORTED && e.getMessage().contains(\"Limit clause is not supported\")) {\n    // rewrite the DDL without LIMIT and retry creation\n  } else { throw e; }\n}","preventionTips":["Never put ORDER BY/LIMIT inside MV definitions; apply at read time.","Add DDL linting that rejects LIMIT in CREATE MATERIALIZED VIEW.","Use WHERE filters to bound view size."],"tags":["materialized-view","limit-clause","not-supported","ddl"],"backgroundTag":"unsupported-materialized-view-definition","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"}