BigQuery中WHERE子句使用CTE过滤失效问题排查

最可能的问题是列名歧义：你的子查询select id from cte1 limit 1里的id没有指定归属，数据库解析时会优先绑定外层查询中dataset.table1的id列，导致子查询逻辑变成了从cte1中选取外层表的id值，这显然不符合预期，进而引发查询错误。

解决办法 #

方法1：明确指定CTE的列别名 #

给子查询里的id加上CTE的前缀，让数据库明确知道要取CTE里的id：

with cte1 as (
  select 1 as id
)

select 
  count(*)
from dataset.table1
where
 id= (select cte1.id from cte1 limit 1)

方法2：简化CTE引用（更高效） #

因为你的CTE只返回单一行单列数据，完全可以去掉子查询嵌套，直接用CTE的列匹配，甚至不需要limit 1：

with cte1 as (
  select 1 as id
)

select 
  count(*)
from dataset.table1, cte1
where
 dataset.table1.id = cte1.id

或者用更规范的JOIN写法：

with cte1 as (
  select 1 as id
)

select 
  count(*)
from dataset.table1
join cte1 on dataset.table1.id = cte1.id

方法3：给CTE的列起独特别名 #

如果不想加前缀，也可以给CTE里的列换个和外层表不重复的名字，从根源避免歧义：

with cte1 as (
  select 1 as filter_id
)

select 
  count(*)
from dataset.table1
where
 id= (select filter_id from cte1 limit 1)

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