[CALCITE-6904] IS_NOT_DISTINCT_FROM is incorrectly handled by EnumerableJoinRule - ASF JIRA

XML

Word

Printable

JSON

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 1.40.0
Component/s: None
Labels:
- pull-request-available

Description

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:

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

calcite jdbc case:

  @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");
  }