dbplyr 2.4.0 - date filter doesn't work anymore, possibly due to absence of single quotes in translation

[mgarbuzov]

tbl(con, from = in_schema("SCHEMA", "TABLE")) |>
filter(START_DATE >= as.Date("2022-01-01"))

In 2.3.4:
Output of show_query():

<SQL>
SELECT *
FROM ("SCHEMA"."TABLE") 
WHERE ("START_DATE " >= DATE '2022-01-01')

Collects fine.

In 2.4.0:

Output of show_query():

<SQL>
SELECT "TABLE".*
FROM "SCHEMA"."TABLE"
WHERE ("START_DATE " >= DATE 2022-01-01)

On collecting:

Error in `collect()`:
! Failed to collect lazy table.
Caused by error:
! nanodbc/nanodbc.cpp:1771: HY000
[Oracle][ODBC][Ora]ORA-00936: missing expression

[hadley]

Can you please provide a reprex, following the suggestions in https://dbplyr.tidyverse.org/articles/reprex.html?

[mgarbuzov]

@hadley Only a reprex that shows SQL generation.

memdb_frame() creates sqlite version which doesn't seem to create dates variables correctly.

> memdb_frame(DATE = c(as.Date("2022-01-01"), as.Date("2024-01-01")))
# Source:   table<dbplyr_003> [2 x 1]
# Database: sqlite 3.45.0 [:memory:]
   DATE
  <dbl>
1 18993
2 19723

Further, for all I know, the problem may be specific to Oracle, which I have got.

dbplyr 2.3.4

> df <- data.frame(DATE = c(as.Date("2022-01-01"), as.Date("2024-01-01")))
> tbl_lazy(df, con = simulate_oracle()) |> filter(DATE >= as.Date("2023-01-01"))
<SQL>
SELECT *
FROM (`df`) 
WHERE (`DATE` >= DATE '2023-01-01')

dbplyr 2.4.0

> df <- data.frame(DATE = c(as.Date("2022-01-01"), as.Date("2024-01-01")))
> tbl_lazy(df, con = simulate_oracle()) |> filter(DATE >= as.Date("2023-01-01"))
<SQL>
SELECT `df`.*
FROM `df`
WHERE (`DATE` >= DATE 2023-01-01)

The difference seems to boil down to presence/absence of single quotes in DATE '2023-01-01'.

[hadley]

For future reference, here's what a minimal reprex might look like:

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

data.frame(x = c(as.Date("2022-01-01"), as.Date("2024-01-01"))) |> 
  tbl_lazy(con = simulate_oracle()) |> 
  filter(x >= as.Date("2023-01-01"))
#> <SQL>
#> SELECT `df`.*
#> FROM `df`
#> WHERE (`x` >= DATE 2023-01-01)

^{Created on 2024-02-15 with reprex v2.0.2.9000}

And the expected SQL should have quotes around the date.

And here's a link to the oracle docs confirming the expected syntax: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html#GUID-8F4B3F82-8821-4071-84D6-FBBA21C05AC1

[hadley]

Last code to touch this was 1e2633e

[hadley]

Oooh, that commit fixed #1389, which was a duplicate of this issue. So it's fixed in the dev version, and we're working towards a CRAN release in the near future.