I've got the following (anonymized) code. When I add the labeled line, the Bigquery execution time goes from 7 seconds of wallclock time to more than several minutes at least, showing no signs of stopping. Any ideas why?
SELECT
*
FROM
table1
LEFT JOIN
table2
ON
LEFT(table2.first_name,3) = UPPER(LEFT(table1.first_name,3))
AND dataset.commons.FuzzyDamerauLevenshtein(UPPER(table1.last_name),
table2.last_name) < 2
AND table2.voting_city = table1.city
AND
(
/* this line */
(table1.address is null) OR
/* the line above */
(LEFT(table2.voting_street_address,GREATEST(0, LENGTH(table2.voting_street_address)-4))
= LEFT(table1.address, GREATEST(0, LENGTH(table1.address)-4))
)
)
JOIN `dataset.crosswalks.personid_to_id` table3
ON table2.person_id = table3.person_id
ORDER BY
table2.last_name;