[
https://issues.apache.org/jira/browse/CALCITE-1422?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde resolved CALCITE-1422.
----------------------------------
Resolution: Fixed
> In JDBC adapter, allow IS NULL and IS NOT NULL operators in generated SQL
> join condition
> ----------------------------------------------------------------------------------------
>
> 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.11.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)
