You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2016/10/08 00:16:20 UTC
[jira] [Commented] (CALCITE-1422) In JDBC adapter, allow IS NULL
and IS NOT NULL operators in generated SQL join condition
[ https://issues.apache.org/jira/browse/CALCITE-1422?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15556712#comment-15556712 ]
Julian Hyde commented on CALCITE-1422:
--------------------------------------
Looks good; I will merge after 1.10 is released.
> 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)