Postgres: substring generates numeric arguments instead of integer

[dfrankow]

Brief description of the problem:

library(dbplyr)

translate_sql(substring('abcdefghijklmno', 6,7))
#> Expected: <SQL> substring('abcdefghijklmno', 6, 7)
#> Actual: <SQL> substring('abcdefghijklmno', 6.0, 7.0)

It looks like there was a fix here, but almost two years later, I don't seem to have it. I don't know which version the fix went into.

Maybe I have an old version?

> packageVersion("dbplyr")
[1] ‘1.4.2’
> packageVersion("tidyverse")
[1] ‘1.3.0’

I tried dbplyr from the vanilla tidyverse install (1.3.0), then I also tried installing dbplyr from github:

devtools::install_github("tidyverse/dbplyr")

In both cases it looks like the numbers become float (numeric), and that makes Postgres (11) cranky in some other code I have:

Error in postgresqlExecStatement: RS-DBI driver: (could not Retrieve the result : ERROR:  function pg_catalog.substring(text, numeric, numeric) does not exist.

[dfrankow]

FYI I was trying to use this to make up rounded dates: floor_date didn't work, I tried substring(1,7)+'-01'). I ended up working around this with:

          started_at = date_trunc("year", started_at))

which worked in dbplyr.

So while this is still an issue, it is not a blocker for me.

[hadley]

Can you please provide a minimal reproducible example using the reprex package? The goal of a reprex is to make it as easy as possible for me to recreate your problem so that I can fix it. If you've never made a minimal reprex before, there is lots of good advice here.

[dfrankow]

I am having a hard time understanding reprex, but my attempt is below. I used reprex(venue="R", si=TRUE). Almost all those libraries are superfluous to reproducing, only dbplyr and its dependencies are needed.

I would only do this for you, since you have provided so much value in the past.

library(dbplyr)

translate_sql(substring('abcdefghijklmno', 6,7))
#> <SQL> substring('abcdefghijklmno', 6.0, 7.0)





devtools::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value                       
#>  version  R version 3.5.2 (2018-12-20)
#>  os       macOS Mojave 10.14.6        
#>  system   x86_64, darwin15.6.0        
#>  ui       X11                         
#>  language (EN)                        
#>  collate  en_US.UTF-8                 
#>  ctype    en_US.UTF-8                 
#>  tz       America/Chicago             
#>  date     2020-01-27                  
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version date       lib source        
#>  assertthat    0.2.0   2017-04-11 [1] CRAN (R 3.5.0)
#>  backports     1.1.3   2018-12-14 [1] CRAN (R 3.5.0)
#>  callr         3.3.2   2019-09-22 [1] CRAN (R 3.5.2)
#>  cli           1.1.0   2019-03-19 [1] CRAN (R 3.5.2)
#>  crayon        1.3.4   2017-09-16 [1] CRAN (R 3.5.0)
#>  DBI           1.0.0   2018-05-02 [1] CRAN (R 3.5.0)
#>  dbplyr      * 1.4.2   2019-06-17 [1] CRAN (R 3.5.2)
#>  desc          1.2.0   2018-05-01 [1] CRAN (R 3.5.0)
#>  devtools      2.2.1   2019-09-24 [1] CRAN (R 3.5.2)
#>  digest        0.6.18  2018-10-10 [1] CRAN (R 3.5.0)
#>  dplyr         0.8.3   2019-07-04 [1] CRAN (R 3.5.2)
#>  ellipsis      0.3.0   2019-09-20 [1] CRAN (R 3.5.2)
#>  evaluate      0.12    2018-10-09 [1] CRAN (R 3.5.0)
#>  fs            1.3.1   2019-05-06 [1] CRAN (R 3.5.2)
#>  glue          1.3.0   2018-07-17 [1] CRAN (R 3.5.0)
#>  highr         0.7     2018-06-09 [1] CRAN (R 3.5.0)
#>  htmltools     0.4.0   2019-10-04 [1] CRAN (R 3.5.2)
#>  knitr         1.26    2019-11-12 [1] CRAN (R 3.5.2)
#>  magrittr      1.5     2014-11-22 [1] CRAN (R 3.5.0)
#>  memoise       1.1.0   2017-04-21 [1] CRAN (R 3.5.0)
#>  pillar        1.4.2   2019-06-29 [1] CRAN (R 3.5.2)
#>  pkgbuild      1.0.6   2019-10-09 [1] CRAN (R 3.5.2)
#>  pkgconfig     2.0.2   2018-08-16 [1] CRAN (R 3.5.0)
#>  pkgload       1.0.2   2018-10-29 [1] CRAN (R 3.5.0)
#>  prettyunits   1.0.2   2015-07-13 [1] CRAN (R 3.5.0)
#>  processx      3.4.1   2019-07-18 [1] CRAN (R 3.5.2)
#>  ps            1.3.0   2018-12-21 [1] CRAN (R 3.5.0)
#>  purrr         0.3.3   2019-10-18 [1] CRAN (R 3.5.2)
#>  R6            2.3.0   2018-10-04 [1] CRAN (R 3.5.0)
#>  Rcpp          1.0.3   2019-11-08 [1] CRAN (R 3.5.2)
#>  remotes       2.1.0   2019-06-24 [1] CRAN (R 3.5.2)
#>  rlang         0.4.1   2019-10-24 [1] CRAN (R 3.5.2)
#>  rmarkdown     1.11    2018-12-08 [1] CRAN (R 3.5.0)
#>  rprojroot     1.3-2   2018-01-03 [1] CRAN (R 3.5.0)
#>  sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 3.5.0)
#>  stringi       1.2.4   2018-07-20 [1] CRAN (R 3.5.0)
#>  stringr       1.4.0   2019-02-10 [1] CRAN (R 3.5.2)
#>  testthat      2.3.0   2019-11-05 [1] CRAN (R 3.5.2)
#>  tibble        2.1.3   2019-06-06 [1] CRAN (R 3.5.2)
#>  tidyselect    0.2.5   2018-10-11 [1] CRAN (R 3.5.0)
#>  usethis       1.5.1   2019-07-04 [1] CRAN (R 3.5.2)
#>  withr         2.1.2   2018-03-15 [1] CRAN (R 3.5.0)
#>  xfun          0.4     2018-10-23 [1] CRAN (R 3.5.0)
#>  yaml          2.2.0   2018-07-25 [1] CRAN (R 3.5.0)
#> 
#> [1] /Library/Frameworks/R.framework/Versions/3.5/Resources/library

[hadley]

@dfrankow sorry, must've been in rush and I didn't read your issue fully — your initial description was fine.

[hadley]

The problem is that you're using substring() which isn't translated because it's not an R function. You want to use the R function substr():

library(dbplyr)

translate_sql(substring('abcdefghijklmno', 6,7))
#> <SQL> substring('abcdefghijklmno', 6.0, 7.0)
translate_sql(substr('abcdefghijklmno', 6,7))
#> <SQL> SUBSTR('abcdefghijklmno', 6, 2)

^{Created on 2020-09-22 by the reprex package (v0.3.0.9001)}

[dfrankow]

Thanks for the reply!

I don't understand "substring is not an R function". It looks like an R function to my naive eyes (see below). Maybe you mean it's not an internal function.

However, this work-around works for me. You don't have to reply.

Substring docs here.

Code saying it's in base:

> substring
function (text, first, last = 1000000L) 
{
    if (!is.character(text)) 
        text <- as.character(text)
    n <- max(lt <- length(text), length(first), length(last))
    if (lt && lt < n) 
        text <- rep_len(text, length.out = n)
    .Internal(substr(text, as.integer(first), as.integer(last)))
}
<bytecode: 0x7f91b92399a0>
<environment: namespace:base>

[hadley]

Oh shit, you're right. I got confused again because I never use substring(). I'll add it as an additional translation to all the backends.

[dfrankow]

As you like. Thanks for the tidyverse!