Limitations - Aster Execution Engine

There are several restrictions:

• You cannot use user defined functions written in C and Java in a single statement. There are some exceptions, such as if one appears at the top level of a statement and the other appears in a sub-select.
• You cannot create a view based on a query using a user defined function.
• You cannot put more than one user defined function written in Java in the same jar file.

For the most part, UDSFs and UDAFs can appear anywhere in a SQL statement that their corresponding native expressions can appear. For UDSFs, this means that they can appear anywhere a scalar expression can appear and for UDAFs, this means that they can appear anywhere a native aggregate can appear. However, there are some restrictions in the placement of UDSFs and UDAFs in a SQL statement. UDSFs or UDAFs cannot appear in the following contexts:

• Any expression of a CREATE TABLE statement, including:
  • the default clause
  • the check constraint expressions
  • the range or list expression of a partitioning clause
• Any expression or predicate of a CREATE INDEX statement
• Any expression in the values clause of an INSERT INTO ... VALUES statement
• Any expression in the MERGE statement
• Any expression in the UPDATE statement
• Any expression in the DELETE statement
• Any expression that is part of a CREATE VIEW statement

UDFs or UDAs appearing in these contexts will result in an error message. For example:

merge into test3 t3
using test2 t2
on t3.key = t2.key
when matched then
update set t3.d1 = average(t2.d1)
when not matched then
insert (t3.key, t3.d1, t3.d2)
values (t2.key, t2.d1, t2.d2);
ERROR: SQL-Analytics function average is not supported in UPDATE/MERGE/DELETE statements