You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Zhen Chen (Jira)" <ji...@apache.org> on 2026/07/03 13:45:00 UTC

[jira] [Created] (CALCITE-7638) SetOpToFilterRule MINUS drops rows when right-side filters evaluate to UNKNOWN

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)