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

Calcite generate wrong field names in JDBC adapter

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 1.8.0
    • Fix Version/s: 1.9.0
    • Component/s: core
    • Labels:
    • Environment:

      This bug was detected using an Oracle database and the calcite version 1.8.0.

      Description

      For example, this query:

      SELECT v1272.`QUESTN_LBL` `Label (Question Metrics)`,v1274.`LBL` `Label (Question)`,v1272.`CLICKTHRU_CNT` `Click Thru Count (Question Metrics)`,v1272.`CLICKTHRU_RATIO` `Click Thru Ratio (Question Metrics)`,v1272.`DATE` `Date (Question Metrics)`,v1272.`HAS_RESPONSES` `Has Responses (Question Metrics)`,v1272.`LOCALE_KEY` `Locale (Question Metrics)`,v1272.`QUESTN_CNT` `Question Count (Question Metrics)`,v1272.`QUESTN_KEY` `NAVIGATION_8_QUESTION` FROM DW_REPORTING.QUESTION v1272 LEFT JOIN DW_REPORTING.METRICS v1274 ON v1272.`QUESTN_KEY` = v1274.`QUESTN_KEY` WHERE UPPER(CAST(v1274.`LBL` AS VARCHAR(1000))) LIKE UPPER('% den %')

      Generates the following SQL query:

      SELECT "QUESTN_LBL" "Label (Question Metrics)", "LBL" "Label (Question)", "CLICKTHRU_CNT" "Click Thru Count (Question Met", "CLICKTHRU_RATIO" "Click Thru Ratio (Question Met", "DATE" "Date (Question Metrics)", "HAS_RESPONSES" "Has Responses (Question Metric", "LOCALE_KEY" "Locale (Question Metrics)", "QUESTN_CNT" "Question Count (Question Metri", "QUESTN_KEY" "NAVIGATION_8_QUESTION", "ID", "QUESTN_KEY0" "QUESTN_KEY"
      FROM (SELECT "ID", "QUESTN_KEY", "LOCALE_KEY", "QUESTN_LBL", "DATE", "QUESTN_CNT", "CLICKTHRU_CNT", "CLICKTHRU_RATIO", "HAS_RESPONSES"
      FROM "DW_REPORTING"."QUESTION") "t"
      LEFT JOIN (SELECT "QUESTN_KEY", "LBL"
      FROM "DW_REPORTING"."METRICS") "t0" ON "t"."QUESTN_KEY" = "t0"."QUESTN_KEY"
      WHERE UPPER("t0"."LBL") LIKE UPPER('% den %')

      The problem here is with the "QUESTN_KEY0" doesn't exist, Both "t" and "t0" have the "QUESTN_KEY" field and after the JdbcFilter, the table alias "t" and "t0" seems to disappear.

      This is the generated plan:

      [TABLE, #ID {PLAN=JdbcToEnumerableConverter
         JdbcProject(Label (Question Metrics)=[$3], Label (Question)=[$10], Click Thru Count (Question Metrics)=[$6], Click Thru Ratio (Question Metrics)=[$7], Date (Question Metrics)=[$4], Has Responses (Question Metrics)=[$8], Locale (Question Metrics)=[$2], Question Count (Question Metrics)=[$5], NAVIGATION_8_QUESTION=[$1], ID=[$0], QUESTN_KEY=[$9])
           JdbcFilter(condition=[LIKE(UPPER(CAST($10):VARCHAR(1000) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"), UPPER('% den %'))])
             JdbcJoin(condition=[=($1, $9)], joinType=[left])
               JdbcProject(ID=[$0], QUESTN_KEY=[$1], LOCALE_KEY=[$2], QUESTN_LBL=[$3], DATE=[$4], QUESTN_CNT=[$5], CLICKTHRU_CNT=[$6], CLICKTHRU_RATIO=[$8], HAS_RESPONSES=[$9])
                 JdbcTableScan(table=[[DW_REPORTING, QUESTION]])
               JdbcProject(QUESTN_KEY=[$0], LBL=[$1])
                 JdbcTableScan(table=[[DW_REPORTING, METRICS]])
      , }]
       

        Issue Links

          Activity

          Hide
          julianhyde Julian Hyde added a comment -

          I tried to reproduce this (with a simpler query, and not against Oracle) but I could not reproduce. Here is the patch:

          diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
          index 97da6c3..ec6b05a 100644
          --- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
          +++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
          @@ -318,6 +318,19 @@
                   .returnsCount(275);
             }
           
          +  @Test public void testJoinPlan2() {
          +    final String sql = "SELECT T1.\"product_class_id\", T2.\"product_class_id\"\n"
          +        + "FROM \"foodmart\".\"product\" AS T1\n"
          +        + " INNER JOIN \"foodmart\".\"product_class\" AS T2\n"
          +        + " ON T1.\"product_class_id\" = T2.\"product_class_id\"\n"
          +        + "WHERE T2.\"product_department\" = 'Frozen Foods'\n"
          +        + " OR T2.\"product_department\" = 'Baking Goods'\n"
          +        + " AND T1.\"brand_name\" <> 'King'";
          +    CalciteAssert.model(JdbcTest.FOODMART_MODEL)
          +        .query(sql).runs()
          +        .returnsCount(275);
          +  }
          +
             /** Test case for
              * <a href="https://issues.apache.org/jira/browse/CALCITE-657">[CALCITE-657]
              * NullPointerException when executing JdbcAggregate implement method</a>. */
          

          Can you try to refine your test case into (a) a standard schema like EMP and DEPT, and/or (b) on a database other than Oracle? Which version of Calcite are you running?

          Show
          julianhyde Julian Hyde added a comment - I tried to reproduce this (with a simpler query, and not against Oracle) but I could not reproduce. Here is the patch: diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java index 97da6c3..ec6b05a 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java @@ -318,6 +318,19 @@ .returnsCount(275); } + @Test public void testJoinPlan2() { + final String sql = "SELECT T1.\"product_class_id\", T2.\"product_class_id\"\n" + + "FROM \"foodmart\".\"product\" AS T1\n" + + " INNER JOIN \"foodmart\".\"product_class\" AS T2\n" + + " ON T1.\"product_class_id\" = T2.\"product_class_id\"\n" + + "WHERE T2.\"product_department\" = 'Frozen Foods'\n" + + " OR T2.\"product_department\" = 'Baking Goods'\n" + + " AND T1.\"brand_name\" <> 'King'"; + CalciteAssert.model(JdbcTest.FOODMART_MODEL) + .query(sql).runs() + .returnsCount(275); + } + /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-657">[CALCITE-657] * NullPointerException when executing JdbcAggregate implement method</a>. */ Can you try to refine your test case into (a) a standard schema like EMP and DEPT, and/or (b) on a database other than Oracle? Which version of Calcite are you running?
          Hide
          migueltaoliveira Miguel Oliveira added a comment - - edited

          You can reproduce the error with this query:

          "SELECT v1.deptno `deptno`, v1.dname `dep name`, v1.loc `location`, v2.deptno `employee dept no`, v2.ename `employee name`, v2.job `job` FROM Scott.dept v1 LEFT JOIN Scott.emp v2 ON v1.deptno = v2.deptno WHERE v2.job LIKE 'president'"

          using the SCOTT schema (emp and dept tables): http://www.orafaq.com/wiki/SCOTT, the database PostgreSQL and the calcite version 1.8.0.

          Show
          migueltaoliveira Miguel Oliveira added a comment - - edited You can reproduce the error with this query: "SELECT v1.deptno `deptno`, v1.dname `dep name`, v1.loc `location`, v2.deptno `employee dept no`, v2.ename `employee name`, v2.job `job` FROM Scott.dept v1 LEFT JOIN Scott.emp v2 ON v1.deptno = v2.deptno WHERE v2.job LIKE 'president'" using the SCOTT schema (emp and dept tables): http://www.orafaq.com/wiki/SCOTT , the database PostgreSQL and the calcite version 1.8.0 .
          Hide
          julianhyde Julian Hyde added a comment -

          Thanks - I can reproduce now. Should have a fix shortly.

          Show
          julianhyde Julian Hyde added a comment - Thanks - I can reproduce now. Should have a fix shortly.
          Hide
          julianhyde Julian Hyde added a comment -

          I have a fix in https://github.com/julianhyde/calcite/tree/1372-jdbc-field-names. MinJi Kim, can you please review and +1, since it's similar to CALCITE-1333?

          Show
          julianhyde Julian Hyde added a comment - I have a fix in https://github.com/julianhyde/calcite/tree/1372-jdbc-field-names . MinJi Kim , can you please review and +1, since it's similar to CALCITE-1333 ?
          Hide
          minjikim MinJi Kim added a comment -

          +1. It looks good to me. I am debugging something related, but it turns out it is not the same problem (I tried out your patch to see if it solved the problem). Once I pinpoint the problem, I will upload a patch for review under a separate jira.

          Show
          minjikim MinJi Kim added a comment - +1. It looks good to me. I am debugging something related, but it turns out it is not the same problem (I tried out your patch to see if it solved the problem). Once I pinpoint the problem, I will upload a patch for review under a separate jira.
          Hide
          julianhyde Julian Hyde added a comment -

          For what it's worth, the test case is minimal. Without both the WHERE clause and the LEFT JOIN the problem didn't reproduce. The logic about how table aliases and sub-query column names are inherited in SqlImplementor/RelToSqlConverter still seems fragile IMHO, and it doesn't help that DB2 takes a different code path. More tests are needed.

          Thanks for your +1 and I will commit shortly.

          Show
          julianhyde Julian Hyde added a comment - For what it's worth, the test case is minimal. Without both the WHERE clause and the LEFT JOIN the problem didn't reproduce. The logic about how table aliases and sub-query column names are inherited in SqlImplementor/RelToSqlConverter still seems fragile IMHO, and it doesn't help that DB2 takes a different code path. More tests are needed. Thanks for your +1 and I will commit shortly.
          Hide
          migueltaoliveira Miguel Oliveira added a comment - - edited

          I was looking to your commit and I have some doubts about this method: https://github.com/julianhyde/calcite/commit/0f29477d9843c6338dd6f43bffc4d9220ad17dc8#diff-3662e7b8ef2b9ddf9f0008edec8dbe22R429.
          For example, if you have a Join between two nodes and the left node has two aliases (could be a join, too) and the right node has only one, the "aliases.next()" will return only one RelDataType instead of two, discarding one RelDataType from the left node. Am I right? I have tested this and I confirm this behaviour.

          Show
          migueltaoliveira Miguel Oliveira added a comment - - edited I was looking to your commit and I have some doubts about this method: https://github.com/julianhyde/calcite/commit/0f29477d9843c6338dd6f43bffc4d9220ad17dc8#diff-3662e7b8ef2b9ddf9f0008edec8dbe22R429 . For example, if you have a Join between two nodes and the left node has two aliases (could be a join, too) and the right node has only one, the "aliases.next()" will return only one RelDataType instead of two, discarding one RelDataType from the left node. Am I right? I have tested this and I confirm this behaviour.
          Hide
          julianhyde Julian Hyde added a comment -

          The number of aliases should be equal to the number of LEAVES of the join. For instance,

          (a JOIN b) JOIN (c JOIN d)

          has 3 join nodes, 4 leaves. Note that the method only calls iterator.next() when it reaches a leaf. So I think the method is OK. If you have a query that breaks it, let me know.

          Show
          julianhyde Julian Hyde added a comment - The number of aliases should be equal to the number of LEAVES of the join. For instance, (a JOIN b) JOIN (c JOIN d) has 3 join nodes, 4 leaves. Note that the method only calls iterator.next() when it reaches a leaf. So I think the method is OK. If you have a query that breaks it, let me know.
          Hide
          jcamachorodriguez Jesus Camacho Rodriguez added a comment -

          I will resolve this, as it has been fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/f9724c2.

          Please, leave a comment if there are any issues left.

          Show
          jcamachorodriguez Jesus Camacho Rodriguez added a comment - I will resolve this, as it has been fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/f9724c2 . Please, leave a comment if there are any issues left.
          Hide
          jcamachorodriguez Jesus Camacho Rodriguez added a comment -

          Resolved in release 1.9.0 (2016-09-22)

          Show
          jcamachorodriguez Jesus Camacho Rodriguez added a comment - Resolved in release 1.9.0 (2016-09-22)

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              migueltaoliveira Miguel Oliveira
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development