[CALCITE-1422] In JDBC adapter, allow IS NULL and IS NOT NULL operators in generated SQL join condition

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.11.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: Viktor Batytskyi
• Created: 07/Oct/16 13:18
• Updated: 06/Jan/17 19:30
• Resolved: 19/Oct/16 04:28

Description

Currently, Calcite doesn't support IS NULL and IS NOT NULL operators in ON condition clause of a JOIN operator. These operators may be useful if a query needs to join on both keys being null.

Test which proves the failure of (RelToSqlConverterTest.java):

  @Test
  public void testSimpleJoinConditionWithIsNullOperators() {
    String query = "select *\n"
            + "from \"foodmart\".\"sales_fact_1997\" as \"t1\"\n"
            + "inner join \"foodmart\".\"customer\" as \"t2\"\n"
            + "on \"t1\".\"customer_id\" = \"t2\".\"customer_id\" or "
            + "(\"t1\".\"customer_id\" is null "
            + "and \"t2\".\"customer_id\" is null)\n"
            + "inner join \"foodmart\".\"product\" as \"t3\"\n"
            + "on \"t1\".\"product_id\" = \"t3\".\"product_id\" or "
            + "(\"t1\".\"product_id\" is not null or " +
            "\"t3\".\"product_id\" is not null)";
      System.out.println(query);
    String expected = "SELECT *\nFROM \"foodmart\".\"sales_fact_1997\"\n"
        + "INNER JOIN \"foodmart\".\"customer\" "
        + "ON \"sales_fact_1997\".\"customer_id\" = \"customer\".\"customer_id\""
        + " OR \"sales_fact_1997\".\"customer_id\" IS NULL "
        + "AND \"customer\".\"customer_id\" IS NULL\n"
        + "INNER JOIN \"foodmart\".\"product\" "
        + "ON \"sales_fact_1997\".\"product_id\" = \"product\".\"product_id\" OR "
        + "\"sales_fact_1997\".\"product_id\" IS NOT NULL "
        + "OR \"product\".\"product_id\" IS NOT NULL";
    sql(query).ok(expected);
  }