[
https://issues.apache.org/jira/browse/CALCITE-7386?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18061238#comment-18061238
]
Silun Dong commented on CALCITE-7386:
-------------------------------------
In fact, this issue is unrelated to the new decorrelation algorithm. In
{{{}TopDownGeneralDecorrelator{}}}, there is preprocessing and postprocessing
around decorrelation, and both of these use the {{FILTER_PROJECT_TRANSPOSE}}
rule. For the test case mentioned above, the {{Filter}} is pushed down under a
{{Project}} that contains a {{V2M}} expression, which produces an incorrect
result; if the {{FILTER_PROJECT_TRANSPOSE}} rule is removed, the result is
correct. I'm not familiar with the measure feature, and the root cause seems to
be a conflict between some rules and measure feature.
I temporarily modified {{CoreQuidemTest2}} to skip this test in
[https://github.com/apache/calcite/commit/87dcba9f0eebb1820f78ef8655045f89938ebd17]
Thanks for your reviews [~jensen] [~mbudiu]
As discussed in the [PR#4806|https://github.com/apache/calcite/pull/4806], this
issue will remain open for now until the problem is properly resolved.
> An error occurred while using TopDownGeneralDecorrelator to process the
> aggregate(col) filter
> ---------------------------------------------------------------------------------------------
>
> Key: CALCITE-7386
> URL: https://issues.apache.org/jira/browse/CALCITE-7386
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Zhen Chen
> Priority: Major
> Labels: pull-request-available
>
> An error will occur when executing the following SQL in CoreQuidemTest2.
> {code:java}
> # Measure with FILTER
> select job,
> c,
> aggregate(avg_sal) as avg_sal,
> aggregate(c) filter (where deptno = 20) as c2,
> aggregate(avg_sal) filter (where deptno = 20) as avg_sal2
> from (
> select *, count(*) as measure c, avg(sal) as measure avg_sal
> from "scott".emp
> where job <> 'PRESIDENT')
> where deptno < 25
> group by job;
> +---------+---+---------+----+----------+
> | JOB | C | AVG_SAL | C2 | AVG_SAL2 |
> +---------+---+---------+----+----------+
> | ANALYST | 2 | 3000.00 | 2 | 3000.00 |
> | CLERK | 4 | 1037.50 | 2 | 950.00 |
> | MANAGER | 3 | 2758.33 | 1 | 2975.00 |
> +---------+---+---------+----+----------+
> (3 rows)!ok {code}
> The result is error.
> < | CLERK | 4 | 1037.50 | 2 | 950.00 |
> < | MANAGER | 3 | 2758.33 | 1 | 2975.00 |
> ---
> > | CLERK | 3 | 1066.66 | 2 | 950.00 |
> > | MANAGER | 2 | 2712.50 | 1 | 2975.00 |
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
