prestodb/presto · error · SemanticException

AMBIGUOUS_ATTRIBUTE

AMBIGUOUS_ATTRIBUTE

Error message

'%s' in ORDER BY is ambiguous

What it means

Presto throws this during ORDER BY analysis when an output alias in the ORDER BY clause maps to more than one distinct expression in the SELECT list. Because the analyzer resolves ORDER BY ordinals/names against select assignments, a name with multiple candidate expressions is ambiguous and cannot be resolved. This is a semantic validation error raised by StatementAnalyzer's Visitor.

Source

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

        {
            private final Multimap<QualifiedName, Expression> assignments;

            public OrderByExpressionRewriter(Multimap<QualifiedName, Expression> assignments)
            {
                this.assignments = assignments;
            }

            @Override
            public Expression rewriteIdentifier(Identifier reference, Void context, ExpressionTreeRewriter<Void> treeRewriter)
            {
                // if this is a simple name reference, try to resolve against output columns
                QualifiedName name = QualifiedName.of(reference.getValue());
                Set<Expression> expressions = assignments.get(name)
                        .stream()
                        .collect(Collectors.toSet());

                if (expressions.size() > 1) {
                    throw new SemanticException(AMBIGUOUS_ATTRIBUTE, reference, "'%s' in ORDER BY is ambiguous", name);
                }

                if (expressions.size() == 1) {
                    return expressions.stream().collect(onlyElement());
                }

                // otherwise, couldn't resolve name against output aliases, so fall through...
                return reference;
            }
        }

        private void checkGroupingSetsCount(GroupBy node)
        {
            // If groupBy is distinct then crossProduct will be overestimated if there are duplicate grouping sets.
            int crossProduct = 1;
            for (GroupingElement element : node.getGroupingElements()) {
                try {
                    int product;

View on GitHub (pinned to 55bb57d202)

Solutions

1. Rename one of the duplicate SELECT aliases so each ORDER BY name maps to a single expression.
2. Replace the ambiguous name in ORDER BY with an explicit ordinal (ORDER BY 2) or a fully qualified column reference (t.col).
3. Order by an expression directly (ORDER BY a + b) instead of the shared alias.

Example fix

// before
SELECT price AS v, discount AS v FROM items ORDER BY v;
// after
SELECT price AS price_v, discount AS discount_v FROM items ORDER BY price_v;

// Parse the SELECT list and ORDER BY before running; ensure every ORDER BY name maps to exactly one select item.
Set<String> aliases = selectItems.stream().map(this::outputName).collect(toSet());
for (String obName : orderByNames) {
    if (Collections.frequency(outputNames, obName) > 1) {
        throw new IllegalArgumentException("Ambiguous ORDER BY name: " + obName);
    }
}

try { queryRunner.execute(sql); } catch (SemanticException e) { if (e.getCode() == SemanticErrorCode.AMBIGUOUS_ATTRIBUTE) { /* rewrite ORDER BY with explicit column or ordinal */ } else { throw e; } }

When it happens

Trigger: An ORDER BY references a column name or alias that appears multiple times in the SELECT list with different underlying expressions, e.g. SELECT a, b AS x, c AS x FROM t ORDER BY x.

Common situations: Queries built by ORMs or query builders that duplicate output column names; hand-written SQL where two select items share the same alias; joins where an unqualified name matches select items from multiple tables after aliasing.

Related errors

• MUST_BE_AGGREGATE_OR_GROUP_BY
• NESTED_AGGREGATION
• NESTED_WINDOW
• ORDER_BY_MUST_BE_IN_AGGREGATE
• MUST_BE_AGGREGATION_FUNCTION

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