dbplyr 2.4.0 - table aliases with in_schema now break queries

[MKatz-DHSC]

New behaviour of prefixing * with the table name breaks Spark ODBC queries, is it possible to override this behaviour?

library(dplyr)
library(dbplyr)

x <- c("abc", "def", "ghif")

# works as generates SELECT *
lazy_frame(x = x, con = simulate_odbc()) %>% 
  show_query()

# breaks as generates SELECT `df`.*
lazy_frame(x = x, con = simulate_odbc()) %>% 
  head(5) %>% 
  show_query()

[mgirlich]

Are you sure this is due to df.*? This works for me

library(sparklyr)
library(dplyr)
sc <- spark_connect(master = "local")

iris_tbl <- copy_to(sc, iris, overwrite = TRUE)
iris_tbl %>% 
  head(5)
#> # Source: spark<?> [?? x 5]
#>   Sepal_Length Sepal_Width Petal_Length Petal_Width Species
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>  
#> 1          5.1         3.5          1.4         0.2 setosa 
#> 2          4.9         3            1.4         0.2 setosa 
#> 3          4.7         3.2          1.3         0.2 setosa 
#> 4          4.6         3.1          1.5         0.2 setosa 
#> 5          5           3.6          1.4         0.2 setosa

iris_tbl %>% 
  head(5) %>% 
  show_query()
#> <SQL>
#> SELECT `iris`.*
#> FROM `iris`
#> LIMIT 5

^{Created on 2023-11-10 with reprex v2.0.2}

Can you please add a reprex?

[MKatz-DHSC]

Apologies, had a deeper dive and believe the issue is to do with the interaction between in_schema and the new use of a prefix for *. If you pass a named vector as the table argument of in_schema that name is used for the table alias (not sure if this is documented) which conflicts with the prefix.

library(dplyr)
library(dbplyr)

x <- c("abc", "def", "ghif")
table <- in_schema("schema", c("alias" = "table"))

# works as generates SELECT *
lazy_frame(x = x, con = simulate_odbc(), .name = table) %>% 
  show_query()
#> <SQL>
#> SELECT *
#> FROM `schema`.`table`

# breaks as generates SELECT `table`.* but with FROM `schema`.`table` AS `alias`
lazy_frame(x = x, con = simulate_odbc(), .name = table) %>% 
  head(5) %>% 
  show_query()
#> <SQL>
#> SELECT `table`.*
#> FROM `schema`.`table` AS `alias`
#> LIMIT 5

Do you want me to open a new issue or rename this one?

[hadley]

@MKatz-DHSC Are you deliberately or accidentally passing a named vector to in_schema()?

[MKatz-DHSC]

The unexpected behaviour occurred when I was indexing a named vector of table names as part of a loop with [ rather than [[. Based on the documentation assumed it did not matter which I passed to in_schema().

[hadley]

Ok, just wanted to double check that the expected behaviour here is to drop the names.

[MKatz-DHSC]

Yep, can't really see a use case where you would want to manually specify the alias name. Thanks for looking into this.