[IGNITE-22252] Sql. Implement aggregate functions according to standard Feature T621

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.0.0-beta1
• Component/s: sql
• Labels: ignite-3
• Assignee: Unassigned
• Reporter: Evgeny Stanilovsky
• Created: 16/May/24 07:12
• Updated: 16/May/24 11:27

Description

Standard defines  Feature T621 as support for STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP. Seems all we need to implement such functionality:

1. Register appropriate in IgniteSqlOperatorTable
2. Append IgniteSqlValidator#validateAggregateFunction
3. Define implementation here Accumulators#accumulatorFunctionFactory
4. And the main (because calcite derive incorrect return type by default) override IgniteTypeSystem.deriveAvgAggType

According to standard :

STDDEV_POP(X) is equivalent to SQRT(VAR_POP(X)).
STDDEV_SAMP(X) is equivalent to SQRT(VAR_SAMP(X)).

Thus derived type need to be double or BigDecimal if we need to use java.math.BigDecimal.sqrt

 

useful implementation info can be found here AggregateReduceFunctionsRule#reduceAgg

 

      STDDEV_POP:
        SQRT((SUM(x * x) - SUM(x) * SUM(x) / COUNT(x)) / COUNT(x))

       STDDEV_SAMP:
         SQRT((SUM(x * x) - SUM(x) * SUM(x) / COUNT(x)) / CASE COUNT(x) WHEN 1 THEN NULL ELSE COUNT(x) - 1 END)

      VAR_POP:
         (SUM(x * x) - SUM(x) * SUM(x) / COUNT(x)) / COUNT(x)

      case VAR_SAMP:
        (SUM(x * x) - SUM(x) * SUM(x) / COUNT(x)) / CASE COUNT(x) WHEN 1 THEN NULL ELSE COUNT(x) - 1 END

Also need to mention that results for appropriate muted tests (for example test_stddev.test_ignore) are not correct (sqrt return is trimmed) and pg returns a bit different results that corresponds to standard, need to fix return results too.

Issue Links

• is duplicated by: IGNITE-14642 Calcite engine. Support for standard deviation aggregate functions
• Testing discovered: IGNITE-21984 Extend test coverage for SQL T621(Enhanced numeric functions)