[CALCITE-4334] LITERAL_AGG, an aggregate function that returns a constant value

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.35.0
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: Julian Hyde
• Created: 12/Oct/20 21:57
• Updated: 20/Aug/23 06:51
• Resolved: 28/May/23 04:27

Description

It would be useful to have an aggregate function that returns a constant value, regardless of how many rows are in the group. We propose LITERAL_AGG, an internal aggregate function. As an aggregate function it has no arguments (meaning that it does not read any columns from the input), but a call will have a RexLiteral (constant expression).

This aggregate function is internal, so there is no SQL syntax. But if I were to write

SELECT deptno, SUM(sal), true
FROM Emp
GROUP BY deptno

I should get the following plan:

Aggregate(group={deptno}, aggCalls=SUM($4), LITERAL_AGG(true))
  Scan(Emp)

Today, the plan would require an extra Project to add RexLiteral(true). Planner rules have to look for a Project of a literal on top of an Aggregate; by removing the Project we simplify planning.

For example, when rewriting sub-queries (see SubQueryRemoveRule) we introduce add "true as indicator" to the SELECT clause of sub-queries. It can be used to detect rows generated by an outer join. If it is an aggregate query, we would have to write "min(true) as indicator", which necessitates an extra Project below the Aggregate to provide the "true" value. The LITERAL_AGG aggregate function allows us to avoid the extra Project.

Another example came up during CALCITE-4317. We would like to make RelBuilder.aggregate(groupKey()) throw when given an empty group key and no aggregate calls. (Because it would create an Aggregate that has zero fields, and that is problematic elsewhere in Calcite.) But we would also like a pattern where an aggregate with an empty group key becomes a constant single-row relational expression. So, RelBulder.aggregate(groupKey(), aggregateCall(LITERAL_AGG(true))) should generate VALUES TRUE.

LITERAL_AGG uses interface SqlStaticAggFunction, an interface that can optionally be implemented (or wrapped, via SqlAggFunction.unwrap(Class)), to make the planner and code-generator aware of its properties.

The implementation adds a List<RexNode> rexList field to AggregateCall. This field is empty in all conventional aggregate functions, and has one element in LITERAL_AGG.

Issue Links

• is related to: CALCITE-4597 Allow RelNodes to have an empty row type (zero fields)
• is related to: CALCITE-4763 add EXISTS_AGG aggregate function
• is related to: CALCITE-5941 Support LITERAL_AGG in Interpreter
• relates to: CALCITE-4317 Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the fields
• links to: GitHub Pull Request #3209