LEAD function causing error.

[abalter]

lead function causing error. lag works fine.

library(tidyverse)
library(bigrquery)

bq_con = dbConnect(
  bigquery(),
  project = "some-os-bio-data",
  dataset = "medicaid_puf"
)

df = tibble(A = 1:3)

### Write table to database
dbWriteTable(bq_con, "tmp", df, overwrite=T)

### Read lazy table from DB
dbf = tbl(bq_con, "tmp")
#> Warning: <BigQueryConnection> uses an old dbplyr interface
#> ℹ Please install a newer version of the package or contact the maintainer
#> This warning is displayed once every 8 hours.

### Test lag and lead
dbf %>% mutate(B = lag(A, 1, 1, order_by = A))
#> # Source:   SQL [3 x 2]
#> # Database: BigQueryConnection
#>       A     B
#>   <int> <dbl>
#> 1     2     1
#> 2     3     2
#> 3     1     1
dbf %>% mutate(B = lead(A, 1, 1, order_by = A))
#> Error in `bq_job_wait()`:
#> ! Job 'some-os-bio-data.job_GOE9671I1uRvqwmQSNPnMaOpdOkw.US' failed
#> ✖ No matching signature for analytic function LEAD for argument types: INT64, FLOAT64, FLOAT64. Supported signature: LEAD(ANY, [INT64], [ANY]) at [1:11] [invalidQuery]
#> Backtrace:
#>      ▆
#>   1. ├─base::tryCatch(...)
#>   2. │ └─base (local) tryCatchList(expr, classes, parentenv, handlers)
#>   3. │   ├─base (local) tryCatchOne(...)
#>   4. │   │ └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>   5. │   └─base (local) tryCatchList(expr, names[-nh], parentenv, handlers[-nh])
#>   6. │     └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>   7. │       └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>   8. ├─base::withCallingHandlers(...)
#>   9. ├─base::saveRDS(...)
#>  10. ├─base::do.call(...)
#>  11. ├─base (local) `<fn>`(...)
#>  12. └─global `<fn>`(input = base::quote("awful-dog_reprex.R"))
#>  13.   └─rmarkdown::render(input, quiet = TRUE, envir = globalenv(), encoding = "UTF-8")
#>  14.     └─knitr::knit(knit_input, knit_output, envir = envir, quiet = quiet)
#>  15.       └─knitr:::process_file(text, output)
#>  16.         ├─base::withCallingHandlers(...)
#>  17.         ├─base::withCallingHandlers(...)
#>  18.         ├─knitr:::process_group(group)
#>  19.         └─knitr:::process_group.block(group)
#>  20.           └─knitr:::call_block(x)
#>  21.             └─knitr:::block_exec(params)
#>  22.               └─knitr:::eng_r(options)
#>  23.                 ├─knitr:::in_input_dir(...)
#>  24.                 │ └─knitr:::in_dir(input_dir(), expr)
#>  25.                 └─knitr (local) evaluate(...)
#>  26.                   └─evaluate::evaluate(...)
#>  27.                     └─evaluate:::evaluate_call(...)
#>  28.                       ├─evaluate (local) handle(...)
#>  29.                       │ └─base::try(f, silent = TRUE)
#>  30.                       │   └─base::tryCatch(...)
#>  31.                       │     └─base (local) tryCatchList(expr, classes, parentenv, handlers)
#>  32.                       │       └─base (local) tryCatchOne(expr, names, parentenv, handlers[[1L]])
#>  33.                       │         └─base (local) doTryCatch(return(expr), name, parentenv, handler)
#>  34.                       ├─base::withCallingHandlers(...)
#>  35.                       ├─base::withVisible(value_fun(ev$value, ev$visible))
#>  36.                       └─knitr (local) value_fun(ev$value, ev$visible)
#>  37.                         └─knitr (local) fun(x, options = options)
#>  38.                           ├─base::withVisible(knit_print(x, ...))
#>  39.                           ├─knitr::knit_print(x, ...)
#>  40.                           └─rmarkdown:::knit_print.tbl_sql(x, ...)
#>  41.                             ├─context$df_print(x)
#>  42.                             └─dbplyr:::print.tbl_sql(x)
#>  43.                               ├─dbplyr:::cat_line(format(x, ..., n = n, width = width, n_extra = n_extra))
#>  44.                               │ ├─base::cat(paste0(..., "\n"), sep = "")
#>  45.                               │ └─base::paste0(..., "\n")
#>  46.                               ├─base::format(x, ..., n = n, width = width, n_extra = n_extra)
#>  47.                               └─pillar:::format.tbl(x, ..., n = n, width = width, n_extra = n_extra)
#>  48.                                 └─pillar:::format_tbl(...)
#>  49.                                   └─pillar::tbl_format_setup(...)
#>  50.                                     ├─pillar:::tbl_format_setup_dispatch(...)
#>  51.                                     └─pillar:::tbl_format_setup.tbl(...)
#>  52.                                       └─pillar:::df_head(x, n + 1)
#>  53.                                         ├─base::as.data.frame(head(x, n))
#>  54.                                         └─dbplyr:::as.data.frame.tbl_sql(head(x, n))
#>  55.                                           ├─base::as.data.frame(collect(x, n = n))
#>  56.                                           ├─dplyr::collect(x, n = n)
#>  57.                                           └─bigrquery:::collect.tbl_BigQueryConnection(x, n = n)
#>  58.                                             └─bigrquery::bq_dataset_query(...)
#>  59.                                               └─bigrquery::bq_job_wait(job, quiet = quiet)
#>  60.                                                 └─rlang::abort(...)

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

[hadley]

Does dbf %>% mutate(B = lead(A, 1L, 1, order_by = A)) work?

[abalter]

It does indeed!

Is it, then, a necessary workaround? Or does it suggest something to add to the {bigrquery} code?

[hadley]

It actually looks like a bug in dbplyr. I'll fix it there.