[
https://issues.apache.org/jira/browse/CALCITE-864?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maryann Xue updated CALCITE-864:
--------------------------------
Summary: Incorrect rowType of correlate variable when correlated sub-query
has its left relation as a join (was: Incorrect "fieldType" in RexFieldAccess
when correlated sub-query has its left relation as a join)
> Incorrect rowType of correlate variable when correlated sub-query has its
> left relation as a join
> -------------------------------------------------------------------------------------------------
>
> Key: CALCITE-864
> URL: https://issues.apache.org/jira/browse/CALCITE-864
> Project: Calcite
> Issue Type: Bug
> Reporter: Maryann Xue
> Assignee: Julian Hyde
>
> DDL
> {code}
> create table orders (
> order_id varchar(15) not null primary key,
> customer_id\ varchar(10),
> item_id varchar(10),
> price integer,
> quantity integer,
> date timestamp,
> the_year integer);
> create table items (
> item_id varchar(10) not null primary key,
> name varchar,
> price integer,
> discount1 integer,
> discount2 integer,
> supplier_id varchar(10),
> description varchar);
> {code}
> Query:
> {code}
> select order_id
> from orders o join items i on o.item_id = i.item_id
> where quantity = (
> select max(quantity)
> from orders o2 join items i2 on o2.item_id = i2.item_id
> where i.supplier_id = i2.supplier_id
> )
> {code}
> While executing this query with option "forceDecorrelate=false", I got a
> RuntimeException:
> {code}
> java.lang.RuntimeException: java.sql.SQLException: error while executing SQL
> "select "order_id" from "Join"."OrderTable" o JOIN "Join"."ItemTable" i on
> o."item_id" = i."item_id" where quantity = (select max(quantity) from
> "Join"."OrderTable" o2 JOIN "Join"."ItemTable" i2 on o2."item_id" =
> i2."item_id" where i."supplier_id" = i2."supplier_id")":
> org.apache.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type
> mismatch. TIMESTAMP and VARCHAR for
> org.apache.phoenix.expression.CorrelateVariableFieldAccessExpression@462bd95e
> = supplier_id
> at org.apache.phoenix.calcite.CalciteIT$Sql.resultIs(CalciteIT.java:187)
> at
> org.apache.phoenix.calcite.CalciteIT.testCorrelate(CalciteIT.java:1155)
> Caused by: java.sql.SQLException: error while executing SQL "select
> "order_id" from "Join"."OrderTable" o JOIN "Join"."ItemTable" i on
> o."item_id" = i."item_id" where quantity = (select max(quantity) from
> "Join"."OrderTable" o2 JOIN "Join"."ItemTable" i2 on o2."item_id" =
> i2."item_id" where i."supplier_id" = i2."supplier_id")":
> org.apache.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type
> mismatch. TIMESTAMP and VARCHAR for
> org.apache.phoenix.expression.CorrelateVariableFieldAccessExpression@462bd95e
> = supplier_id
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:112)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:130)
> at org.apache.phoenix.calcite.CalciteIT$Sql.resultIs(CalciteIT.java:174)
> ... 28 more
> Caused by: java.lang.RuntimeException:
> org.apache.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type
> mismatch. TIMESTAMP and VARCHAR for
> org.apache.phoenix.expression.CorrelateVariableFieldAccessExpression@462bd95e
> = supplier_id
> at
> org.apache.phoenix.calcite.CalciteUtils$3.newExpression(CalciteUtils.java:145)
> at
> org.apache.phoenix.calcite.CalciteUtils.toExpression(CalciteUtils.java:807)
> at
> org.apache.phoenix.calcite.rel.PhoenixTableScan.implement(PhoenixTableScan.java:187)
> at
> org.apache.phoenix.calcite.rel.PhoenixRelImplementorImpl.visitInput(PhoenixRelImplementorImpl.java:50)
> at
> org.apache.phoenix.calcite.rel.PhoenixToClientConverter.implement(PhoenixToClientConverter.java:42)
> at
> org.apache.phoenix.calcite.rel.PhoenixRelImplementorImpl.visitInput(PhoenixRelImplementorImpl.java:50)
> at
> org.apache.phoenix.calcite.rel.PhoenixAbstractJoin.implementInput(PhoenixAbstractJoin.java:49)
> at
> org.apache.phoenix.calcite.rel.PhoenixServerJoin.implement(PhoenixServerJoin.java:113)
> at
> org.apache.phoenix.calcite.rel.PhoenixRelImplementorImpl.visitInput(PhoenixRelImplementorImpl.java:50)
> at
> org.apache.phoenix.calcite.rel.PhoenixServerAggregate.implement(PhoenixServerAggregate.java:57)
> at
> org.apache.phoenix.calcite.rel.PhoenixRelImplementorImpl.visitInput(PhoenixRelImplementorImpl.java:50)
> at
> org.apache.phoenix.calcite.rel.PhoenixCorrelate.implement(PhoenixCorrelate.java:78)
> at
> org.apache.phoenix.calcite.rel.PhoenixRelImplementorImpl.visitInput(PhoenixRelImplementorImpl.java:50)
> at
> org.apache.phoenix.calcite.rel.PhoenixFilter.implement(PhoenixFilter.java:59)
> at
> org.apache.phoenix.calcite.rel.PhoenixRelImplementorImpl.visitInput(PhoenixRelImplementorImpl.java:50)
> at
> org.apache.phoenix.calcite.rel.PhoenixClientProject.implement(PhoenixClientProject.java:59)
> at
> org.apache.phoenix.calcite.rel.PhoenixRelImplementorImpl.visitInput(PhoenixRelImplementorImpl.java:50)
> at
> org.apache.phoenix.calcite.rel.PhoenixToEnumerableConverter.makePlan(PhoenixToEnumerableConverter.java:88)
> at
> org.apache.phoenix.calcite.rel.PhoenixToEnumerableConverter.implement(PhoenixToEnumerableConverter.java:69)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:99)
> at
> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1050)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:293)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:188)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:671)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:572)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:541)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:173)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:561)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:477)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:109)
> ... 30 more
> Caused by: org.apache.phoenix.schema.TypeMismatchException: ERROR 203
> (22005): Type mismatch. TIMESTAMP and VARCHAR for
> org.apache.phoenix.expression.CorrelateVariableFieldAccessExpression@462bd95e
> = supplier_id
> at
> org.apache.phoenix.schema.TypeMismatchException.newException(TypeMismatchException.java:53)
> at
> org.apache.phoenix.expression.ComparisonExpression.create(ComparisonExpression.java:133)
> at
> org.apache.phoenix.calcite.CalciteUtils$3.newExpression(CalciteUtils.java:143)
> ... 60 more
> {code}
> The plan was:
> {code}
> PhoenixToEnumerableConverter
> PhoenixClientProject(order_id=[$0])
> PhoenixFilter(condition=[=($4, $14)])
> PhoenixCorrelate(correlation=[$cor0], joinType=[LEFT],
> requiredColumns=[{12}])
> PhoenixClientProject(order_id=[$7], CUSTOMER_ID=[$8], ITEM_ID=[$9],
> PRICE=[$10], QUANTITY=[$11], DATE=[$12], THE_YEAR=[$13], ITEM_ID0=[$0],
> NAME=[$1], PRICE0=[$2], DISCOUNT1=[$3], DISCOUNT2=[$4], SUPPLIER_ID=[$5],
> DESCRIPTION=[$6])
> PhoenixToClientConverter
> PhoenixServerJoin(condition=[=($9, $0)], joinType=[inner])
> PhoenixTableScan(table=[[phoenix, Join, ItemTable]])
> PhoenixToClientConverter
> PhoenixTableScan(table=[[phoenix, Join, OrderTable]])
> PhoenixServerAggregate(group=[{}], EXPR$0=[MAX($4)])
> PhoenixServerJoin(condition=[=($2, $7)], joinType=[inner])
> PhoenixTableScan(table=[[phoenix, Join, OrderTable]])
> PhoenixToClientConverter
> PhoenixTableScan(table=[[phoenix, Join, ItemTable]],
> filter=[=($cor0.SUPPLIER_ID, $5)])
> {code}
> Debugging this case, I found that $cor0.SUPPLIER_ID was referencing to the
> 6th (index=5) of the correlating variable. And meanwhile the correlating
> variable has the same RelDataType as table "items" but not the left relation
> ("orders" join "items") of the rel Correlate.
> After switching the join tables of the left relation, the query gave the
> right result. I think the reason was that the leading part (all that matters
> to RexFieldAccess "$cor0.SUPPLIER_ID") was now the same between table "items"
> and the left relation ("items" join "orders") of the rel Correlate.
> {code}
> select order_id
> from items i join orders o on o.item_id = i.item_id
> where quantity = (
> select max(quantity)
> from orders o2 join items i2 on o2.item_id = i2.item_id
> where i.supplier_id = i2.supplier_id
> )
> {code}
> With de-correlation, the first query also failed because the join condition
> was referencing to the wrong position. The second query succeed with
> de-correlation too.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
