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]])
      , }]
       

        Attachments

          Issue Links

            Activity

              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: