[ https://issues.apache.org/jira/browse/CALCITE-5802?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Hyde updated CALCITE-5802: --------------------------------- Summary: In RelBuilder add method aggregateRex, to allow aggregating complex expressions such as "1 + SUM(x + 2)" (was: In RelBuilder add method aggregateExtended, to allow aggregating complex expressions such as "1 + SUM(x + 2)") > In RelBuilder add method aggregateRex, 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)