You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Xurenhe (Jira)" <ji...@apache.org> on 2022/10/26 11:26:00 UTC
[jira] [Comment Edited] (CALCITE-5328) The rule of AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN throws error when meeting the rollup's query
[ https://issues.apache.org/jira/browse/CALCITE-5328?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17624382#comment-17624382 ]
Xurenhe edited comment on CALCITE-5328 at 10/26/22 11:25 AM:
-------------------------------------------------------------
The rule of *_CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN_* may work as follow:
*origin sql:*
{code:java}
SELECT c1,
c2,
c3,
count(DISTINCT c4) AS c,
sum(c4) AS s
FROM t
GROUP BY c1,
c2,
c3
GROUPING SETS((c1, c2, c3), (c1, c2), (c1), ()); {code}
*transformed sql:*
{code:java}
SELECT t1.c1,
t1.c2,
t1.c3,
c,
s
FROM
(SELECT c1,
c2,
c3,
count(DISTINCT c4) AS c,
GROUPING(c1, c2, c3) as g
FROM t
GROUP BY c1,
c2,
c3
GROUPING SETS((c1, c2, c3), (c1, c2), (c1), ()))t1
INNER JOIN
(SELECT c1,
c2,
c3,
sum(c4) AS s,
GROUPING(c1, c2, c3) as g
FROM t
GROUP BY c1,
c2,
c3
GROUPING SETS((c1, c2, c3), (c1, c2), (c1), ()))t2
ON t1.c1 IS NOT DISTINCT FORM t2.c1
AND t1.c2 IS NOT DISTINCT FORM t2.c2
AND t1.c3 IS NOT DISTINCT FORM t2.c3
AND t1.g = t2.g; {code}
Note that we need add a join condition with grouping's value to avoid origin field's null-value join group-set emit null-value.
was (Author: wojustme):
The rule of *_CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN_* may work as follow:
*origin sql:*
{code:java}
SELECT c1,
c2,
c3,
count(DISTINCT c4) AS c,
sum(c4) AS s
FROM t
GROUP BY c1,
c2,
c3
GROUPING SETS((c1, c2, c3), (c1, c2), (c1), ()); {code}
*transformed sql:*
{code:java}
SELECT t1.c1,
t1.c2,
t1.c3,
c,
s
FROM
(SELECT c1,
c2,
c3,
count(DISTINCT c4) AS c,
GROUPING(c1, c2, c3) as g
FROM t
GROUP BY c1,
c2,
c3
GROUPING SETS((c1, c2, c3), (c1, c2), (c1), ()))t1
INNER JOIN
(SELECT c1,
c2,
c3,
sum(c4) AS s,
GROUPING(c1, c2, c3) as g
FROM t
GROUP BY c1,
c2,
c3
GROUPING SETS((c1, c2, c3), (c1, c2), (c1), ()))t2
ON t1.c1 IS NOT DISTINCT FORM t2.c1
AND t1.c2 IS NOT DISTINCT FORM t2.c2
AND t1.c3 IS NOT DISTINCT FORM t2.c3
AND t1.g = t2.g; {code}
Note that we need add a join condition with grouping's value.
> The rule of AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN throws error when meeting the rollup's query
> -----------------------------------------------------------------------------------------------------
>
> Key: CALCITE-5328
> URL: https://issues.apache.org/jira/browse/CALCITE-5328
> Project: Calcite
> Issue Type: Bug
> Reporter: Xurenhe
> Assignee: Xurenhe
> Priority: Major
>
> The rule of AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN aims to transform aggregate with distince agg-call to join, such as:
>
> {code:java}
> // origin sql
> SELECT deptno,
> job,
> count(DISTINCT ename),
> sum(DISTINCT sal)
> FROM sales.emp
> GROUP BY deptno,
> job;
> // transformed sql
> SELECT t1.deptno,
> t2.job,
> c1,
> c2
> (SELECT deptno, job, count(ename) AS c1
> FROM
> (SELECT deptno, job, ename
> GROUP BY deptno, job, ename)
> GROUP BY deptno, job) t1
> INNER JOIN
> (SELECT deptno,
> job,
> sum(sal) AS c2
> FROM
> (SELECT deptno,
> job,
> sal
> GROUP BY deptno,
> job,
> sal)
> GROUP BY deptno,
> job) t2
> ON t1.deptno IS NOT DISTINCT FROM t2.deptno AND t1.job IS NOT DISTINCTFROM t2.job;{code}
>
> *{color:#FF0000}But, {color:#172b4d}this rule throws ex, when meeting aggregate with rollup.{color}{color}*
> * *Test Case*
> {code:java}
> // org.apache.calcite.test.RelOptRulesTest#test
> @Test void test() {
> final String sql = "select deptno, job, count(distinct ename), sum(distinct sal)\n"
> + "from sales.emp group by rollup(deptno,job)";
> sql(sql)
> .withRule(CoreRules.AGGREGATE_EXPAND_DISTINCT_AGGREGATES_TO_JOIN)
> .check();
> } {code}
> * *Error Message*
> {code:java}
> type mismatch:
> ref:
> INTEGER
> input:
> INTEGER NOT NULL
> java.lang.AssertionError: type mismatch:
> ref:
> INTEGER
> input:
> INTEGER NOT NULL
> at org.apache.calcite.util.Litmus$1.fail(Litmus.java:32)
> at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:2183)
> at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:129)
> at org.apache.calcite.rex.RexChecker.visitInputRef(RexChecker.java:61)
> at org.apache.calcite.rex.RexInputRef.accept(RexInputRef.java:113)
> at org.apache.calcite.rel.core.Project.isValid(Project.java:245)
> at org.apache.calcite.rel.core.Project.<init>(Project.java:106)
> at org.apache.calcite.rel.logical.LogicalProject.<init>(LogicalProject.java:75)
> at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:166)
> at org.apache.calcite.rel.logical.LogicalProject.create(LogicalProject.java:143)
> at org.apache.calcite.rel.core.RelFactories$ProjectFactoryImpl.createProject(RelFactories.java:199)
> at org.apache.calcite.tools.RelBuilder.project_(RelBuilder.java:2066)
> at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1837)
> at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1820)
> at org.apache.calcite.tools.RelBuilder.project(RelBuilder.java:1792)
> at org.apache.calcite.rel.rules.AggregateExpandDistinctAggregatesRule.onMatch(AggregateExpandDistinctAggregatesRule.java:265)
> at org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:337) {code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)