Krisztian Kasa created CALCITE-7851:
---------------------------------------
Summary: AggregateJoinTransposeRule references wrong column when
an aggregate argument is a group key of a unique join input
Key: CALCITE-7851
URL: https://issues.apache.org/jira/browse/CALCITE-7851
Project: Calcite
Issue Type: Bug
Components: core
Reporter: Krisztian Kasa
The issue is reproducible when the {{AggregateJoinTransposeRule}}
* allows aggregate functions
* a join input is already unique on its below-aggregate key.
{code:java}
@Test void testPushAggregateThroughJoinAggArgIsGroupKeyOfUniqueSide() {
final String sql = "select d.id1, d.id2, d.age, sum(d.age), min(d.age)\n"
+ "from sales.double_pk as d\n"
+ "join sales.emp as e on d.id1 = e.deptno\n"
+ "group by d.id1, d.id2, d.age";
sql(sql)
.withPreRule(CoreRules.AGGREGATE_PROJECT_MERGE)
.withRule(CoreRules.AGGREGATE_JOIN_TRANSPOSE_EXTENDED)
.check();
}
{code}
Expected plan
{code}
LogicalProject(ID1=[$0], ID2=[$1], AGE=[$2], $f5=[CAST(*($2, $4)):INTEGER NOT
NULL], AGE0=[$2])
LogicalJoin(condition=[=($0, $3)], joinType=[inner])
LogicalProject(ID1=[$0], ID2=[$1], AGE=[$3])
LogicalTableScan(table=[[CATALOG, SALES, DOUBLE_PK]])
LogicalAggregate(group=[{7}], agg#0=[COUNT()])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
Actual plan
{code}
LogicalProject(ID1=[$0], ID2=[$1], AGE=[$2], $f5=[CAST(*($3, $4)):INTEGER NOT
NULL], DEPTNO=[$3])
LogicalJoin(condition=[=($0, $3)], joinType=[inner])
LogicalProject(ID1=[$0], ID2=[$1], AGE=[$3])
LogicalTableScan(table=[[CATALOG, SALES, DOUBLE_PK]])
LogicalAggregate(group=[{7}], agg#0=[COUNT()])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
In the Project rel in the top the DEPTNO column is referenced instead of AGE
which leads to incorrect results.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
