as.Date() now throws an error (version 2.3.2) when querying a Teradata server.

[SayfSaid]

I had a previously running code to query a Teradata server that is no longer running after updating (dbplyr, R, and odbc).
The issue seems to be related to how as.Date() is being translated into SQL. Using show_query(), in the prior versions it came as CAST("X" AS DATE), now SELECT DATE "X" which throws an error when trying to pull the data.

I know it is related to the update because the code still runs on a VM running older versions and querying the same server.
The updates are: R 4.2 to 4.3, dplyr 1.0.10 to 1.1.2, dbplyr 2.2.1 to 2.3.2, odbc 1.3.3 to 1.3.4, and DBI is the same 1.1.3.

Below are comparison of the two queries.

   library (dplyr)
   library (DBI)
   library (odbc)
   connection <- dbConnect(odbc(), "Teradata")

   tbl(connection, "Teradata_table") %>% 
   head() %>% 
   select(POSIXct_POSIXt_var) %>% 
   mutate(date_var= as.Date(POSIXct_POSIXt_var)) %>% 
   show_query()

The old version brings

<SQL>
   SELECT "POSIXct_POSIXt_var", CAST("POSIXct_POSIXt_var" AS DATE) AS "date_var"
   FROM (
     SELECT TOP 6 *
     FROM "Teradata_table"
   ) "q01"

Now results in:

 <SQL>
   SELECT TOP 6 "POSIXct_POSIXt_var", DATE "POSIXct_POSIXt_var" AS "date_var"
   FROM "Teradata_table"

The latter throws an error when trying to pull the data as such:

   tbl(connection, "Teradata_table") %>% 
   head() %>% 
   select(POSIXct_POSIXt_var) %>% 
   mutate(date_var = as.Date(POSIXct_POSIXt_var)) %>% 
   as_tibble()

Results in:

(-3706)Syntax error: expected something between the word 'POSIXct_POSIXt_var' and the 'AS' keyword.

While I had a good suggestion for a walk around on my SO post by using sql('CAST ("var" AS DATE)') to replace as.Date(), I feel this might need to be addressed here to retain as.Date() functionality for Teradata use. A more experience user on SO suggested that the issue might be related to this commit. I hope this helps.

[mgirlich]

@overmar This translations was added by you in #913. Would be great if you could look into this.

[But2ene]

The problem comes from two conflicting use cases.

Litterals

With litterals such as "2020-01-01", what was implemented in #913 is correct. It is a good practice to prefix date litterals with the keyword "DATE". For example,

as.Date("2020-01-01")

should be translated to (with single quotes !)

DATE '2020-01-01'

Identifiers

With identifiers / variable names / vectors of length greater than 1, we have

as.Date(MY_TIMESTAMP_VAR)

should be translated to (with double quotes)

CAST("MY_TIMESTAMP_VAR" AS DATE)

I'm not sure how to implement something that can distinguish both cases. By default, I would expect as.Date and lubridate::as_date to be both translated as CAST AS DATE, which was the previous default. In my code, I manually specify the DATE '2020-01-01' when needed.

[mgirlich]

Fixed by 8e4feba.

[mgirlich]

Thanks @But2ene for the detailed description.