[jira] [Created] (CALCITE-1422) Add support of IS NULL and IS NOT NULL operators to a join condition clause

["Viktor Batytskyi (JIRA)" <ji...@apache.org>]

Viktor Batytskyi created CALCITE-1422:
-----------------------------------------

             Summary: Add support of IS NULL and IS NOT NULL operators to a join condition clause
                 Key: CALCITE-1422
                 URL: https://issues.apache.org/jira/browse/CALCITE-1422
             Project: Calcite
          Issue Type: Improvement
          Components: core
            Reporter: Viktor Batytskyi
            Assignee: Julian Hyde
             Fix For: 1.9.0


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):
{code}
  @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);
  }
{code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)