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

["Julian Hyde (Jira)" <ji...@apache.org>]

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)