What causes "Scalar sub-query has returned multiple rows" in prestodb/presto?

Add LIMIT 1 to the scalar subquery (with an ORDER BY if a specific row is needed)

prestodb/presto · error · PrestoException

SUBQUERY_MULTIPLE_ROWS

SUBQUERY_MULTIPLE_ROWS

Error message

Scalar sub-query has returned multiple rows

What it means

A scalar subquery must return at most one row because its result is used as a single value. EnforceSingleRowOperator.addInput throws SUBQUERY_MULTIPLE_ROWS when it receives a page with more than one position, or a second non-empty page after already storing one. This is a runtime semantic constraint of scalar subqueries in Presto.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/operator/EnforceSingleRowOperator.java:111

        return finishing && page == null;
    }

    @Override
    public boolean needsInput()
    {
        return !finishing;
    }

    @Override
    public void addInput(Page page)
    {
        requireNonNull(page, "page is null");
        checkState(needsInput(), "Operator did not expect any more data");
        if (page.getPositionCount() == 0) {
            return;
        }
        if (this.page != null || page.getPositionCount() > 1) {
            throw new PrestoException(SUBQUERY_MULTIPLE_ROWS, "Scalar sub-query has returned multiple rows");
        }
        this.page = page;
    }

    @Override
    public Page getOutput()
    {
        if (!finishing) {
            return null;
        }
        checkState(page != null, "Operator is already done");

        Page pageToReturn = page;
        page = null;
        return pageToReturn;
    }
}

View on GitHub (pinned to 55bb57d202)

Solutions

Add LIMIT 1 to the scalar subquery (with an ORDER BY if a specific row is needed)
Rewrite as a join with aggregation (MAX/MIN) or GROUP BY to guarantee one row per key
Fix the data/unique constraint that allowed multiple matching rows

Example fix

// before
SELECT x, (SELECT y FROM t WHERE t.k = x) FROM s;
// after
SELECT x, (SELECT y FROM t WHERE t.k = x LIMIT 1) FROM s;

Defensive patterns

Strategy: validation

Validate before calling

// rewrite query or pre-verify uniqueness in your data pipeline
-- check before relying on scalar subquery
SELECT k, count(*) FROM t GROUP BY k HAVING count(*) > 1;

Try / catch

try { query(sql); } catch (PrestoException e) {
    if (e.getErrorCode().getName().equals("SUBQUERY_MULTIPLE_ROWS")) {
        query(sqlWithLimit1OrAggregation); // retry with corrected query
    } else { throw e; }
}

Prevention

Always pair scalar subqueries with a uniqueness guarantee (key constraint, LIMIT 1, or aggregation)
Prefer explicit joins with GROUP BY/MAX over scalar subqueries for non-unique relations
Add data-quality checks on join keys used in scalar subqueries

When it happens

Trigger: addInput is called with page.getPositionCount() > 1, or a second non-empty page arrives while this.page is already set.

Common situations: Queries like SELECT x, (SELECT y FROM t WHERE t.k = x) ... where the subquery's predicate matches multiple rows of t; often after data changes that make a previously-unique join key non-unique.

Related errors

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