Getting sql_join_suffix with pool 1.0

[vituri]

I updated pool to the 1.0 version on CRAN today I got the following error when doing a left_join:

Error in UseMethod("sql_join_suffix") :
método não aplicável para 'sql_join_suffix' aplicado a um objeto de classe "c('Pool', 'R6')"

Going back to pool 0.1.6 solves the issue.

I am doing the following with a mariadb connection:

tbl(con, 'Grupos_com_ancestrais') %>%
  filter(
    Id_ancestral %in% ids
  ) %>%
  left_join(
    tbl(con, "Grupos")  
  ) %>%
  collect()

Thanks in advance!

[henrique1008]

Hi
I had the same problem today

I found the same problem in old issue, this #111

If you try add suffix = c("a","b") in join code with pool 1.0.0, this works, but anyway this is a bug

tbl(con, 'Grupos_com_ancestrais') %>%
  filter(
    Id_ancestral %in% ids
  ) %>%
  left_join(
    tbl(con, "Grupos") ,
 suffix = c("a","b") 
  ) %>%
  collect()

[hadley]

Can you please provide a minimal reprex (reproducible example)? The goal of a reprex is to make it as easy as possible for me to recreate your problem so that I can fix it: please help me help you! If you've never heard of a reprex before, start by reading about the reprex package, including the advice further down the page. Please make sure your reprex is created with the reprex package as it gives nicely formatted output and avoids a number of common pitfalls.

[henrique1008]

This is a small example

con <- pool::dbPool(
  RPostgres::Postgres(),
  dbname = YOUR_DB,
  host = YOUR_IP,
  user = YOUR_USER,
  password = YOUR PASSWORD
)


tbl_01 <- con |> 
  dplyr::tbl("tbl_01")

tbl_02 <- con |> 
  dplyr::tbl("tbl_02")

tbl_01 |> 
  dplyr::left_join(y = tbl_02,
                   by = c("column_01", "column_02"))

This code return this error

Error in UseMethod("sql_join_suffix"):
não aplicável para 'sql_join_suffix' aplicado a um método de classe "c('Pool', 'R6')"

but if I write adding suffix = c("a","b") the code works

tbl_01 |> 
  dplyr::left_join(y = tbl_02,
                   by = c("column_01", "column_02"),
                   suffix = c("a","b"))

this was related in this issue #111 in the past

Thanks in advance for the help

[vituri]

Thanks, @henrique1008 ! Here is another example:

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

# this is a personal function to connect to my mariadb database; it just uses pool and RMariaDB
con = my_connection_function()

tbl(con, 'API_users') %>% 
  left_join(
    tbl(con, 'API_users_config')
  )
#> Joining, by = "User"
#> Error in UseMethod("sql_join_suffix"): método não aplicável para 'sql_join_suffix' aplicado a um objeto de classe "c('Pool', 'R6')"

As Henrique said, using the suffix argument solves the problem, but I never needed to use it before.

^{Created on 2023-02-16 with reprex v2.0.2}

[hadley]

This works fine for me:

library(dplyr, warn.conflicts = FALSE)
con <- pool::dbPool(RSQLite::SQLite())
df1 <- copy_to(con, data.frame(x = 1, y = 2, z = 3), temporary = FALSE)
df2 <- copy_to(con, data.frame(x = 1, y = 2, w = 3), temporary = FALSE)

left_join(df1, df2, by = "x")
#> # Source:   SQL [1 x 5]
#> # Database: sqlite 3.40.0 []
#>       x   y.x     z   y.y     w
#>   <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1     1     2     3     2     3

^{Created on 2023-02-16 with reprex v2.0.2}

[vituri]

@hadley your example works fine here too.

[hadley]

Could you please modify it to recreate the error you are seeing?

[vituri]

I was able to modify it and obtain an error:

library(dplyr, warn.conflicts = FALSE)

con <- pool::dbPool(RSQLite::SQLite())
copy_to(con, data.frame(x = 1, y = 2, z = 3), name = 'df1', temporary = FALSE)
copy_to(con, data.frame(x = 1, y = 2, w = 3), name = 'df2', temporary = FALSE)

left_join(
  tbl(con, 'df1')
  ,tbl(con, 'df2')
)
#> Joining, by = c("x", "y")
#> Error in UseMethod("sql_join_suffix"): método não aplicável para 'sql_join_suffix' aplicado a um objeto de classe "c('Pool', 'R6')"

Adding suffix = letters[1:2] to the left_join seems to avoid the error.

^{Created on 2023-02-16 with reprex v2.0.2}

[hadley]

Oh yeah, that suggests another possible problem — copy_to should use a pool object.

[hadley]

Thanks for reporting this! It's now fixed in the dev version and I'll push out a release next week.