为何BigQuery查询仅在设置表别名后生效？报'Unrecognized name'错误

执行以下查询时出现Unrecognized name: bigquery at [8:8]错误：

WITH longest_used_bike AS(
  SELECT bike_id,SUM(duration_minutes) AS trip_duration
  FROM bigquery-public-data.austin_bikeshare.bikeshare_trips
  GROUP BY bike_id
  ORDER BY trip_duration DESC
  LIMIT 1
)

SELECT 
  bigquery-public-data.austin_bikeshare.bikeshare_trips.start_station_id, 
  COUNT(*) AS trip_ct
FROM bigquery-public-data.austin_bikeshare.bikeshare_trips
JOIN longest_used_bike
  ON longest_used_bike.bike_id = bigquery-public-data.austin_bikeshare.bikeshare_trips.bike_id
GROUP BY start_station_id
ORDER BY trip_ct DESC
LIMIT 1

为表设置别名后，查询可正常运行：

WITH longest_used_bike AS (
  SELECT bike_id,SUM(duration_minutes) AS trip_duration
  FROM bigquery-public-data.austin_bikeshare.bikeshare_trips
  GROUP BY bike_id
  ORDER BY trip_duration DESC
  LIMIT 1
)

SELECT
  trips.start_station_id,
  COUNT(*) AS trip_ct
FROM bigquery-public-data.austin_bikeshare.bikeshare_trips as trips
JOIN longest_used_bike as longest
  ON longest.bike_id = trips.bike_id
GROUP BY start_station_id
ORDER BY trip_ct DESC
LIMIT 1

原因解释 #

• BigQuery的SQL解析器在处理带连字符的完整表路径（比如bigquery-public-data.austin_bikeshare.bikeshare_trips）时，会把连字符误判为减号运算符，导致它将bigquery当成一个未定义的变量，从而抛出Unrecognized name: bigquery错误。
• 表别名是无特殊字符的标识符，SQL解析器可以直接识别它作为表的引用，彻底避免了连字符带来的语法歧义。
• 如果不想使用别名，也可以用反引号包裹完整表路径，比如`bigquery-public-data.austin_bikeshare.bikeshare_trips`.start_station_id，这样解析器就能正确识别这是一个完整表名，而非运算表达式。

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