unnest_subqueries produces wrong results for COALESCE(SUM(...), k) and for nullable CASE expressions containing COUNT

Open
  • Is something wrong?
#8,752 2 comments 0 reactions 1 assignee View on GitHub

Maintainers usually reply within 1 day

@treysp is already working on this.

Since Oct 23, 2026.

Assessment

This issue has not been assessed yet.

Description

Description

sqlglot/optimizer/unnest_subqueries.py:366-375 compensates for empty groups only when the scalar subquery
contains exp.Count:

if find_in_scope(value, exp.Count):
    def remove_aggs(node):
        if isinstance(node, exp.Count):
            return exp.Literal.number(0)
        elif isinstance(node, exp.AggFunc):
            return exp.null()
        return node
    alias = exp.Coalesce(this=alias, expressions=[value.this.transform(remove_aggs)])

This is wrong in both directions:

(a) Missing compensation for non-COUNT aggregates that are non-NULL on empty input

q = "SELECT a.k, (SELECT COALESCE(SUM(b.v), 0) FROM b WHERE b.k = a.k) AS s FROM a"
print(optimize(sqlglot.parse_one(q),
               schema={"a": {"k": "INT"}, "b": {"k": "INT", "v": "INT"}}).sql())
# WITH "_u_0" AS (SELECT COALESCE(SUM("b"."v"), 0) AS "_col_0", "b"."k" AS "_u_1"
#                 FROM "b" GROUP BY "b"."k")
# SELECT "a"."k", "_u_0"."_col_0" FROM "a"
# LEFT JOIN "_u_0" ON "_u_0"."_u_1" = "a"."k"

The COALESCE stays inside the grouped subquery, so an unmatched outer row gets NULL instead of 0.

(b) Over-compensation that overwrites a legitimate matched NULL

q = ("SELECT a.k, (SELECT CASE WHEN COUNT(*) >= 1 THEN SUM(b.v) ELSE 99 END "
     "FROM b WHERE b.k = a.k) AS val FROM a")
print(optimize(sqlglot.parse_one(q),
               schema={"a": {"k": "INT"}, "b": {"k": "INT", "v": "INT"}}).sql())
# ... COALESCE("_u_0"."_col_0", 99) AS "val" ...

When a.k = 1 matches one row (k=1, v=NULL), the subquery must return NULL (COUNT(*) >= 1 is true,
SUM(NULL) is NULL), but the added outer COALESCE(_u_0._col_0, 99) turns it into 99.

Expected vs Actual
  • (a) a={(1),(2)}, b={(1,10)}: expected [(1,10),(2,0)]; actual [(1,10),(2,None)].
  • (b) a={(1)}, b={(1,None)}: expected [(1,None)]; actual [(1,99)].
Root cause

The empty-group compensation is computed from the syntactic presence of COUNT rather than from
"what does this scalar subquery evaluate to on zero tuples". A single probe is insufficient: the compensation
must be a per-expression default computed structurally (as Spark's evalSubqueryOnZeroTups does), and the outer
COALESCE must be applied only when the expression's empty-input value is non-NULL.

Suggested fix

Compute the zero-tuple value of the projection: replace every aggregate by its empty-input default and fold the
surrounding expression. If the result is non-NULL, wrap the joined column with COALESCE(col, default);
otherwise leave it untouched. Do not blanket-wrap any expression that merely contains a COUNT.

Dominant language
Python
Stars
9.7k
Forks
1.4k
Avg merge
13h 20m
Merged PRs (30d)
217

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from tobymao/sqlglot

All issues in tobymao/sqlglot

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.