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.