prestodb/presto · error · SemanticException

NOT_SUPPORTED

NOT_SUPPORTED

Error message

Limit clause is not supported in query optimizer

What it means

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.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/MaterializedViewInformationExtractor.java:51

import java.util.Map;
import java.util.Optional;
import java.util.Set;

import static com.facebook.presto.sql.ExpressionUtils.removeGroupingElementPrefix;
import static com.facebook.presto.sql.ExpressionUtils.removeSingleColumnPrefix;
import static com.facebook.presto.sql.analyzer.SemanticErrorCode.NOT_SUPPORTED;
import static com.google.common.base.Preconditions.checkState;

public class MaterializedViewInformationExtractor
        extends DefaultTraversalVisitor<Void, Void>
{
    private final MaterializedViewInfo materializedViewInfo = new MaterializedViewInfo();

    @Override
    protected Void visitQuerySpecification(QuerySpecification node, Void context)
    {
        if (node.getLimit().isPresent()) {
            throw new SemanticException(NOT_SUPPORTED, node, "Limit clause is not supported in query optimizer");
        }
        if (node.getHaving().isPresent()) {
            throw new SemanticException(NOT_SUPPORTED, node, "Having clause is not supported in query optimizer");
        }
        if (!node.getFrom().isPresent()) {
            throw new SemanticException(NOT_SUPPORTED, node, "Materialized view with no From clause is not supported in query optimizer");
        }
        materializedViewInfo.setBaseTable(node.getFrom().get());
        materializedViewInfo.setWhereClause(node.getWhere());
        return super.visitQuerySpecification(node, context);
    }

    protected Void visitSelect(Select node, Void context)
    {
        super.visitSelect(node, context);
        materializedViewInfo.setDistinct(node.isDistinct());
        return null;
    }

View on GitHub (pinned to 55bb57d202)

Solutions

  1. Remove the LIMIT clause from the materialized view definition.
  2. If a row cap is needed, filter in the WHERE clause instead (e.g. date-range predicate).
  3. Enforce top-N semantics in the consumer query at read time, not in the view.

Example fix

// before
CREATE MATERIALIZED VIEW mv AS SELECT * FROM orders ORDER BY ts LIMIT 1000;
// after
CREATE MATERIALIZED VIEW mv AS SELECT * FROM orders; -- apply ORDER BY/LIMIT in the reading query
Defensive patterns

Strategy: validation

Validate before calling

// Validate the MV definition before DDL: the query must not contain LIMIT.
function validateMvDefinition(sql) {
  if (/\bLIMIT\b/i.test(extractSelectBody(sql))) {
    throw new Error("Materialized view definition must not contain LIMIT");
  }
}

Try / catch

catch (SemanticException e) {
  if (e.getCode() == SemanticErrorCode.NOT_SUPPORTED && e.getMessage().contains("Limit clause is not supported")) {
    // rewrite the DDL without LIMIT and retry creation
  } else { throw e; }
}

Prevention

When it happens

Trigger: CREATE MATERIALIZED VIEW ... AS SELECT ... FROM ... LIMIT n — visitQuerySpecification sees node.getLimit().isPresent() during MV metadata extraction (at view creation or refresh).

Common situations: 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.

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/3a27f76b6d2cc904. Report an issue: GitHub.