[CALCITE-4334] TRUE_AGG, an aggregate function that always returns TRUE

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Julian Hyde
• Created: 12/Oct/20 21:57
• Updated: 02/Nov/21 19:14

Description

It would be useful to have an (internal) aggregate function that has no arguments and returns a constant value, regardless of how many rows are in the group. We suggest TRUE_AGG, which always returns the BOOLEAN value TRUE.

If there are reasons why it would be better to return other constants (such as 0, 1, FALSE or the empty string) we will consider them.

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. A TRUE_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(TRUE_AGG)) should generate VALUES TRUE.

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
• relates to: CALCITE-4317 Some rules fail to handle Aggregate node if RelFieldTrimmer trims all the fields