Solving “correlated subqueries” error in UDF

Question

I'm trying to use a UDF but it is producing a correlated subqueries error, and I'm hoping someone can help with my particular case. The error:

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

My UDF is this:

CREATE FUNCTION getTestStatus(name STRING, version STRING) AS (
ARRAY(SELECT status
    FROM test_complete_dummy_data
    WHERE release_version = version AND test_name = name
    ORDER BY result_uploaded_at DESC)[OFFSET(0)]
);

I'm using it in a query like this:

SELECT release_version, release_timestamp,
    if(
        getTestStatus('Unit tests', release_dummy_data.release_version) = 'PASS',
        'PASS',
        'FAIL'
    ) AS unit_tests,
    if(
        getTestStatus('Nightly test 1', release_dummy_data.release_version) = 'PASS'
            AND getTestStatus('Nightly test 2', release_dummy_data.release_version) = 'PASS',
        'PASS',
        'FAIL'
    ) AS nightly_tests,
FROM release_dummy_data
ORDER BY release_version DESC

If I remove the ORDER BY statement from the UDF then it works without error.

Is there a way around this?

Callum Kilby Callum Kilby · Accepted Answer · 2020-11-18T11:40:57

With some help, I got a working implementation of the function by using RANK to get the latest test result for each release_version in a subquery, and then select a specific release version from the result of that:

CREATE FUNCTION getTestStatus(name STRING, version STRING) AS (
(SELECT status
    FROM (SELECT release_version, status, RANK() OVER(PARTITION BY release_version ORDER BY result_uploaded_at DESC) rank
        FROM test_complete_dummy_data
        WHERE test_name = name
    )
    WHERE rank = 1 AND release_version = version
));

Solving “correlated subqueries” error in UDF

1 Answers