[
https://issues.apache.org/jira/browse/CALCITE-6741?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17907251#comment-17907251
]
xiong duan commented on CALCITE-6741:
-------------------------------------
When we convert IN to OR condition, it is determined by the position of the
current condition. If it is in the project, we consider it unknown as unknown,
we can get NULL value, and if it is in the filter, we consider unknown as false.
> The type of a comparison is nullable when either operand is nullable
> --------------------------------------------------------------------
>
> Key: CALCITE-6741
> URL: https://issues.apache.org/jira/browse/CALCITE-6741
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.38.0
> Reporter: Mihai Budiu
> Assignee: Mihai Budiu
> Priority: Minor
> Labels: pull-request-available
>
> This function converts an expression into another expression which does not
> have the same type.
> The function convertInToOr has the following JavaDoc:
> {code:java}
> /* Converts "x IN (1, 2, ...)" to "x=1 OR x=2 OR ..." */
> {code}
> This is unsound when x is not nullable and the list contains NULL.
> The "in" expression can never evaluate to NULL in that case, whereas the
> converted expression can.
> As an example, consider this test case, which is a simplified version of a
> test from Sql Logic Test:
> {code:sql}
> SELECT CASE WHEN 1 NOT IN ( NULL, COUNT(*) ) THEN 1 END
> {code}
> The plan produced for this query is as follows:
> {code}
> LogicalProject(EXPR$0=[CASE(CAST(AND(null, <>(1, $0))):BOOLEAN NOT NULL,
> 1, null:INTEGER)]), id = 202
> LogicalAggregate(group=[{}], agg#0=[COUNT()]), id = 200
> LogicalValues(tuples=[[{ 0 }]]), id = 160
> {code}
> Notice that there's a CAST(AND ...): BOOLEAN NOT NULL. Unfortunately the AND
> expression as written is nullable, so this cast is unsound.
> I am thinking that the correct expansion should use IS_NOT_DISTINCT_FROM
> instead of EQUALS.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
