Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1422

In JDBC adapter, allow IS NULL and IS NOT NULL operators in generated SQL join condition

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.11.0
    • Component/s: core
    • Labels:
      None

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

        Activity

        Hide
        victor.batyt@gmail.com Viktor Batytskyi added a comment -
        Show
        victor.batyt@gmail.com Viktor Batytskyi added a comment - Please take a look at: https://github.com/apache/calcite/pull/300
        Hide
        julianhyde Julian Hyde added a comment -

        Looks good; I will merge after 1.10 is released.

        Show
        julianhyde Julian Hyde added a comment - Looks good; I will merge after 1.10 is released.
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/57346213 . Thanks for the PR, Viktor Batytskyi !
        Hide
        julianhyde Julian Hyde added a comment -

        Resolved in release 1.11.0 (2017-01-11).

        Show
        julianhyde Julian Hyde added a comment - Resolved in release 1.11.0 (2017-01-11).

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            victor.batyt@gmail.com Viktor Batytskyi
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development