[
https://issues.apache.org/jira/browse/CALCITE-733?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-733:
--------------------------------
Description:
The query {code}select "department_id" as d, count(distinct "education_level")
as c1, count(distinct "gender") as c2 from foodmart_clone."employee" group by
"department_id";{code} returns 0 rows and should return 12. In the plan
{noformat}EnumerableCalc(expr#0..3=[{inputs}], department_id=[$t2], C1=[$t3],
C2=[$t1])
EnumerableJoin(condition=[=($0, $2)], joinType=[inner])
EnumerableAggregate(group=[{0}], groups=[[{7}]], C2=[COUNT($1)])
EnumerableAggregate(group=[{7, 15}])
EnumerableTableScan(table=[[FOODMART_CLONE, employee]])
EnumerableAggregate(group=[{0}], groups=[[{7}]], C1=[COUNT($1)])
EnumerableAggregate(group=[{7, 13}])
EnumerableTableScan(table=[[FOODMART_CLONE, employee]])
{noformat} you can see {noformat}group=[{0}], groups=[[{7}]]{noformat} and this
is wrong -- the groups should be made up of the same bits as the group. We
should add an assert on this invariant and fixing it will probably cause the
plan to return the right results.
was:
The query {code}select "department_id" as d, count(distinct "education_level")
as c1, count(distinct "gender") as c2 from foodmart_clone."employee" group by
"department_id";{code} returns 0 rows and should return 12. In the plan
{noformat}EnumerableCalc(expr#0..3=[{inputs}], department_id=[$t2], C1=[$t3],
C2=[$t1])
EnumerableJoin(condition=[=($0, $2)], joinType=[inner])
EnumerableAggregate(group=[{0}], groups=[[{7}]], C2=[COUNT($1)])
EnumerableAggregate(group=[{7, 15}])
EnumerableTableScan(table=[[FOODMART_CLONE, employee]])
EnumerableAggregate(group=[{0}], groups=[[{7}]], C1=[COUNT($1)])
EnumerableAggregate(group=[{7, 13}])
EnumerableTableScan(table=[[FOODMART_CLONE, employee]])
{noformat} you can see {{group=[{0}], groups=[[{7}]] }} and this is wrong --
the groups should be made up of the same bits as the group. We should add an
assert on this invariant and fixing it will probably cause the plan to return
the right results.
> Multiple distinct-COUNT query gives wrong results
> -------------------------------------------------
>
> Key: CALCITE-733
> URL: https://issues.apache.org/jira/browse/CALCITE-733
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Julian Hyde
>
> The query {code}select "department_id" as d, count(distinct
> "education_level") as c1, count(distinct "gender") as c2 from
> foodmart_clone."employee" group by "department_id";{code} returns 0 rows and
> should return 12. In the plan {noformat}EnumerableCalc(expr#0..3=[{inputs}],
> department_id=[$t2], C1=[$t3], C2=[$t1])
> EnumerableJoin(condition=[=($0, $2)], joinType=[inner])
> EnumerableAggregate(group=[{0}], groups=[[{7}]], C2=[COUNT($1)])
> EnumerableAggregate(group=[{7, 15}])
> EnumerableTableScan(table=[[FOODMART_CLONE, employee]])
> EnumerableAggregate(group=[{0}], groups=[[{7}]], C1=[COUNT($1)])
> EnumerableAggregate(group=[{7, 13}])
> EnumerableTableScan(table=[[FOODMART_CLONE, employee]])
> {noformat} you can see {noformat}group=[{0}], groups=[[{7}]]{noformat} and
> this is wrong -- the groups should be made up of the same bits as the group.
> We should add an assert on this invariant and fixing it will probably cause
> the plan to return the right results.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
