select() fails after specific sequence of dplyr commands

[edgararuiz]

It seems that when you sequence a group_by(), then a mutate(), and lastly an arrange(), dbplyr is not able to find the variable created by the mutate() when using select(). This works against a regular data frame. Reprex here:

suppressPackageStartupMessages(library(dplyr)) 
suppressPackageStartupMessages(library(dbplyr))
memdb_frame(
  Name = c("Alice", "Bob", "Alice", "Bob", "Charlie"),
  Subject = c("Math", "Math", "English", "English", "Math"),
  Score = c(90, 85, 88, 92, 78)
) %>% 
  group_by(Name) %>% 
  mutate(AvgScore = 1) %>% 
  ungroup() %>% 
  arrange(AvgScore, Name) %>% 
  select(Name)
#> Error in `collect()`:
#> ! Failed to collect lazy table.
#> Caused by error:
#> ! no such column: AvgScore

^{Created on 2024-01-09 with reprex v2.0.2}

Initially reported as a possible bug in sparklyr here: sparklyr/sparklyr#3413

[hadley]

Slightly more minimal reprex:

library(dbplyr)
library(dplyr, warn.conflicts = FALSE)

db <- lazy_frame(x = 1, y = 2) 

db %>% 
  mutate(z = 1) %>% 
  arrange(x, z) %>%
  select(x)
#> <SQL>
#> SELECT `x`
#> FROM `df`
#> ORDER BY `x`, `z`

^{Created on 2024-01-09 with reprex v2.0.2.9000}