Persistent UDF Issue in BigQuery

Question

Im creating a Persistent UDF in Bigquery.

CREATE OR REPLACE FUNCTION `project.dataset.get_names_function`(x STRING) RETURNS STRING AS (
(
select string_agg( c.company_name,' | ') final_names
from `project.dataset.table1` b
inner join unnest(split(x, "|")) gbi 
ON (gbi = cast(b.globid as string)),
unnest(b.ind_ids) gid
inner join `project.dataset.table2` c
ON (gid = c.globind)
)
);

When i try to invoke it using hardcoded values it works fine.

SELECT `project.dataset.get_names_function`("12345|67890");

Result:
Managed Services | Combined Policy

But when i add it as part of select query of another table

select 
   `project.dataset.get_names_function`(product_ids)
from `project.dataset.test_table1`

it errors out stating:

Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

Thanks you in advance for solutions.

Answer 1

Since it works when hardcoded, have you tried using DECLARE and storing the results ahead of time? That way we can bypass the runtime evaluation that is causing the error.

Try:

DECLARE product_ids STRING DEFAULT (SELECT product_ids from project.dataset.test_table1)

And then pass this into the function.

Answer 2

Currently, BigQuery does not support correlated subqueries.

These kinds of issues normally stem from the way the query you're trying to run was written. Sometimes queries break when they make many references to other tables in a cumbersome way. If you add ORDER BY, LIMIT, EXISTS, NOT EXISTS, IN (table) clauses into correlated subqueries then most likely it won't work.

Normally re-writing the query in a more straightforward way or using less join statements can work. More information on how the join clause works can be found in this documentation. Another workaround for correlated subquery issues is to use BigQuery's new scripting interface which will let you CREATE TEMP TABLE on your substeps.

However, there is a feature request filed for the same. You can vote for this feature by clicking the +1 and STAR mark to receive updates on it.