dbplyr调度tidyr::expand()处理多列时SQL解析错误问题咨询

这个问题的核心是旧版本dbplyr的SQL翻译逻辑与duckdb的语法要求不兼容，具体触发逻辑如下：

• tidyr::expand针对tbl_dbi类型的远程表没有单独实现，完全依赖dbplyr包完成R语法到SQL的翻译
• 单列expand场景只需要生成单表去重的SQL，无需关联操作，因此可以正常运行
• 多列expand场景需要生成多个去重子查询的笛卡尔积，dbplyr < 2.2.0版本会使用无ON条件的LEFT JOIN写法实现交叉连接，但duckdb的SQL解析器要求所有JOIN必须显式指定ON条件，仅CROSS JOIN允许省略关联条件，因此会抛出语法错误。报错信息里的q01是子查询的合法别名，不是错误来源。

方案1：升级依赖包（推荐） #

该问题已经在dbplyr 2.2.0及之后的版本修复，同时升级duckdb R包到最新稳定版即可原生支持多列expand操作：

install.packages(c("dbplyr", "duckdb"))

升级后原代码fruits_db %>% tidyr::expand(type, size)可直接正常运行。

方案2：手动改写等价逻辑（适配旧版本） #

如果受环境限制无法升级包，可以用显式交叉连接的写法代替原生expand：

仅保留原数据中存在的列值 #

fruits_db %>% 
  dplyr::distinct(type) %>% 
  dplyr::cross_join(fruits_db %>% dplyr::distinct(size))

保留factor的所有缺失级别（和本地expand行为完全一致） #

# 先把因子级别上传到数据库
size_levels <- tibble(size = levels(fruits$size)) 
DBI::dbWriteTable(con, "size_levels", size_levels)
size_levels_db <- tbl(con, "size_levels")

fruits_db %>% 
  dplyr::distinct(type) %>% 
  dplyr::cross_join(size_levels_db)

方案3：小数据量临时方案 #

如果数据量很小，可以先把数据拉取到本地再调用expand：

fruits_db %>% 
  collect() %>% 
  tidyr::expand(type, size)

该bug最初出现在dbplyr的SQL翻译层，不属于tidyr或duckdb内核的问题，目前已在新版本中修复。

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