[
https://issues.apache.org/jira/browse/CALCITE-5802?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde reassigned CALCITE-5802:
------------------------------------
Assignee: Julian Hyde
> In RelBuilder add method aggregateExtended, to allow aggregating complex
> expressions such as "1 + SUM(x + 2)"
> -------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-5802
> URL: https://issues.apache.org/jira/browse/CALCITE-5802
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Julian Hyde
> Priority: Major
>
> In {{RelBuilder}} add method {{{}aggregateExtended{}}}, to allow aggregating
> complex expressions such as "1 + SUM(x + 2)". These expressions are difficult
> because there is an expression ({{{}x + 2{}}}) before the aggregate, then the
> aggregate, then an expression ({{{}1 + sum{}}}) after the aggregate. For
> complex expressions such as this, the translation requires a {{Project}}
> followed by an {{Aggregate}} followed by a {{{}Project{}}}.
> Aggregate functions are not conventionally represented as {{{}RexNode{}}},
> but we allow them in the expression passed to {{{}aggregateExtended{}}}.
> For example, to create the same effect as SQL
> {code:java}
> SELECT deptno,
> deptno + 2 AS d2,
> 3 + SUM(4 + sal) AS s
> FROM emp
> GROUP BY deptno
> {code}
> we use the {{RelBuilder}} code
> {code:java}
> RelBuilder b;
> b.scan("EMP")
> .aggregateRex(b.groupKey(b.field("DEPTNO")),
> b.field("DEPTNO"),
> b.alias(
> b.call(SqlStdOperatorTable.PLUS, b.field("DEPTNO"),
> b.literal(2)),
> "d2"),
> b.alias(
> b.call(SqlStdOperatorTable.PLUS, b.literal(3),
> b.call(SqlStdOperatorTable.SUM,
> b.call(SqlStdOperatorTable.PLUS, b.literal(4),
> b.field("SAL")))),
> "s"))
> .build();
> {code}
> and the resulting relational expression is
> {noformat}
> LogicalProject(DEPTNO=[$0], d2=[+($0, 2)], s=[+(3, $1)])
> LogicalAggregate(group=[{0}], agg#0=[SUM($1)])
> LogicalProject(DEPTNO=[$7], $f8=[+(4, $5)])
> LogicalTableScan(table=[[scott, EMP]])
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
