Correlated subqueries that reference other tables are not supported unless they can be de-correlated using a function

Question

I have a table which looks like this

CREATE OR REPLACE TABLE `{{ ur_project_id }}.{{ ur_dataset_name }}.popular_domains` (
    `domains` STRING
);

INSERT INTO `{{ ur_project_id }}.{{ ur_dataset_name }}.popular_domains`
VALUES
 ('gmail.com'),
 ('yahoo.com'),
 ('hotmail.com'),
 ('aol.com'),
 ('hotmail.co.uk')

And then i am creating a UDF function like this one

CREATE OR REPLACE FUNCTION `{{ ur_project_id }}.{{ ur_dataset_name }}.get_email_features`(email STRING)
RETURNS STRUCT
            AS
            (STRUCT(
            (SELECT (SELECT COUNT(1) FROM `{{ ur_project_id }}.{{ ur_dataset_name }}.popular_domains` pd WHERE pd.domains = SPLIT(email, '@')[SAFE_OFFSET(1)]) AS is_popular_domain)));

And finally i am trying to pass the function created through another table containing emails

SELECT 
    `{{ ur_project_id }}.{{ ur_dataset_name }}.get_email_features`(email) 
FROM {{ table with emails }}

but unlucky i am getting :

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

there is a way to correct the UDF for not falling in this error ?

user_16929145 · Answer

The error is in the STRUCT, because you are using subqueries inside the STRUCT.

(STRUCT( (SELECT (SELECT COUNT(1)

Here is another way to do the same query using functions.

This is my sample data:

This is my query using a function named countDomains

CREATE TEMP FUNCTION countDomains(domain STRING)
AS ((
  
select counts from (
select SPLIT(mail,'@')[SAFE_OFFSET(1)] as domain,count(*) as counts
FROM `eMails` where SPLIT(mail,'@')[SAFE_OFFSET(1)]=domain
group by domain) as b
  
));

SELECT id,mails,countDomains(mails) as counts
from `domains` 
order by id

This is my output. The output are the number of domains that exist in the table domains and are in the table eMAils.

Correlated subqueries that reference other tables are not supported unless they can be de-correlated using a function

1 Answers