You are viewing a plain text version of this content. The canonical link for it is here.

Posted to commits@cloudberry.apache.org by "Alena0704 (via GitHub)" <gi...@apache.org> on 2026/10/14 09:39:34 UTC

[PR] ORCA: preserve empty-input aggregate values and rows in NULL-accepting subquery filters [cloudberry]

Alena0704 opened a new pull request, #2127:
URL: https://github.com/apache/cloudberry/pull/2127

   ### What does this PR do?
   
   Fix incorrect results when ORCA unnests correlated aggregate subqueries or scalar subqueries in filters that can accept NULL. Preserve the aggregate result on empty input and keep outer rows that can satisfy the original predicate.
   
   **ORCA: preserve the empty-input COUNT result in a correlated IN subquery**
   
   With ORCA (`optimizer = on`), the following query incorrectly returns no rows before the fix. It must return the row `(0, 1)`:
   
   <details>
   <summary>Reproduction: correlated <code>IN</code> over <code>count(*)</code></summary>
   
   ```sql
   CREATE TABLE cb (a int, b int) DISTRIBUTED BY (a);
   INSERT INTO cb VALUES (0, 1);
   SET optimizer = on;
   
   select * from cb x where x.a in (select count(*) from cb y where y.b = x.a);
   ```
   
   Before the fix, with ORCA:
   
   ```text
    a | b
   ---+---
   (0 rows)
   ```
   
   After the fix, with ORCA:
   
   ```text
    a | b
   ---+---
    0 | 1
   (1 row)
   ```
   
   With `optimizer = off`, the same query already returns `(0, 1)` before the ORCA fix.
   
   </details>
   
   For the outer row, `x.a = 0`. The only inner row has `y.b = 1`, so no row satisfies `y.b = x.a`. The aggregate subquery still returns one row containing `0`, and the condition is `0 IN (0)`, which is true. The input to this particular aggregate is empty even though `cb` itself is not empty.
   
   The incorrect plan groups the inner scan by `y.b` and joins it to the outer scan. There is a group for `y.b = 1`, but none for `y.b = 0`. The inner join therefore discards the outer row before it can be compared with the count of zero.
   
   The fix uses a left outer apply with a match flag. After decorrelation, the left join keeps the outer row, and `CASE` supplies `0` when there is no matching group. The comparison above the join then evaluates to `x.a = 0`, preserving `(0, 1)`.
   
   <details>
   <summary>EXPLAIN (COSTS OFF) for the same query, before and after the fix</summary>
   
   ```sql
   SET optimizer = on;
   EXPLAIN (COSTS OFF)
   select * from cb x where x.a in (select count(*) from cb y where y.b = x.a);
   ```
   
   Before the fix:
   
   ```text
   Gather Motion 3:1  (slice1; segments: 3)
     ->  Hash Join
           Hash Cond: ((y.b = x.a) AND ((count(*)) = (x.a)::bigint))
           ->  GroupAggregate
                 Group Key: y.b
                 ->  Sort
                       Sort Key: y.b
                       ->  Redistribute Motion 3:3  (slice2; segments: 3)
                             Hash Key: y.b
                             ->  Seq Scan on cb y
           ->  Hash
                 ->  Seq Scan on cb x
   Optimizer: GPORCA
   ```
   
   After the fix:
   
   ```text
   Gather Motion 3:1  (slice1; segments: 3)
     ->  Result
           Filter: (x.a = CASE WHEN (true) THEN (count(*)) ELSE '0'::bigint END)
           ->  Hash Left Join
                 Hash Cond: (x.a = y.b)
                 ->  Seq Scan on cb x
                 ->  Hash
                       ->  GroupAggregate
                             Group Key: y.b
                             ->  Sort
                                   Sort Key: y.b
                                   ->  Redistribute Motion 3:3  (slice2; segments: 3)
                                         Hash Key: y.b
                                         ->  Seq Scan on cb y
   Optimizer: GPORCA
   ```
   </details>
   
   In the corrected plan, `(true)` is the marker projected by the inner side. For the missing `y.b = 0` group, the left join supplies `NULL` for that marker, so `CASE` takes the `ELSE 0` branch. The `Result` filter compares `x.a` with this corrected value.
   
   **Preserve rows in NULL-accepting scalar-subquery filters**
   
   GPORCA could also discard outer rows when a scalar-subquery filter accepts
   NULL, such as `IS NULL` or `COALESCE(..., 0) = 0`. An inner apply removes
   unmatched rows before the predicate can evaluate to true.
   
   <details>
   <summary>For example:</summary>
   
   ```sql
   CREATE TABLE outer_t(a int, b int, d int) DISTRIBUTED BY (a);
   INSERT INTO outer_t VALUES
       (3,1,1), (1,9,5), (0,2,7), (5,5,1), (2,4,9);
   
   CREATE TABLE inner_t(a int, b int) DISTRIBUTED BY (a);
   INSERT INTO inner_t VALUES (1,10), (2,20), (1,30);
   ANALYZE outer_t;
   ANALYZE inner_t;
   
   SET optimizer = on;
   
   SELECT a, d FROM outer_t o
   WHERE COALESCE(
       (SELECT sum(i.b) FROM inner_t i WHERE i.a = o.d), 0
   ) = 0
   ORDER BY 1, 2;
   
   SELECT a, d FROM outer_t o
   WHERE (SELECT i.b FROM inner_t i
          WHERE i.a = o.d AND i.b = 10) IS NULL
   ORDER BY 1, 2;
   ```
   
   Before this fix, both queries returned no rows. They now return the expected
   three rows:
   
   ```text
    a | d
   ---+---
    0 | 7
    1 | 5
    2 | 9
   ```
   </details>
   
   Keep the filter context when the predicate rejects NULL from each scalar subquery individually, or when the subquery is known to return a row. Otherwise, use the value context to preserve unmatched outer rows through an outer apply.
   
   Check each subquery separately, leaving the other subqueries unknown: `COALESCE(sq1, sq2) > 0` can be true when either subquery is NULL, even though it is NULL when both are NULL.
   
   Recognize singleton constant subqueries, possibly under projections without set-returning functions, as nonempty. This preserves the existing SubPlan for `(SELECT 1)` inside `LEAST()` and `GREATEST()`, avoiding an unnecessary outer join. A potentially empty subquery such as `(SELECT 1 WHERE false)` still receives the NULL-preserving treatment.
   
   Skip result-type lookup for structural scalar nodes such as array index lists and CASE arms, which do not have a result type of their own.
   
   **Preserve expression evaluation and aggregate semantics**
   
   Keep aggregate values and the match flag below the join, and evaluate expressions around aggregates in the appropriate `CASE` branch above it. This avoids division-by-zero errors for unused inner groups while preserving NULL results and errors required by the original query.
   
   Preserve decorrelation through `HAVING` by evaluating its condition above the join using matched or empty-input aggregate values. A rejected row produces NULL for a scalar subquery and FALSE for `IN`, including when the left operand is NULL. Evaluate the target expression only when `HAVING` accepts the row.
   
   Expose the aggregate columns and match flag needed when non-equality correlations require correlated execution. Retain correlated execution for nested aggregates and unsafe user-defined final functions.
   
   Introduce shared helpers for catalog-based empty-input constants, NULL-result properties, and final-function safety checks. These helpers do not change PostgreSQL planner subquery pull-up behavior in this PR.
   
   Recognize the NULL empty-input results of built-in numeric and int8 `AVG`/`SUM`, retaining inner joins where the comparison rejects those results. For built-in hypothetical `rank()` with constant direct arguments, use the empty-input value `1` instead of an extra empty aggregate and its join. Require constant direct arguments so this optimization does not suppress their evaluation or errors.
   
   Verified the #2046 reproducer and the added regression queries, including
   EXPLAIN output, on a three-segment cluster.
   
   Related issue: [#2046](https://github.com/apache/cloudberry/issues/2046).
   
   
   ### Type of Change
   
   - [x] Bug fix (non-breaking change)
   - [ ] New feature (non-breaking change)
   - [ ] Breaking change (fix or feature with breaking changes)
   - [ ] Documentation update
   
   ### Test Plan
   
   - [x] Added/updated SQL regression tests and expected output.
   - [x] Built the updated server and ORCA code.
   - [x] Ran the dedicated `orca_empty_input` and `orca_filter_subqueries` regression tests.
   - [x] Verified the #2046 reproducer and EXPLAIN output on a three-segment cluster.
   - [ ] Passed `make installcheck`.
   - [ ] Passed `make -C src/test installcheck-cbdb-parallel`.
   
   The dedicated tests cover missing and matching groups, all-NULL groups, multiple aggregates, scalar and `IN`/`NOT IN` subqueries, `OR`, `HAVING`, non-equality correlations, unsafe final functions, and expression evaluation errors. Filter tests cover `IS NULL`, `COALESCE` over two correlated sums, subqueries in array indexes and CASE arms, and singleton or empty subqueries inside `LEAST()` and `GREATEST()`.
   
   Refresh affected ORCA expected plans, including the PAX expectation for `HAVING`. PAX storage and the full regression suites were not run locally for this ORCA-only branch.
   
   ### Impact
   
   Correlated aggregate subqueries preserve their empty-input values when no inner group matches. Scalar-subquery filters such as `IS NULL` and `COALESCE(..., 0) = 0` retain outer rows that satisfy the predicate.
   
   Plans use a left join or outer apply when needed to preserve those results. Comparisons that reject empty-input values retain inner joins where safe, and recognized singleton constant subqueries retain their existing filter context. Unsupported or unsafe cases retain correlated execution. No new dependencies are introduced.
   
   ### Additional Context
   
   A subset of the aggregate regression tests is retained from [Greengage commit 2ef1914](https://github.com/GreengageDB/greengage/commit/2ef1914b4df1418befff7443ea5c2b22be74a726). The implementation of the fix is different.
   
   ### Checklist
   
   - [x] Followed [contribution guide](https://cloudberry.apache.org/contribute/code)
   - [ ] Added/updated documentation
   - [ ] Reviewed code for security implications
   - [ ] This PR contains AI-assisted code generation
   - [x] Requested review from [cloudberry committers](https://github.com/orgs/apache/teams/cloudberry-committers)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org