[
https://issues.apache.org/jira/browse/CALCITE-7638?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7638:
------------------------------------
Labels: pull-request-available (was: )
> 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
> Priority: Major
> Labels: pull-request-available
> 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)
