[
https://issues.apache.org/jira/browse/CALCITE-864?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maryann Xue updated CALCITE-864:
--------------------------------
Description:
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.
was:
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}
> Incorrect "fieldType" in RexFieldAccess 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)
