You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@kylin.apache.org by "zhong.zhu (Jira)" <ji...@apache.org> on 2023/12/11 11:44:00 UTC
[jira] [Commented] (KYLIN-5743) Set kylin.query.convert-sum-expression-enabled=true, fail to completely hit the aggregate index when the query contains sum (case when) expressions
[ https://issues.apache.org/jira/browse/KYLIN-5743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17795303#comment-17795303 ]
zhong.zhu commented on KYLIN-5743:
----------------------------------
h1.Root Cause
With the _*kylin.query.convert-sum-expression-enabled=true*_ conversion switch turned on, the original SQL generates an execution plan as follows
{code:shell}
KapOLAPToEnumerableConverter
KapLimitRel(ctx=[], fetch=[500])
KapAggregateRel(group-set=[[]], groups=[null], EXPR$0=[SUM($0)], ctx=[])
KapProjectRel($f0=[$1], ctx=[])
KapJoinRel(condition=[=($0, $2)], joinType=[inner], ctx=[])
KapProjectRel(LO_COMMITDATE=[$15], CASE=[CASE(=($15, CAST('20230501'):DATE NOT NULL), $11, null)], ctx=[])
KapTableScan(table=[[SSB, LINEORDER]], ctx=[], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19]])
KapProjectRel(EXPR$0=[$0], ctx=[])
KapAggregateRel(group-set=[[]], groups=[null], EXPR$0=[MAX($0)], ctx=[])
KapProjectRel(LO_COMMITDATE=[$15], ctx=[])
KapTableScan(table=[[SSB, LINEORDER]], ctx=[], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19]])
{code}
It can be seen that case when is pushed over TableScan (in combination with another ProjectMergeRule to the above result), which makes SumExpressionRule not work. It is possible that this is unstable, and it is also possible to get a different execution plan
{code:shell}
KapOLAPToEnumerableConverter
KapLimitRel(ctx=[], fetch=[500])
KapAggregateRel(group-set=[[]], groups=[null], AGG$0=[SUM($0)], ctx=[])
KapProjectRel($f0=[CASE(=($0, CAST('20230501'):DATE NOT NULL), $1, null)], ctx=[])
KapAggregateRel(group-set=[[0]], groups=[null], TOP_AGG$0=[SUM($1)], TOP_AGG$1=[SUM($2)], ctx=[])
KapProjectRel(LO_COMMITDATE=[$0], SUM_CASE$0$0=[$1], $f2=[*(0, $2)], ctx=[])
KapAggregateRel(group-set=[[0]], groups=[null], SUM_CASE$0$0=[SUM($1)], SUM_CONST$1=[COUNT()], ctx=[])
KapProjectRel(LO_COMMITDATE=[$0], LO_DISCOUNT=[$1], ctx=[])
KapProjectRel(LO_COMMITDATE=[$0], LO_DISCOUNT=[$1], LO_ORDERDATE=[$2], ctx=[])
KapJoinRel(condition=[=($0, $3)], joinType=[inner], ctx=[])
KapProjectRel(LO_COMMITDATE=[$15], LO_DISCOUNT=[$11], LO_ORDERDATE=[$5], ctx=[])
KapTableScan(table=[[SSB, LINEORDER]], ctx=[], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19]])
KapAggregateRel(group-set=[[]], groups=[null], EXPR$0=[MAX($0)], ctx=[])
KapProjectRel(LO_COMMITDATE=[$15], ctx=[])
KapTableScan(table=[[SSB, LINEORDER]], ctx=[], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19]])
{code}
h1.Fix Design
With *_kylin.query.convert-sum-expression-enabled=true_*, then skipping the _*KapProjectJoinTransposeRule*_ then ensures that the second execution plan above is stabilized, so that it can hit two aggregated indexes instead of one aggregated and one detailed.
> Set kylin.query.convert-sum-expression-enabled=true, fail to completely hit the aggregate index when the query contains sum (case when) expressions
> ---------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: KYLIN-5743
> URL: https://issues.apache.org/jira/browse/KYLIN-5743
> Project: Kylin
> Issue Type: Bug
> Affects Versions: 5.0-beta
> Reporter: zhong.zhu
> Assignee: zhong.zhu
> Priority: Major
> Fix For: 5.0.0
>
>
> {code:sql}
> select
> sum(
> case
> when LO_COMMITDATE = '20230501' then LO_DISCOUNT
> end
> )
> from
> (
> select
> LO_COMMITDATE,
> LO_DISCOUNT,
> LINEORDER.LO_ORDERDATE
> from
> ssb.LINEORDER
> ) a
> where
> LO_COMMITDATE = (
> select
> max(LO_COMMITDATE)
> from
> ssb.LINEORDER
> )
> LIMIT
> 500
> {code}
> Fix the sum case when in this scenario so that it hits aggregated indexes
--
This message was sent by Atlassian Jira
(v8.20.10#820010)