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