prestodb/presto · error · SemanticException

DUPLICATE_RELATION

DUPLICATE_RELATION

Error message

WITH query name '%s' specified more than once

What it means

Semantic check in StatementAnalyzer while processing a WITH clause: two CTEs in the same WITH list declare the same query name. Since each name must bind to exactly one subquery for scope resolution, the analyzer rejects the query, formatting the duplicated name into the message.

Source

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

        private Scope analyzeWith(Query node, Optional<Scope> scope)
        {
            // analyze WITH clause
            if (!node.getWith().isPresent()) {
                return createScope(scope);
            }
            With with = node.getWith().get();
            if (with.isRecursive()) {
                throw new SemanticException(NOT_SUPPORTED, with, "Recursive WITH queries are not supported");
            }

            Scope.Builder withScopeBuilder = scopeBuilder(scope);
            for (WithQuery withQuery : with.getQueries()) {
                Query query = withQuery.getQuery();
                process(query, withScopeBuilder.build());

                String name = withQuery.getName().getValueLowerCase();
                if (withScopeBuilder.containsNamedQuery(name)) {
                    throw new SemanticException(DUPLICATE_RELATION, withQuery, "WITH query name '%s' specified more than once", name);
                }

                // check if all or none of the columns are explicitly alias
                if (withQuery.getColumnNames().isPresent()) {
                    List<Identifier> columnNames = withQuery.getColumnNames().get();
                    RelationType queryDescriptor = analysis.getOutputDescriptor(query);
                    if (columnNames.size() != queryDescriptor.getVisibleFieldCount()) {
                        throw new SemanticException(MISMATCHED_COLUMN_ALIASES, withQuery, "WITH column alias list has %s entries but WITH query(%s) has %s columns", columnNames.size(), name, queryDescriptor.getVisibleFieldCount());
                    }
                }

                withScopeBuilder.withNamedQuery(name, withQuery);
            }

            Scope withScope = withScopeBuilder.build();
            analysis.setScope(with, withScope);
            return withScope;
        }

View on GitHub (pinned to 55bb57d202)

Solutions

1. Rename one of the duplicate WITH queries
2. Merge the two CTE definitions if they compute the same thing
3. Check generated SQL builders for alias collisions

Example fix

// before
WITH sales AS (...), sales AS (...) SELECT ...
// after
WITH sales_2023 AS (...), sales_2024 AS (...) SELECT ...

List<String> names = withQueries.stream().map(q -> q.getName().getValueLowerCase()).collect(toList());
Set<String> dupes = names.stream().filter(n -> Collections.frequency(names, n) > 1).collect(toSet());
if (!dupes.isEmpty()) throw new IllegalArgumentException("Duplicate CTE names: " + dupes);

try { runQuery(); } catch (SemanticException e) { if (e.getCode() == DUPLICATE_RELATION.toErrorCode()) { renameCteAndRetry(); } throw e; }

When it happens

Trigger: WITH a AS (...), a AS (...) SELECT ... — any duplicate CTE name within a single WITH clause.

Common situations: Copy-pasting CTEs and forgetting to rename; programmatic SQL generation emitting the same alias twice; merging queries by concatenating WITH lists.

Related errors

• INVALID_TABLE_PROPERTY
• MISSING_ATTRIBUTE
• AMBIGUOUS_ATTRIBUTE
• NOT_SUPPORTED
• SYNTAX_ERROR

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