Error to use pool and left_join without suffixes

[ppagnone]

Hi, I was updating my package and a query which worked well with previous version is throwing exception if "suffix" param is not pass to left_join() function , altought "suffix" has a default value.

Error:

Error in UseMethod("sql_join_suffix") : 
  no applicable method for 'sql_join_suffix' applied to an object of class "c('Pool', 'R6')"

Code example:

pool <- dbPool(
    drv =RMariaDB::MariaDB(),
    user = myConfig$database$user,
    password = myConfig$database$password,
    host = myConfig$database$host,
    dbname = myConfig$database$dbname
  )

### Old query working previous update of package and throwing exception now:

query <- tbl(pool, "users") %>%  left_join(tbl(pool, "user_role"), by="user_id")

### This is working with last version
query <- tbl(pool, "users") %>% left_join(tbl(pool, "user_role"), by="user_id", suffix = c("a","b"))

Thanks in advance!

[dietrichson]

Reproducible example based on example code:

library(pool)
library(dplyr)
library(dbplyr)
pool <- dbPool(
  drv = RMySQL::MySQL(),
  dbname = "shinydemo",
  host = "shiny-demo.csa7qlmguqrf.us-east-1.rds.amazonaws.com",
  username = "guest",
  password = "guest"
)
City <- tbl(pool,"City")
Country <- tbl(pool, "Country")
City %>% 
  left_join(Country, by = c("CountryCode" = "Code"))`

Error in UseMethod("sql_join_suffix") : 
  no applicable method for 'sql_join_suffix' applied to an object of class "c('Pool', 'R6')"

[burgerga]

@hadley Caused by tidyverse/dbplyr@6e64685

Possible fix/workaround is to define

sql_join_suffix.default <- function(con, ...) {
  c(".x", ".y")
}

[jcheng5]

Thanks for the report, should be all set on master now!

[henrique1008]

Hi, today this happened with me

If I try this , everything works
query <- tbl(pool, "users") %>% left_join(tbl(pool, "user_role"), by="user_id", suffix = c("a","b"))

but if I try this

### This is working with last version
query <- tbl(pool, "users") %>% left_join(tbl(pool, "user_role"), by="user_id")

I receive this error

Error in UseMethod("sql_join_suffix") : 
  no applicable method for 'sql_join_suffix' applied to an object of class "c('Pool', 'R6')"

Therefore add suffix = c("a","b") make the code works, even if you don't have any duplicate variables other than those used for the join

It starts this morning for me

versions

pool 1.0.0
dplyr 1.1.0
dbplyr 2.3.0

I copied the exemplo of ppagnore up here, but I use Postgres backend, but I think this don´t mattters in this case