You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Miguel Oliveira <mi...@gmail.com> on 2016/09/12 10:11:06 UTC
Wrong Field in JDBC Adapter
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.
Re: Wrong Field in JDBC Adapter
Posted by Miguel Oliveira <mi...@gmail.com>.
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 <jh...@apache.org>:
> 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 <
> migueloliveira1990@gmail.com> 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.
>
>
Re: Wrong Field in JDBC Adapter
Posted by Julian Hyde <jh...@apache.org>.
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 <mi...@gmail.com> 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.