kylin-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Xi Chen (Jira)" <j...@apache.org>
Subject [jira] [Updated] (KYLIN-4440) IllegalStateException thrown when a query contains group by clause with case when of dimensional table columns
Date Tue, 07 Apr 2020 12:40:00 GMT

     [ https://issues.apache.org/jira/browse/KYLIN-4440?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Xi Chen updated KYLIN-4440:
---------------------------
    Description: 
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:
{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}
 

  was:
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:
{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}
 


> 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
>            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:
> {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.3.4#803005)

Mime
View raw message