Possible incorrect date literal translation for Oracle?

[mfherman]

I'm trying a simple filter on a date column in an Oracle 19c database and getting the following error:

Error: nanodbc/nanodbc.cpp:1655: HY000: [Oracle][ODBC][Ora]ORA-00936: missing expression

It appears that the issue (in my Oracle instance) is caused by the parenthesis surrounding the date in the dbplyr translation

tbl(con, "TEST_TABLE") %>%    
  filter(DT == as.Date("2020-01-01")) %>% 
  show_query()
# <SQL>
# SELECT *
# FROM ("TEST_TABLE") 
# WHERE ("DT" = DATE('2020-01-01'))

If I run the same query with the parentheses removed in my SQL IDE, the query runs with no error.

SELECT *
FROM ("TEST_TABLE") 
WHERE ("DT" = DATE '2020-01-01')

[cluoma]

It look like https://github.com/tidyverse/dbplyr/blob/main/R/backend-oracle.R is using the wrong function for date translation.

# https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/date-and-time-functions.html#function_date
as.Date = function(x) sql_expr(DATE(!!x)),

This is actually a reference for MySQL. Oracle does not use the DATE, but rather TO_DATE.

I made this change cluoma@5f004ab and it's now working for me.

[mfherman]

Looks right! I switched jobs and don’t have an Oracle DB to test anymore, but seems like it would be an easy fix!