BigQuery日期分区表过滤异常：误用DATETIME_SUB致全表扫描

问题原因 #

1. 函数类型不匹配导致计算结果偏差 #

BigQuery中DATE_SUB和DATETIME_SUB是针对不同数据类型的专用函数：

• DATE_SUB接收并返回DATE类型（格式如YYYY-MM-DD），对传入的日期字符串会自动按DATE规则解析计算。
• DATETIME_SUB接收并返回DATETIME类型（格式如YYYY-MM-DDTHH:MM:SS），当传入DATE格式的字符串"2023-06-06"时，BigQuery会隐式将其转换为DATETIME类型"2023-06-06T00:00:00"，再执行减7天操作，最终得到2023-05-30T00:00:00，与预期的DATE类型结果不符。

2. 隐式类型转换导致分区裁剪失效 #

表T是按DATE类型的date_part字段分区的。当用DATETIME类型的结果与DATE类型的date_part比较时，BigQuery会对date_part做隐式转换（将DATE转为DATETIME）。这种转换会破坏BigQuery对分区过滤条件的识别逻辑，使其无法判断这是针对DATE分区的范围查询，因此无法触发分区裁剪，只能执行全表扫描。

3. 为何未返回空数据 #

BigQuery的隐式类型转换是双向兼容的：DATE类型的date_part会被转为对应时间零点的DATETIME值（比如2023-05-30转为2023-05-30T00:00:00），与DATETIME_SUB返回的结果比较时，符合>=条件的分区数据会被正常匹配，因此不会返回空数据。

优化建议 #

• 严格匹配函数与字段类型：针对DATE类型的分区字段，始终使用DATE_SUB、DATE_ADD等DATE系列函数，避免误用DATETIME系列函数。
• 显式转换数据类型：如果必须使用DATETIME函数处理DATE值，显式转换类型确保匹配，例如：

  SELECT * FROM T WHERE date_part >= DATE(DATETIME_SUB("2023-06-06", INTERVAL 7 DAY))
  

• 提前验证函数结果：编写查询前，先单独执行函数部分确认结果是否符合预期，例如：

  SELECT DATE_SUB("2023-06-06", INTERVAL 7 DAY) AS correct_date,
         DATETIME_SUB("2023-06-06", INTERVAL 7 DAY) AS wrong_datetime
  

• 检查查询计划：执行查询前查看BigQuery的查询计划，确认是否触发了分区裁剪。如果显示全表扫描，及时排查过滤条件中的类型不匹配问题。

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