Zhen Chen created CALCITE-7638:
----------------------------------
Summary: SetOpToFilterRule MINUS drops rows when right-side
filters evaluate to UNKNOWN
Key: CALCITE-7638
URL: https://issues.apache.org/jira/browse/CALCITE-7638
Project: Calcite
Issue Type: Bug
Components: core
Affects Versions: 1.42.0
Reporter: Zhen Chen
Assignee: Zhen Chen
Fix For: 1.43.0
SetOpToFilterRule rewrites MINUS/EXCEPT over the same source by keeping the
left-side condition and negating right-side filter conditions.
For nullable columns, this is not equivalent to SQL EXCEPT semantics. A
right-side filter only contributes rows when its condition evaluates to TRUE.
If the right-side condition evaluates to UNKNOWN, the row is not present in the
right input and should therefore remain in the MINUS result.
Example:
{code:java}
SELECT mgr, comm FROM empnullables WHERE mgr = 12
EXCEPT
SELECT mgr, comm FROM empnullables WHERE comm = 5 {code}
The current rewrite can produce:
{code:java}
WHERE mgr = 12 AND NOT(comm = 5) {code}
For rows where comm is NULL, comm = 5 evaluates to UNKNOWN, and NOT(UNKNOWN) is
still UNKNOWN. The WHERE clause filters the row out, even though that row is
not present in the right input.
The correct rewrite is:
{code:java}
WHERE mgr = 12 AND (comm = 5) IS NOT TRUE {code}
This keeps rows where the right-side condition is FALSE or UNKNOWN, matching
EXCEPT semantics.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
