[CALCITE-5802] In RelBuilder add method aggregateRex, to allow aggregating complex expressions such as "1 + SUM(x + 2)"

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.38.0
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 27/Jun/23 01:36
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

Description

In RelBuilder add method aggregateRex, 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 aggregateRex: note b.call(SqlStdOperatorTable.SUM, ... in the code.

For example, to create the same effect as SQL

SELECT deptno,
    deptno + 2 AS d2,
    3 + SUM(4 + sal) AS s
FROM emp
GROUP BY deptno

we use the RelBuilder code

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();

and the resulting relational expression is

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]])

Issue Links

• is related to: CALCITE-4496 Measure columns ("SELECT ... AS MEASURE")