Error: “Tabular UDFs must not be correlated” When Creating a Function

Summary

When creating a tabular user-defined function (UDF) in Dremio, the following error may appear:

Tabular UDFs must not be correlated.

This error occurs when the function’s internal query references external (outer) query columns, which is considered a correlated subquery — a pattern currently unsupported in Dremio for tabular UDFs.

Relevant Versions

All Dremio Active Versions

Troubleshooting Steps

• Review the function definition and check whether it includes references to columns from the outer query inside the UDF body.
• Temporarily remove WHERE clauses or filters that may be introducing correlation and test the function again.
• Check for view dependencies used within the function. A correlated subquery may be introduced indirectly via a view that references outer scope fields.
• Confirm behavior by reviewing the query profile and verifying whether correlation is flagged during planning.

Cause

Dremio currently does not support correlated tabular UDFs. A correlated subquery exists when a subquery (or the body of a UDF) references one or more columns from the outer query scope. This restriction applies to ensure query planning and execution integrity for reusable function logic.

Steps to Resolve

To resolve this error:

• Refactor the function to eliminate any references to outer query fields inside the UDF.
• Update any dependent views used inside the UDF to ensure they do not include correlations or outer-scope dependencies.

• Here’s an example refactor:

  Problematic Pattern:

  CREATE FUNCTION my_func() RETURNS TABLE AS
  SELECT * FROM some_table WHERE some_table.col1 = outer_table.col2

  Correct pattern (non-correlated):

  CREATE FUNCTION my_func(input_val VARCHAR) RETURNS TABLE AS
  SELECT * FROM some_table WHERE some_table.col1 = input_val;

  Then use:

  SELECT * FROM outer_table, TABLE(my_func(outer_table.col2));

Next Steps

• Ensure future tabular UDFs are written in a non-correlated way.
• Consider using scalar UDFs if outer-column references are essential.
• If a similar error occurs, review the full query and query plan to identify correlation patterns.

Additional Resources

https://docs.dremio.com/current/reference/api/catalog/user-defined-function/