ClickHouse DB::Exception: Incorrect result of scalar subquery

The "DB::Exception: Incorrect result of scalar subquery" error in ClickHouse occurs when a subquery used in a scalar context (where exactly one row and one column is expected) returns more than one row. The error code is INCORRECT_RESULT_OF_SCALAR_SUBQUERY. A scalar subquery must produce exactly one value, and this error means that contract was violated. (A subquery that returns zero rows is handled separately: ClickHouse returns NULL for a Nullable-capable type, or raises a distinct "Scalar subquery returned empty result" error for types that cannot be Nullable, such as Array or AggregateFunction.)

Impact

The query containing the scalar subquery fails entirely. This is a query-time error that prevents any results from being returned. It does not affect stored data but will block any pipeline or application logic that depends on the query.

Common Causes

1. Subquery returns multiple rows -- The most common cause. A subquery intended to return one value actually matches multiple rows because the filter condition is not restrictive enough.
2. Subquery returns zero rows -- A scalar subquery that returns no rows does not raise this specific error code, but it produces a related failure ("Scalar subquery returned empty result") for non-Nullable types, or yields NULL otherwise. Either outcome usually signals the same underlying problem: the filter matched nothing.
3. Missing aggregation -- The subquery selects a raw column without using an aggregate function (like max(), min(), or any()), so it returns all matching rows instead of one.
4. Missing or incorrect WHERE clause -- The filter in the subquery is not specific enough to guarantee a single result.
5. Data changes between query design and execution -- The subquery was written when the data guaranteed one result, but new data has since been added that produces multiple results.

Troubleshooting and Resolution Steps

1. Run the subquery independently to see how many rows it returns:

   -- If your query contains: WHERE x = (SELECT val FROM other_table WHERE ...)
   -- Run the subquery alone:
   SELECT val FROM other_table WHERE ...;
   

2. Add an aggregate function to guarantee a single result:

   -- Instead of:
   SELECT * FROM my_table WHERE id = (SELECT id FROM lookup WHERE key = 'abc');
   
   -- Use:
   SELECT * FROM my_table WHERE id = (SELECT max(id) FROM lookup WHERE key = 'abc');
   

3. Add LIMIT 1 if any matching row is acceptable:

   SELECT * FROM my_table
   WHERE id = (SELECT id FROM lookup WHERE key = 'abc' LIMIT 1);
   

4. Use IN instead of = if multiple matches are valid:

   -- Instead of scalar subquery:
   SELECT * FROM my_table WHERE id = (SELECT id FROM lookup WHERE category = 'A');
   
   -- Use IN for multiple values:
   SELECT * FROM my_table WHERE id IN (SELECT id FROM lookup WHERE category = 'A');
   

5. Tighten the WHERE clause in the subquery to ensure it filters to exactly one row:

   SELECT * FROM my_table
   WHERE id = (SELECT id FROM lookup WHERE key = 'abc' AND version = 2);
   

6. Use any() for non-deterministic single-value selection:

   SELECT * FROM my_table
   WHERE id = (SELECT any(id) FROM lookup WHERE key = 'abc');
   

Best Practices

• Always verify that scalar subqueries return exactly one row by testing them independently before embedding them in larger queries.
• Use aggregate functions (max, min, any, count) in scalar subqueries as a safety net, even when you expect only one row.
• Prefer IN (SELECT ...) over = (SELECT ...) when the subquery might legitimately return multiple values.
• Add appropriate UNIQUE constraints or primary key lookups to ensure subqueries against lookup tables return single values.
• In application code, consider running the subquery first, validating the result count, and then using the value in the main query.

Frequently Asked Questions

Q: Can I make ClickHouse just pick the first row if a scalar subquery returns multiple rows?
A: ClickHouse does not do this automatically. You need to explicitly add LIMIT 1 or wrap the subquery in an aggregate function like any() or min() to ensure a single value is returned.

Q: What happens if the scalar subquery returns zero rows?
A: This does not raise INCORRECT_RESULT_OF_SCALAR_SUBQUERY (which is specific to multiple rows). For a Nullable-capable type, ClickHouse returns NULL. For a type that cannot be Nullable (such as Array or AggregateFunction), it raises a separate "Scalar subquery returned empty result" error. To be safe, use COALESCE or ifNull to provide a default value, or restructure the subquery so it always returns a row.

Q: Is this error specific to WHERE clauses?
A: No. Scalar subqueries can appear in SELECT lists, WHERE clauses, HAVING clauses, and even in SET statements. The error applies anywhere a single value is expected from a subquery.