yujun777 opened a new pull request, #62299:
URL: https://github.com/apache/doris/pull/62299
### What problem does this PR solve?
Issue Number: close #xxx
Problem Summary:
`PullUpPredicates.getFiltersFromUnionConstExprs()` incorrectly removes
`NullLiteral` from UNION ALL constant expressions without compensating with `OR
IS NULL`, producing invalid pull-up predicates.
For example, `SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT NULL` generates
the pull-up predicate `n IN (1, 3)`, which is NOT true for the NULL row (`NULL
IN (1, 3)` evaluates to NULL, not TRUE).
When `InferPredicates` pushes this predicate from one INTERSECT child to
another, the IN filter incorrectly eliminates NULL rows. Since INTERSECT treats
NULL=NULL per SQL standard, this causes incorrect query results with missing
NULLs.
**Root Cause**: In `getFiltersFromUnionConstExprs()`, line 467:
```java
options.removeIf(option -> option instanceof NullLiteral);
```
removes NULL but does not add `OR IS NULL` to compensate.
**Fix**: When NULL constants are present, generate `n IN (...) OR n IS NULL`
instead of just `n IN (...)`, making the predicate valid for all rows including
NULL.
**Reproducing Query**:
```sql
WITH
tbl0(n) AS (SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT NULL),
tbl1(n) AS (SELECT 2 UNION ALL SELECT NULL UNION ALL SELECT 1)
(
SELECT (n * 2) AS n FROM tbl0
INTERSECT
SELECT (n * 2) AS n FROM tbl1
);
-- Expected: {2, NULL} Actual before fix: {2}
```
### Release note
Fixed a bug where INTERSECT and EXCEPT queries could incorrectly drop NULL
rows when the optimizer inferred and pushed down predicates from UNION ALL
constant expressions.
### Check List (For Author)
- Test: Regression test
- Behavior changed: No
- Does this need documentation: No
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
