[
https://issues.apache.org/jira/browse/CALCITE-6904?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17938322#comment-17938322
]
Julian Hyde commented on CALCITE-6904:
--------------------------------------
Regarding the "{{{}collapseExpandedIsNotDistinctFromExpr{}}}" method (which was
added in CALCITE-1200 and extended in CALCITE-6776).
I have encountered problems where {{x IS NOT DISTINCT FROM y}} is expanded to
{{{}x = y OR (x IS NULL AND y IS NULL){}}}, then {{x IS NULL}} is pushed down,
and the expression becomes {{{}x = y OR (b AND y IS NULL){}}}, and then cannot
be converted back. Seems like madness.
Maybe we should revisit the strategy of expanding {{IS NOT DISTINCT FROM}}
early during planning, and then recreating it afterwards. What we are doing now
is creating mayhem.
> IS_NOT_DISTINCT_FROM is converted error in EnumerableJoinRule
> -------------------------------------------------------------
>
> Key: CALCITE-6904
> URL: https://issues.apache.org/jira/browse/CALCITE-6904
> Project: Calcite
> Issue Type: Bug
> Reporter: Zhen Chen
> Assignee: Zhen Chen
> Priority: Major
> Labels: pull-request-available
> Attachments: image-2025-03-21-13-06-47-354.png
>
>
> The convert() method of EnumerableJoinRule incorrectly converts the condition
> of IS_NOT_DISTINCT_FROM , which is considered an equivalence condition in
> calcite and carries a `filterNulls` to save whether to perform equivalence
> processing on NULL. However, when converting to EnumerableJoin, the
> filterNulls are ignored, resulting in `IS_NOT_DISTINCT_FROM ($0, $1)` being
> converted to `=($0, $1)`.
>
> mysql case:
> {code:java}
> CREATE TABLE emps (
> commission int
> );
> INSERT INTO emps VALUES (1);
> INSERT INTO emps VALUES (2);
> INSERT INTO emps VALUES (3);
> INSERT INTO emps VALUES (NULL);
> SELECT t1.commission FROM emps as t1
> join
> emps as t2
> on t1.commission <=> t2.commission;
> mysql result:
> commission
> 1
> 2
> 3
> NULL {code}
> calcite jdbc case:
> {code:java}
> @Test void testIsNotDistinctFrom1() {
> final String sql = ""
> + "select \"t1\".\"commission\" from \"hr\".\"emps\" as \"t1\"\n"
> + "join\n"
> + "\"hr\".\"emps\" as \"t2\"\n"
> + "on \"t1\".\"commission\" is not distinct from
> \"t2\".\"commission\"";
> CalciteAssert.hr()
> .query(sql)
> .explainContains("")
> .returnsUnordered("commission=500",
> "commission=null");
> } {code}
> debug result:
> !image-2025-03-21-13-06-47-354.png|width=519,height=649!
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
