rdplyrdbplyrbigrquery

bigrquery - Error: No matching signature for operator - for argument types: DATE, FLOAT64

When filter()ing by date, and using a function to provide that date, I see an expected error: 

library(tidyverse)
library(bigrquery)

table1 %>% 
  filter(date > as.character(today() - 730)) %>% 
  tally()

# Error: No matching signature for operator - for argument types: DATE, FLOAT64. 
Supported signatures: INT64 - INT64; NUMERIC - NUMERIC; FLOAT64 - FLOAT64 at [29:29] [invalidQuery]

but when the date is provided as a string it works fine

table1 %>%
  filter(date > '2018-04-11') %>% 
  tally()

#          n
#      <int>
#  623451234

I can confirm as.character(today() - 730) and '2018-04-11' are the same

> identical(as.character(today() - 730), '2018-04-11')
[1] TRUE

The following workaround is okay, but becomes cluttered for larger code bases:

date_from <- as.character(today() - 730)

table1 %>% 
  filter(date > date_from) %>% 
  tally()

Question

Why do we see an error when a date is provided to filter() via a function like as.character(today() - 730), but not when the date is provided by a string?

Notes

Solution

  • You have tagged this with dbplyr though there is no mention of it in your question. If you are using dbplyr then the issue is due to how dbplyr translates from R to the database language (bigquery in your case).

    dbplyr has a series of in-built translations that do the conversion (e.g. filter gets tranlated to WHERE clauses). These translations are able to look up values from variables as per your example:

    date_from <- as.character(today() - 730)
table1 %>% filter(date > date_from)

    But can not tell for sub-functions whether to evaluate, translate, or leave as-is. Hence according to this documentation dbplyr leaves any 'unknown' functions as-is. This means that

    table1 %>% filter(date > as.character(today() - 730))

    probably gets translated to something like

    SELECT ...
FROM ...
WHERE date > as.character(today() - 730)

    which causes an error as this is not valid syntax for the database.

    You can check the translation by using show_query():

    table1 %>% filter(date > as.character(today() - 730)) %>% show_query()

    This question asks about a very similar problem - the translations are defined for the functions they are using, but not for the custom function that acts as a wrapper. Being able to define custom translations would solve both questions but this does not appear possible at present.

    There are two work arounds:

    1. From the dbplyr documentation you can use sql() to insert a raw SQL string (that will not be translated).
    2. You can write your own functions that return custom table definitions. See the "Creating custom functions" section of this answer.