[
https://issues.apache.org/jira/browse/KYLIN-4440?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xi Chen closed KYLIN-4440.
--------------------------
Resolution: Won't Fix
> IllegalStateException thrown when a query contains group by clause with case
> when of dimensional table columns
> --------------------------------------------------------------------------------------------------------------
>
> Key: KYLIN-4440
> URL: https://issues.apache.org/jira/browse/KYLIN-4440
> Project: Kylin
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: v2.2.0, v2.5.1, v2.6.5
> Reporter: Xi Chen
> Assignee: Yifei Wu
> Priority: Major
> Labels: calcite
>
> When a sub-query contains group by clause with case when of dimensional table
> columns, and the sub-query is joined by another sub-query, the whole query
> fails throwing java.lang.IllegalStateException.
> Example query of kylin_sales_cube:
> {code:java}
> SELECT *
> FROM
> (SELECT part_dt ,
> CASE
> WHEN buyer_id >= 10003000 THEN 'A'
> ELSE kylin_account.account_country
> END AS category ,
> sum(price) AS total_sold ,
> count(DISTINCT seller_id) AS sellers
> FROM kylin_sales
> JOIN kylin_account ON kylin_sales.buyer_id = kylin_account.account_id
> GROUP BY part_dt ,
> CASE
> WHEN buyer_id >= 10003000 THEN 'A'
> ELSE kylin_account.account_country
> END
> ORDER BY part_dt) t1
> JOIN
> (SELECT part_dt ,
> sum(price) AS total_sold ,
> count(DISTINCT seller_id) AS sellers
> FROM kylin_sales
> JOIN kylin_account ON kylin_sales.buyer_id = kylin_account.account_id
> GROUP BY part_dt
> ORDER BY part_dt) t2 ON t1.part_dt = t2.part_dt
> ORDER BY t1.part_dt DESC
> {code}
> Exception message:
> {code:java}
> RowType=7, ColumnRowType=8 while executing SQL: "SELECT * FROM ( SELECT
> part_dt , CASE WHEN buyer_id >= 10003000 THEN 'A' ELSE
> kylin_account.account_country END AS category , sum(price) AS total_sold ,
> count(DISTINCT seller_id) AS sellers FROM kylin_sales LEFT JOIN kylin_account
> ON kylin_sales.buyer_id = kylin_account.account_id GROUP BY part_dt , CASE
> WHEN buyer_id >= 10003000 THEN 'A' ELSE kylin_account.account_country END
> ORDER BY part_dt ) t1 LEFT JOIN ( SELECT part_dt , sum(price) AS total_sold ,
> count(DISTINCT seller_id) AS sellers FROM kylin_sales LEFT JOIN kylin_account
> ON kylin_sales.buyer_id = kylin_account.account_id GROUP BY part_dt ORDER BY
> part_dt ) t2 ON t1.part_dt = t2.part_dt ORDER BY t1.part_dt DESC LIMIT
> 50000"{code}
> More message from kylin logs:
> {code:java}
> Caused by: java.lang.IllegalStateException: RowType=7, ColumnRowType=8
> at
> org.apache.kylin.query.relnode.OLAPJoinRel.buildColumnRowType(OLAPJoinRel.java:223)
> at
> org.apache.kylin.query.relnode.OLAPJoinRel.implementOLAP(OLAPJoinRel.java:174)
> at
> org.apache.kylin.query.relnode.OLAPRel$OLAPImplementor.visitChild(OLAPRel.java:84)
> at
> org.apache.kylin.query.relnode.OLAPSortRel.implementOLAP(OLAPSortRel.java:72)
> at
> org.apache.kylin.query.relnode.OLAPRel$OLAPImplementor.visitChild(OLAPRel.java:84)
> at
> org.apache.kylin.query.relnode.OLAPLimitRel.implementOLAP(OLAPLimitRel.java:77)
> at
> org.apache.kylin.query.relnode.OLAPRel$OLAPImplementor.visitChild(OLAPRel.java:84)
> at
> org.apache.kylin.query.relnode.OLAPToEnumerableConverter.implement(OLAPToEnumerableConverter.java:75)
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:103)
> at
> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1278)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:331)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:230)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:796)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:655)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:618)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:221)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:603)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:638)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:149)
> ... 83 more
> {code}
>
> However, the sub-queries both runs successfully alone:
> {code:java}
> SELECT part_dt ,
> CASE
> WHEN buyer_id >= 10003000 THEN 'A'
> ELSE kylin_account.account_country
> END AS category ,
> sum(price) AS total_sold ,
> count(DISTINCT seller_id) AS sellers
> FROM kylin_sales
> JOIN kylin_account ON kylin_sales.buyer_id = kylin_account.account_id
> GROUP BY part_dt ,
> CASE
> WHEN buyer_id >= 10003000 THEN 'A'
> ELSE kylin_account.account_country
> END
> ORDER BY part_dt;{code}
> {code:java}
> SELECT part_dt ,
> sum(price) AS total_sold ,
> count(DISTINCT seller_id) AS sellers
> FROM kylin_sales
> JOIN kylin_account ON kylin_sales.buyer_id = kylin_account.account_id
> GROUP BY part_dt
> ORDER BY part_dt
> {code}
> If the case when clause is removed from sub-query, it also runs successfully:
> {code:java}
> SELECT *
> FROM
> (SELECT part_dt ,
> kylin_account.account_country,
> sum(price) AS total_sold ,
> count(DISTINCT seller_id) AS sellers
> FROM kylin_sales
> JOIN kylin_account ON kylin_sales.buyer_id = kylin_account.account_id
> GROUP BY part_dt ,
> kylin_account.account_country
> ORDER BY part_dt) t1
> JOIN
> (SELECT part_dt ,
> sum(price) AS total_sold ,
> count(DISTINCT seller_id) AS sellers
> FROM kylin_sales
> JOIN kylin_account ON kylin_sales.buyer_id = kylin_account.account_id
> GROUP BY part_dt
> ORDER BY part_dt) t2 ON t1.part_dt = t2.part_dt
> ORDER BY t1.part_dt DESC
> {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
