The grammar admits an {ALL|DISTINCT} set quantifier on every aggregate function call, but semantic analysis currently rejects DISTINCT:
SELECT ARRAY_AGG(DISTINCT val) FROM T1
SELECT SUM(DISTINCT val) FROM T1
-- ERROR: aggregator DISTINCT is not supported
The rejection is in ExpressionVisitor.visitAggregateWindowedFunction() and applies uniformly to ARRAY_AGG(), SUM(), AVG(), MIN(), MAX(), COUNT() and GROUP_CONCAT().
What is needed:
- De-duplicate the argument values within each group before they reach the aggregate function. For the scalar aggregates this changes the result of
SUM(), AVG() and COUNT(); for MIN() and MAX() it is a no-op, so those could short-circuit to the plain form.
- Decide where the de-duplication happens. For streaming aggregation, if the input is already sorted on the argument, it can drop adjacent duplicates cheaply. One approach is to generate appropriate ordering requirements for the inputs.
Related:
The grammar admits an
{ALL|DISTINCT}set quantifier on every aggregate function call, but semantic analysis currently rejectsDISTINCT:The rejection is in
ExpressionVisitor.visitAggregateWindowedFunction()and applies uniformly toARRAY_AGG(),SUM(),AVG(),MIN(),MAX(),COUNT()andGROUP_CONCAT().What is needed:
SUM(),AVG()andCOUNT(); forMIN()andMAX()it is a no-op, so those could short-circuit to the plain form.Related:
SELECT DISTINCTis accepted by the parser but ignored #4305 covers the query-levelSELECT DISTINCT, which is a different code path.