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/12 07:30:00 UTC
[jira] [Updated] (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:all-tabpanel ]
Xurenhe updated CALCITE-5328:
-----------------------------
Description:
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}
was:
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}
But, this rule throws ex, when meeting aggregate with rollup.
* *TestCase*
{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}
> 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)