Julian Hyde created CALCITE-5802: ------------------------------------ Summary: 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
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)