[CASSANDRA-18060] Add aggregation scalar functions on collections

• Type: New Feature
• Status: Resolved
• Priority: Normal
• Resolution: Fixed
• Fix Version/s: 5.0-alpha1, 5.0
• Component/s: CQL/Semantics
• Change Category: Semantic
• Complexity: Normal
• Platform: All

Source Control Link

https://github.com/apache/cassandra/commit/b7c7972a51ab6be6e5f410d2b12c770f5b7ebc98

Test and Documentation Plan

PR	CI
trunk	j8 j11

• Assignee: Andres de la Peña
• Reporter: Andres de la Peña

Authors

Andres de la Peña

Reviewers

Berenguer Blasi

• Created: 18/Nov/22 10:58
• Updated: 12/Sep/23 13:03
• Resolved: 25/Nov/22 14:12

Description

The new mechanism for dynamically building native functions introduced by CASSANDRA-17811 can be used to provide within-collection aggregation functions. We can use that mechanism to add new CQL functions to get:

• The number of items in a collection.
• The max/min items of a collection.
• The sum/avg of the items of a numeric collection.
• The keys or the values of a map.

For example:

CREATE TABLE k.t (k int PRIMARY KEY, l list<int>, m map<int, int>);
INSERT INTO t(k, l, m) VALUES (0, [1, 2, 3], {1:10, 2:20, 3:30});

> SELECT map_keys(m), map_values(m) FROM t;

 system.map_keys(m) | system.map_values(m)
--------------------+----------------------
          {1, 2, 3} |         [10, 20, 30]

> SELECT collection_count(m), collection_count(l) FROM t;

 system.collection_count(m) | system.collection_count(l)
----------------------------+----------------------------
                          3 |                          3

> SELECT collection_min(l), collection_max(l) FROM t;

 system.collection_min(l) | system.collection_max(l)
--------------------------+--------------------------
                        1 |                        3

> SELECT collection_sum(l), collection_avg(l) FROM t;

 system.collection_sum(l) | system.collection_avg(l)
--------------------------+--------------------------
                        6 |                        2

Note that this type of aggregation is different from the kind of aggregation provided by min, max, sum and avg, which aggregate entire collections across rows. Here we only aggregate the items of a collection row per row.

Issue Links

• is a parent of: CASSANDRA-18074 Add CQL cast functions for collections
• Parent Feature: CASSANDRA-17811 Fix CQL aggregation functions for collections, tuples and UDTs
• relates to: CASSANDRA-18085 Add support for singletons on CQL collection functions
• Testing discovered: CASSANDRA-18065 Flaky test org.apache.cassandra.tools.TopPartitionsTest#testStartAndStopScheduledSampling
• Testing discovered: CASSANDRA-18073 AVG CQL function of an empty set of values returns zero