{"record":{"id":"2f8f767628fa3581","repo":"prestodb/presto","slug":"subquery-multiple-rows","errorCode":"SUBQUERY_MULTIPLE_ROWS","errorMessage":"Scalar sub-query has returned multiple rows","messagePattern":"Scalar sub-query has returned multiple rows","errorType":"error_code","errorClass":"PrestoException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/operator/EnforceSingleRowOperator.java","lineNumber":111,"sourceCode":"        return finishing && page == null;\n    }\n\n    @Override\n    public boolean needsInput()\n    {\n        return !finishing;\n    }\n\n    @Override\n    public void addInput(Page page)\n    {\n        requireNonNull(page, \"page is null\");\n        checkState(needsInput(), \"Operator did not expect any more data\");\n        if (page.getPositionCount() == 0) {\n            return;\n        }\n        if (this.page != null || page.getPositionCount() > 1) {\n            throw new PrestoException(SUBQUERY_MULTIPLE_ROWS, \"Scalar sub-query has returned multiple rows\");\n        }\n        this.page = page;\n    }\n\n    @Override\n    public Page getOutput()\n    {\n        if (!finishing) {\n            return null;\n        }\n        checkState(page != null, \"Operator is already done\");\n\n        Page pageToReturn = page;\n        page = null;\n        return pageToReturn;\n    }\n}\n","sourceCodeStart":93,"sourceCodeEnd":129,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/operator/EnforceSingleRowOperator.java#L93-L129","documentation":"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.","triggerScenarios":"addInput is called with page.getPositionCount() > 1, or a second non-empty page arrives while this.page is already set.","commonSituations":"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.","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"],"exampleFix":"// before\nSELECT x, (SELECT y FROM t WHERE t.k = x) FROM s;\n// after\nSELECT x, (SELECT y FROM t WHERE t.k = x LIMIT 1) FROM s;","handlingStrategy":"validation","validationCode":"// rewrite query or pre-verify uniqueness in your data pipeline\n-- check before relying on scalar subquery\nSELECT k, count(*) FROM t GROUP BY k HAVING count(*) > 1;","typeGuard":null,"tryCatchPattern":"try { query(sql); } catch (PrestoException e) {\n    if (e.getErrorCode().getName().equals(\"SUBQUERY_MULTIPLE_ROWS\")) {\n        query(sqlWithLimit1OrAggregation); // retry with corrected query\n    } else { throw e; }\n}","preventionTips":["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"],"tags":["subquery","sql","runtime"],"backgroundTag":"scalar-subquery-multiple-rows","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"}