Usage Notes | ARRAY_AGG | Teradata Vantage - 17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Data Types and Literals

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

Because you can pass any array_expression, the ARRAY type value that you pass may or may not contain any initialized elements. However, ARRAY_AGG ignores any initialized elements in the array_expression. The array_expression is used only for providing the data type of the result value.

Recommendation: Pass a blank instance of the desired ARRAY type value for this parameter, such as NEW myArray().

If the array_expression argument passed to ARRAY_AGG is a NULL literal, then an error is returned. If the array_expression argument can be resolved to an ARRAY data type, then the function executes normally, even if the data contains a NULL.

If the value_expression argument for the current row being processed evaluates to NULL, then that expression will not be eliminated from the list of values being aggregated, but will be used to set the appropriate ARRAY element value just as would be done with a non-NULL element. This follows the ANSI SQL:2011 standard and is noted in the standard as a difference between ARRAY_AGG and other aggregate functions.

ARRAY_AGG is created with a CLASS AGGREGATE value of 64000, meaning that the intermediate aggregate storage will be allocated at that size. Since the intermediate aggregate storage is a fixed-length value, it may be possible in cases of processing a very large input set that this buffer may overflow. All aggregate UDFs have this limitation.