[jira] [Commented] (CALCITE-1372) Calcite generate wrong field names in JDBC adapter

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1372?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15487150#comment-15487150 ] 

Julian Hyde commented on CALCITE-1372:
--------------------------------------

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

{noformat}
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>. */
{noformat}

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?

> Calcite generate wrong field names in JDBC adapter
> --------------------------------------------------
>
>                 Key: CALCITE-1372
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1372
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.8.0
>         Environment: This bug was detected using an Oracle datasource.
>            Reporter: Miguel Oliveira
>            Assignee: Julian Hyde
>            Priority: Critical
>              Labels: adapters, jdbc
>
> For example, this query: 
> {quote}
> 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 %') 
> {quote}
> Generates the following SQL query:
> {quote} 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 %')
> {quote}
> 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:
> {noformat}
> [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]])
> , }]
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)