Bigquery takes a lot longer when a null test is added to JOIN condition

Viewed 36

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;
0 Answers
Related