[
https://issues.apache.org/jira/browse/CALCITE-7702?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
zzwqqq updated CALCITE-7702:
----------------------------
Description:
JoinAggregateTransposeRule produces a non-equivalent plan when the aggregate
with empty input empty group set
For example, using the SCOTT schema:
{code:sql}
select g.emp_count, d.deptno
from (select count(*) as emp_count from emp where false) g
join (select deptno from dept where deptno = 10) d on true;
{code}
The query returns:
{code:java}
emp_count | deptno
-----------+--------
0 | 10
(1 row)
{code}
The rewritten plan has the same behavior as follow，and returns no rows:
{code:sql}
select count(*) as emp_count, d.deptno
from (select * from emp where false) e
join (select deptno from dept where deptno = 10) d on true
group by d.deptno;
{code}
COUNT without GROUP BY returns one row for empty input. After the aggregate is
pulled above the join, DEPTNO becomes a group key. An aggregate with a group
key returns no rows for empty input.
The rule should not apply in this case unless the aggregate input is known to
be non-empty.
was:
JoinAggregateTransposeRule produces a non-equivalent plan when the aggregate
with empty input empty group set
For example, using the SCOTT schema:
{code:sql}
select g.emp_count, d.deptno
from (select count(*) as emp_count from emp where false) g
join (select deptno from dept where deptno = 10) d on true;
{code}
The query returns:
{code:java}
emp_count | deptno
-----------+--------
0 | 10
(1 row)
{code}
The rewritten plan has the same behavior as follow，and returns no rows:
{code:sql}
select count(*) as emp_count, d.deptno
from (select * from emp where false) e
join (select deptno from dept where deptno = 10) d on true
group by d.deptno;
}
{code}
COUNT without GROUP BY returns one row for empty input. After the aggregate is
pulled above the join, DEPTNO becomes a group key. An aggregate with a group
key returns no rows for empty input.
The rule should not apply in this case unless the aggregate input is known to
be non-empty.
> JoinAggregateTransposeRule produces a non-equivalent plan when the aggregate
> with empty input and empty group set
> -----------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7702
> URL: https://issues.apache.org/jira/browse/CALCITE-7702
> Project: Calcite
> Issue Type: Bug
> Reporter: zzwqqq
> Assignee: zzwqqq
> Priority: Major
> Labels: pull-request-available
>
> JoinAggregateTransposeRule produces a non-equivalent plan when the aggregate
> with empty input empty group set
> For example, using the SCOTT schema:
> {code:sql}
> select g.emp_count, d.deptno
> from (select count(*) as emp_count from emp where false) g
> join (select deptno from dept where deptno = 10) d on true;
> {code}
> The query returns:
> {code:java}
> emp_count | deptno
> -----------+--------
> 0 | 10
> (1 row)
> {code}
> The rewritten plan has the same behavior as follow，and returns no rows:
> {code:sql}
> select count(*) as emp_count, d.deptno
> from (select * from emp where false) e
> join (select deptno from dept where deptno = 10) d on true
> group by d.deptno;
> {code}
> COUNT without GROUP BY returns one row for empty input. After the aggregate
> is pulled above the join, DEPTNO becomes a group key. An aggregate with a
> group key returns no rows for empty input.
> The rule should not apply in this case unless the aggregate input is known to
> be non-empty.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
