glue ≥ 1.7 : wrong format of Posixt datetime by glue_sql()

Question

glue ≥ 1.7 : wrong format of Posixt datetime by glue_sql()

Closed this issue 8 months ago · 13 comments

In glue 1.6.2 Posixt datetimes are well formated by glue_sql() for RDBMS : '2023-11-16 14:52:45'
In glue 1.7.0 Posixt datetimes are not well formated by glue_sql() for RDBMS : '20231116145245'

glue 1.6.2 : ok `'2023-11-16 14:52:45'`

library(glue)

datas<-structure(
  list(
    mydatetime_posixt = structure(1700146365, 
                                  tzone = "UTC", 
                                  class = c("POSIXct",  "POSIXt")
    ), 
    mydate = structure(19676, class = "Date")
  ), 
  row.names = c(NA,-1L), 
  class = c("tbl_df", "tbl", "data.frame")
)

glue_sql (
  '{mydatetime_posixt} "mydatetime_sqlserver",{mydate} "mydate_sqlserver"',
  mydatetime_posixt=datas$mydatetime_posixt,
  mydate=datas$mydate, 
  .con=DBI::ANSI() 
)
#> <SQL> '2023-11-16 14:52:45' "mydatetime_sqlserver",'2023-11-15' "mydate_sqlserver"

^{Created on 2024-02-21 with reprex v2.0.2}

Session info

sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.2.2 Patched (2022-11-10 r83330)
#>  os       Ubuntu 22.04.2 LTS
#>  system   x86_64, linux-gnu
#>  ui       X11
#>  language en
#>  collate  fr_FR.UTF-8
#>  ctype    fr_FR.UTF-8
#>  tz       Europe/Paris
#>  date     2024-02-21
#>  pandoc   3.1.1 @ /usr/lib/rstudio-server/bin/quarto/bin/tools/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version date (UTC) lib source
#>  cli           3.6.0   2023-01-09 [1] CRAN (R 4.2.2)
#>  DBI           1.1.3   2022-06-18 [1] CRAN (R 4.2.2)
#>  digest        0.6.34  2024-01-11 [1] CRAN (R 4.2.2)
#>  evaluate      0.20    2023-01-17 [1] CRAN (R 4.2.2)
#>  fastmap       1.1.1   2023-02-24 [1] CRAN (R 4.2.2)
#>  fs            1.6.1   2023-02-06 [1] CRAN (R 4.2.2)
#>  glue        * 1.6.2   2022-02-24 [1] CRAN (R 4.2.2)
#>  htmltools     0.5.5   2023-03-23 [1] CRAN (R 4.2.2)
#>  knitr         1.42    2023-01-25 [1] CRAN (R 4.2.2)
#>  lifecycle     1.0.3   2022-10-07 [1] CRAN (R 4.2.2)
#>  magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.2.2)
#>  purrr         1.0.1   2023-01-10 [1] CRAN (R 4.2.2)
#>  R.cache       0.16.0  2022-07-21 [1] CRAN (R 4.2.2)
#>  R.methodsS3   1.8.2   2022-06-13 [1] CRAN (R 4.2.2)
#>  R.oo          1.25.0  2022-06-12 [1] CRAN (R 4.2.2)
#>  R.utils       2.12.2  2022-11-11 [1] CRAN (R 4.2.2)
#>  reprex        2.0.2   2022-08-17 [1] CRAN (R 4.2.2)
#>  rlang         1.1.0   2023-03-14 [1] CRAN (R 4.2.2)
#>  rmarkdown     2.20    2023-01-19 [1] CRAN (R 4.2.2)
#>  rstudioapi    0.14    2022-08-22 [1] CRAN (R 4.2.2)
#>  sessioninfo   1.2.2   2021-12-06 [1] CRAN (R 4.2.2)
#>  styler        1.9.0   2023-01-15 [1] CRAN (R 4.2.2)
#>  vctrs         0.6.5   2023-12-01 [1] CRAN (R 4.2.2)
#>  withr         2.5.0   2022-03-03 [1] CRAN (R 4.2.2)
#>  xfun          0.37    2023-01-31 [1] CRAN (R 4.2.2)
#>  yaml          2.3.7   2023-01-23 [1] CRAN (R 4.2.2)
#> 
#>  [1] /usr/local/lib/R/site-library
#>  [2] /usr/lib/R/site-library
#>  [3] /usr/lib/R/library
#> 
#> ──────────────────────────────────────────────────────────────────────────────

glue 1.7.0 : issue `'20231116145245'`

library(glue)

datas<-structure(
  list(
    mydatetime_posixt = structure(1700146365, 
                                  tzone = "UTC", 
                                  class = c("POSIXct",  "POSIXt")
    ), 
    mydate = structure(19676, class = "Date")
  ), 
  row.names = c(NA,-1L), 
  class = c("tbl_df", "tbl", "data.frame")
)

glue_sql (
  '{mydatetime_posixt} "mydatetime_sqlserver",{mydate} "mydate_sqlserver"',
  mydatetime_posixt=datas$mydatetime_posixt,
  mydate=datas$mydate, 
  .con=DBI::ANSI() 
)
#> <SQL> '20231116145245' "mydatetime_sqlserver",'2023-11-15' "mydate_sqlserver"

^{Created on 2024-02-21 with reprex v2.1.0}

Session info

sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.3.2 (2023-10-31)
#>  os       Ubuntu 22.04.3 LTS
#>  system   x86_64, linux-gnu
#>  ui       X11
#>  language en
#>  collate  fr_FR.UTF-8
#>  ctype    fr_FR.UTF-8
#>  tz       Europe/Paris
#>  date     2024-02-21
#>  pandoc   3.1.1 @ /usr/lib/rstudio-server/bin/quarto/bin/tools/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version date (UTC) lib source
#>  cli           3.6.2   2023-12-11 [1] CRAN (R 4.3.2)
#>  DBI           1.2.1   2024-01-12 [1] CRAN (R 4.3.2)
#>  digest        0.6.34  2024-01-11 [1] CRAN (R 4.3.2)
#>  evaluate      0.23    2023-11-01 [1] CRAN (R 4.3.2)
#>  fastmap       1.1.1   2023-02-24 [1] CRAN (R 4.3.2)
#>  fs            1.6.3   2023-07-20 [1] CRAN (R 4.3.2)
#>  glue        * 1.7.0   2024-01-09 [1] CRAN (R 4.3.2)
#>  htmltools     0.5.7   2023-11-03 [1] CRAN (R 4.3.2)
#>  knitr         1.45    2023-10-30 [1] CRAN (R 4.3.2)
#>  lifecycle     1.0.4   2023-11-07 [1] CRAN (R 4.3.2)
#>  magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.3.2)
#>  purrr         1.0.2   2023-08-10 [1] CRAN (R 4.3.2)
#>  R.cache       0.16.0  2022-07-21 [1] CRAN (R 4.3.2)
#>  R.methodsS3   1.8.2   2022-06-13 [1] CRAN (R 4.3.2)
#>  R.oo          1.26.0  2024-01-24 [1] CRAN (R 4.3.2)
#>  R.utils       2.12.3  2023-11-18 [1] CRAN (R 4.3.2)
#>  reprex        2.1.0   2024-01-11 [1] CRAN (R 4.3.2)
#>  rlang         1.1.3   2024-01-10 [1] CRAN (R 4.3.2)
#>  rmarkdown     2.25    2023-09-18 [1] CRAN (R 4.3.2)
#>  rstudioapi    0.15.0  2023-07-07 [1] CRAN (R 4.3.2)
#>  sessioninfo   1.2.2   2021-12-06 [1] CRAN (R 4.3.2)
#>  styler        1.10.2  2023-08-29 [1] CRAN (R 4.3.2)
#>  vctrs         0.6.5   2023-12-01 [1] CRAN (R 4.3.2)
#>  withr         3.0.0   2024-01-16 [1] CRAN (R 4.3.2)
#>  xfun          0.41    2023-11-01 [1] CRAN (R 4.3.2)
#>  yaml          2.3.5   2022-02-21 [2] CRAN (R 4.1.2)
#> 
#>  [1] /usr/local/lib/R/site-library
#>  [2] /usr/lib/R/site-library
#>  [3] /usr/lib/R/library
#> 
#> ──────────────────────────────────────────────────────────────────────────────

temporary workaround in glue 1.7 : base::format(mydatetime_posixt, format = "%Y-%m-%d %H:%M:%S")

library(glue)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

datas<-structure(
  list(
    mydatetime_posixt = structure(1700146365, 
                                  tzone = "UTC", 
                                  class = c("POSIXct",  "POSIXt")
    ), 
    mydate = structure(19676, class = "Date")
  ), 
  row.names = c(NA,-1L), 
  class = c("tbl_df", "tbl", "data.frame")
)

datas <- (datas
          %>% mutate(
            mydatetime_posixt = base::format(mydatetime_posixt, format  =  "%Y-%m-%d %H:%M:%S")
          )
)

glue_sql (
  '{mydatetime_posixt} "mydatetime_sqlserver",{mydate} "mydate_sqlserver"',
  mydatetime_posixt=datas$mydatetime_posixt,
  mydate=datas$mydate, 
  .con=DBI::ANSI() 
)
#> <SQL> '2023-11-16 14:52:45' "mydatetime_sqlserver",'2023-11-15' "mydate_sqlserver"

^{Created on 2024-02-21 with reprex v2.1.0}

Answer 1 · 2024-02-21T10:12:02.000Z

(reprex with my implementation of glue_sql()

Tools.glue_sql.multi.fct <- function(sql_multi_vars, args, connexion_bdd = DBI::ANSI()) {

  args <- unlist(list(sql_multi_vars, args, .con = connexion_bdd), recursive = FALSE)
  unname(names(args)[1])
  do.call(glue_sql, args)
}
Tools.glue_sql.multi.fct (
  '{mydatetime_posixt} "mydatetime_sqlserver",{mydate} "mydate_sqlserver"',
  datas
)
# glue 1.6.2
#> <SQL> '2023-11-16 14:52:45' "mydatetime_sqlserver",'2023-11-15' "mydate_sqlserver"

# glue 1.7.0
#> <SQL> '20231116145245' "mydatetime_sqlserver",'2023-11-15' "mydate_sqlserver"

)

Answer 2 · 2024-02-22T19:12:56.000Z

@hadley Do you think this is a consequence of #293?

Answer 3 · 2024-02-22T20:47:38.000Z

Yeah, quite possibly.

Answer 4 · 2024-02-22T21:23:45.000Z

With the caveat that I have not worked on the SQL-related features of glue, a quick peek at DBI suggests that, in our use of DBI::dbQuoteLiteral(), we would like to be hitting this code for datetimes:

https://github.com/r-dbi/DBI/blob/9789f9937f4a099ef88aa086e91c5a3e1ec73c9f/R/dbQuoteLiteral_DBIConnection.R#L22

I'm relying on the other discussants to confirm this would produce the desired outcome.

Update: Or, perhaps, maybe that code is the problem? Now that I read it more carefully, that looks like the formatting I see above. Should this be an issue in DBI?

Answer 5 · 2024-02-22T21:32:07.000Z

Yeah, I thnk it's a DBI issue.

Answer 6 · 2024-02-22T22:15:40.000Z

@philibe given your excellent reprex-ing skills 😀 I think you should be able to create a reprex that only uses DBI (not glue). If true, then you can close this an open an issue on DBI.

Answer 7 · 2024-02-23T10:52:11.000Z

You mean this test : DBI::dbQuoteLiteral(conn=DBI::ANSI(), datas$mydatetime_posixt) ?

Here is the results:

DBI 1.1.3 : '20231116145245'
DBI 1.2.1 : '20231116145245'

Therefore I debug step by step:

Traceback:

glue_sql( ........ .transformer .... ) <--
DBI::SQL
glue
glue_data
glue:::bind_args(dots[named], parent_env)
glue:::glue_

I'm in the process of debug and it seems it maybe caused by the change of glue:::sql_quote_transformer between 1.6.2 ans 1.7.0 in glue_sql( ........ .transformer .... )

Answer 8 · 2024-02-23T13:10:55.000Z

Here is glue_sql() 1.7.0 with sql_quote_transformer_162:

'2023-11-16 14:52:45' "mydatetime_sqlserver",'2023-11-15' "mydate_sqlserver"

library(glue)

datas<-structure(
  list(
    mydatetime_posixt = structure(1700146365, 
                                  tzone = "UTC", 
                                  class = c("POSIXct",  "POSIXt")
    ), 
    mydate = structure(19676, class = "Date")
  ), 
  row.names = c(NA,-1L), 
  class = c("tbl_df", "tbl", "data.frame")
)


sql_quote_transformer_162 <-
function (connection, .na) 
{
  if (is.null(.na)) {
    .na <- DBI::SQL(NA)
  }
  function(text, envir) {
    should_collapse <- grepl("[*][[:space:]]*$", text)
    if (should_collapse) {
      text <- sub("[*][[:space:]]*$", "", text)
    }
    m <- gregexpr("^`|`$", text)
    is_quoted <- any(m[[1]] != -1)
    if (is_quoted) {
      regmatches(text, m) <- ""
      res <- eval(parse(text = text, keep.source = FALSE), 
                  envir)
      if (length(res) == 1) {
        res <- DBI::dbQuoteIdentifier(conn = connection, 
                                      res)
      }
      else {
        res[] <- lapply(res, DBI::dbQuoteIdentifier, 
                        conn = connection)
      }
    }
    else {
      res <- eval(parse(text = text, keep.source = FALSE), 
                  envir)
      if (inherits(res, "SQL")) {
        if (should_collapse) {
          res <- glue_collapse(res, ", ")
        }
        if (length(res) == 0L) {
          res <- DBI::SQL("NULL")
        }
        return(res)
      }
      is_object <- is.object(res)
      if (is_object) {
        res <- as.character(res)
      }
      is_na <- is.na(res)
      if (any(is_na)) {
        res[is_na] <- rep(list(.na), sum(is_na))
      }
      is_char <- vapply(res, function(x) !is.na(x) && is.character(x), 
                        logical(1))
      res[is_char] <- lapply(res[is_char], function(x) DBI::dbQuoteLiteral(conn = connection, 
                                                                           x))
      res[!is_char] <- lapply(res[!is_char], function(x) DBI::SQL(conn = connection, 
                                                                  x))
    }
    if (should_collapse) {
      res <- glue_collapse(res, ", ")
    }
    if (length(res) == 0L) {
      res <- DBI::SQL("NULL")
    }
    res
  }
}

glue_sql_with_sql_quote_transformer_162<-
  function (..., .con, .sep = "", .envir = parent.frame(), .open = "{", 
            .close = "}", .na = DBI::SQL("NULL"), .null = character(), 
            .comment = "#", .literal = FALSE, .trim = TRUE) 
  {
    DBI::SQL(glue(..., .sep = .sep, .envir = .envir, .open = .open, 
                  .close = .close, .na = .na, .null = .null, .comment = .comment, 
                  .literal = .literal, .transformer = sql_quote_transformer_162(.con, 
                                                                                .na), .trim = .trim))
  }


glue_sql_with_sql_quote_transformer_162 (
  '{mydatetime_posixt} "mydatetime_sqlserver",{mydate} "mydate_sqlserver"',
  mydatetime_posixt=datas$mydatetime_posixt,
  mydate=datas$mydate, 
  .con=DBI::ANSI() 
)
#> <SQL> '2023-11-16 14:52:45' "mydatetime_sqlserver",'2023-11-15' "mydate_sqlserver"

^{Created on 2024-02-23 with reprex v2.1.0}

Answer 9 · 2024-02-23T13:13:20.000Z

The ball is in your court :)

Answer 10 · 2024-02-23T13:13:50.000Z

@philibe yes, glue now relies on DBI to supply the correct transformation from R literals to SQL literals. So please file an issue in whatever database package you are using.

Answer 11 · 2024-02-23T13:20:55.000Z

Can you explain your answer please ? : with connection DBI::ANSI() there is yet an issue.

I use DBI::dbConnect(odbc::odbc(),.connection_string=choosen_RDBMS_connection, encoding = database_encoding)

Answer 12 · 2024-02-23T13:22:54.000Z

Therefore in https://github.com/r-dbi/DBI/issues I suppose.

Answer 13 · 2024-02-23T13:35:50.000Z

@philibe you need to file an issue with the specific backend that you're using, unless you have evidence to suggest that's the wrong date-time format for ANSI SQL.

glue 1.6.2 : ok '2023-11-16 14:52:45'

glue 1.7.0 : issue '20231116145245'

temporary workaround in glue 1.7 : base::format(mydatetime_posixt, format = "%Y-%m-%d %H:%M:%S")

glue 1.6.2 : ok `'2023-11-16 14:52:45'`

glue 1.7.0 : issue `'20231116145245'`