I have logged https://issues.apache.org/jira/browse/CALCITE-1372 for this.
Best regards, Miguel 2016-09-12 13:24 GMT+01:00 Julian Hyde <[email protected]>: > I don’t have time to look into this right now, but I’m fairly sure it is a > bug in the JDBC adapter, and I think it will reproduce against other JDBC > data sources. Can you please log a JIRA case and post the URL here. > > Julian > > > > On Sep 12, 2016, at 12:11 PM, Miguel Oliveira < > [email protected]> wrote: > > > > Hi, > > > > I am using the Jdbc Adapter to generate a sql query using a Oracle > > DataSource. > > The question is about this particular calcite 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 %') > > > > > > That generates the following 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]]) > >> , }] > > > > > > > > Can you tell me what is wrong with this particular query? > > > > Best regards, > > Miguel. > >
