Informix Error -9401: Cannot re-define or drop builtin aggregate <aggregate-name>. </aggregate-name>

Cause and resolution

Cannot re-define or drop builtin aggregate <aggregate-name>. </aggregate-name>

The named aggregate is a system-defined aggregate. You can overload the functions that support the built-in aggregates, but you cannot redefine or drop built-in aggregates.

Create support functions for the named aggregate but do not use the CREATE AGGREGATE statement.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-9401 fires when an attempt is made to redefine or drop a system-defined (built-in) aggregate — per the official guidance.

A CREATE or DROP AGGREGATE statement targeting a built-in aggregate, per the official guidance — the direct, only cause; built-in aggregates can't be redefined or dropped, only their support functions can be overloaded.

Solutions / Resolution

Create support functions for the named aggregate instead of using CREATE AGGREGATE, per the official guidance — overload the functions that support the built-in aggregate.

Examples

Attempting to redefine SUM

CREATE AGGREGATE sum WITH (...);   -- -9401: sum is a built-in aggregate

Corrected — overloading the support function instead

CREATE FUNCTION plus(c1 new_type, c2 new_type) RETURNING new_type
   ...;

Diagnostic Checks

Check whether the target aggregate name is one of the built-in aggregates, and use CREATE FUNCTION to overload its support functions instead of CREATE/DROP AGGREGATE.

Related Errors / Related Topics

-9400 — "User-defined aggregate already exists." A related aggregate-naming error, on name collision with a user-defined aggregate or routine.
-9406 — "Cannot resolve support function for user-defined aggregate ." A related aggregate-support-function error, on a missing rather than forbidden redefinition.

Built-in aggregates can't be redefined or dropped — overload their support functions with CREATE FUNCTION instead.