fix: apply a pulled up HAVING in scalar subqueries whose aggregate is NULL for empty input - #26608

Which issue does this PR close?

• Closes Correlated scalar subquery ignores HAVING that is true for empty input #26505.

Rationale for this change

A correlated scalar subquery without GROUP BY ignores its HAVING when the aggregate is NULL for an empty input (sum, max, min) and the HAVING is true for an empty input:

SELECT x.a,
       (SELECT sum(y.b) FROM (VALUES (1, 10)) AS y(a, b) WHERE y.a = x.a HAVING sum(y.b) IS NULL) AS v
FROM (VALUES (1), (2)) AS x(a)

For a = 1 this returns 10 instead of NULL.

When the correlated filter is pulled up, such a HAVING is removed from the subquery and applied again in the count bug CASE that build_join adds over the join. But build_join skips that CASE for aggregates that are NULL for an empty input, so the HAVING was never applied.

What changes are included in this PR?

build_join only skips the CASE for those aggregates when there is no pulled up HAVING.

The HAVING reads the aggregates by their unqualified names, so it is now qualified with the subquery alias. Otherwise two such subqueries in the same query, or an outer column with the same name, fail with an ambiguous reference.

What is the testing strategy for this PR?

New sqllogictest cases in subquery.slt with sum, max, HAVING count(*) = 0, a HAVING that keeps some matched rows and two subqueries with the same aggregate. The expected results match SQLite.

Are there any user-facing changes?

No, only the wrong results are fixed.