"! Can't extract an environment from a call." when using dplyr::desc().

[avhz]

Not sure if this is a bug or not, but I have run into the following issue.

My actual workflow is with company data but I have managed to reproduce it with a pipeline that is close enough:

This works:

lf <- dbplyr::lazy_frame(
    a = TRUE, b = 1, c = 2, d = "z", 
    con = dbplyr::simulate_hana()
)
  
lf |> 
    dplyr::filter(.data$a == TRUE) |>
    dplyr::group_by(.data$b) |>
    dplyr::mutate(e = .data$b + .data$c) |>
    dbplyr::window_order(desc(.data$e)) |>      ## Note this line.
    dplyr::distinct(.data$d, .keep_all = TRUE)

Returns:

<SQL>
SELECT `a`, `b`, `c`, `d`, `e`
FROM (
  SELECT
    `q01`.*,
    ROW_NUMBER() OVER (PARTITION BY `b`, `d` ORDER BY `e` DESC) AS `col01`
  FROM (
    SELECT `df`.*, `b` + `c` AS `e`
    FROM `df`
    WHERE (`a` = TRUE)
  ) AS `q01`
) AS `q01`
WHERE (`col01` = 1)

This does not work:

lf <- dbplyr::lazy_frame(
    a = TRUE, b = 1, c = 2, d = "z", 
    con = dbplyr::simulate_hana()
)
  
lf |> 
    dplyr::filter(.data$a == TRUE) |>
    dplyr::group_by(.data$b) |>
    dplyr::mutate(e = .data$b + .data$c) |>
    dbplyr::window_order(dplyr::desc(.data$e)) |>  ## Note: I have now fully-qualified the desc() call.
    dplyr::distinct(.data$d, .keep_all = TRUE)

Returns:

Error in `get_env()` at dbplyr/R/translate-sql.R:166:3:
! Can't extract an environment from a call.
Run `rlang::last_trace()` to see where the error occurred.

[MichaelChirico]

I'm also encountering this in a semi_join() of a slice_min() and a slice_max() object:

mtcars <- mtcars |>
  tibble::rownames_to_column("model")
mtcars_lzy <- conn |>
  copy_to(mtcars, temporary = TRUE)

top_hp <- mtcars_lzy |>
  select(model, mpg, hp, disp) |>
  slice_max(n = 4L, order_by = hp)
bottom_wt <- mtcars_lzy |>
  select(model, mpg, cyl, wt) |>
  slice_min(n =15L, order_by = wt)

semi_join(top_hp, bottom_wt, by = "model")
# Error in `get_env()`:
# an't extract an environment from a call.
# Run `rlang::last_trace()` to see where the error occurred.

Per the traceback(), we get the error in sql_data_mask() where expr is the call:

sql("`RHS`")$col01 <= 15L

Rendering either of top_hp or bottom_wt individually presents no issues.

It seems to work, however, if I avoid slice_{min,max} in favor of an arrange()|>head() approach:

top_hp <- mtcars_lzy |>
  select(model, mpg, hp, disp) |>
  arrange(desc(hp)) |>
  head(4)
bottom_wt <- mtcars_lzy |>
  select(model, mpg, cyl, wt) |>
  arrange(wt) |>
  head(15)
semi_join(top_hp, bottom_wt, by = "model")
# # Source:     SQL [?? x 4]
# # Database:   MyConnection
# # Ordered by: desc(hp)
#   model            mpg    hp  disp
#   <chr>          <dbl> <dbl> <dbl>
# 1 Ford Pantera L  15.8   264   351

[MichaelChirico]

Slightly simpler example avoiding mtcars:

t1 = conn |> tbl(sql('SELECT 1 AS a, 0 AS v UNION ALL SELECT 2 AS a, 0 AS v'))
t2 = conn |> tbl(sql('SELECT 1 AS a, 1 AS v UNION ALL SELECT 3 AS a, 1 AS v'))
# plain semi_join works
invisible(semi_join(t1, t2, by='a'))
# as does an arrange() approach [incl. desc(v)]
semi_join(t1, head(arrange(t2, v), 2), by='a')

# but fails on a continued query why 'y' is a slice_max()
semi_join(t1, slice_max(t2, v, n=2), by='a')
# Error in `get_env()`:
# ! Can't extract an environment from a call.
# Run `rlang::last_trace()` to see where the error occurred.