BigQuery使用临时函数执行UPDATE语句触发左外连接错误

报错原因 #

这个报错和你的函数本身逻辑无关，是BigQuery对DML语句的隐式关联规则限制导致的：
你定义的临时函数内部包含了对bigquery-public-data.new_york_taxi_trips.taxi_zone_geom表的子查询，当你在UPDATE语句的SET子句中调用该函数时，BigQuery的查询优化器会自动将该逻辑转换为主表collated_data和taxi_zone_geom表的左关联操作。而你关联的判断条件是空间函数ST_DWithin，不属于两张表字段的等值匹配条件，就触发了BigQuery DML语句不支持非等值关联的限制，因此报出连接相关的错误。
单独执行SELECT调用函数没有问题，是因为普通SELECT查询没有DML的这类关联限制，标量子查询可以正常逐行计算返回结果。

解决方案 #

方案1：直接写关联逻辑执行UPDATE #

无需封装函数调用，直接把空间匹配逻辑写到UPDATE的FROM子句中，是最简单的写法：

UPDATE `project-id.datasetid.collated_data` main
SET NEIGHBORHOOD = UPPER(tz_loc.borough)
FROM `bigquery-public-data.new_york_taxi_trips.taxi_zone_geom` tz_loc
WHERE 
  main.collision_date = "2019-10-27" 
  AND main.LAT = 40.705246 
  AND main.LONG = -73.95908
  AND ST_DWithin(tz_loc.zone_geom, ST_GeogPoint(main.LONG, main.LAT), 0)

方案2：通过临时表中转实现等值关联更新 #

如果你需要复用匹配逻辑，可以先把匹配结果存入临时表，再通过等值关联执行UPDATE，完全符合BigQuery的DML规则：

-- 先查询匹配结果存入临时表
CREATE TEMP TABLE matched_borough AS
SELECT 
  main.collision_date,
  main.LAT,
  main.LONG,
  UPPER(tz_loc.borough) AS borough
FROM `project-id.datasetid.collated_data` main
JOIN `bigquery-public-data.new_york_taxi_trips.taxi_zone_geom` tz_loc
ON ST_DWithin(tz_loc.zone_geom, ST_GeogPoint(main.LONG, main.LAT), 0)
WHERE 
  main.collision_date = "2019-10-27" 
  AND main.LAT = 40.705246 
  AND main.LONG = -73.95908;

-- 用临时表和主表等值关联完成更新
UPDATE `project-id.datasetid.collated_data` main
SET NEIGHBORHOOD = mb.borough
FROM matched_borough mb
WHERE 
  main.collision_date = mb.collision_date
  AND main.LAT = mb.LAT
  AND main.LONG = mb.LONG;

内容的提问来源于stack exchange，提问作者Joseph