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)
