random_value UDF result is re-used by query optimizer when invoked in subquery

[danieldeleo]

BigQuery query optimizer will invoke once and re-use the output of random_value UDF if the UDF is called within a SQL subquery.

https://github.com/GoogleCloudPlatform/bigquery-utils/blob/master/udfs/community/random_value.sql

Possible Solution:

random_value(arr ANY TYPE, col ANY TYPE) AS
(
(
SELECT val[OFFSET(0)]
FROM(
SELECT
[value, col] AS val
FROM
UNNEST(arr) value
ORDER BY
RAND()
LIMIT 1
)
));

Caveat: the second input arg must have the same type as the array elements in first arg