Why is the native pipe `|>` not a function?

ablack3 · July 11, 2023, 9:35pm

It seems like literally every operator in R is a function ... except for the new native pipe |>

Why is this the case and what exactly is |>?
Does it have a definition somewhere that I can read?

library(magrittr)
1:2 |> sum()
#> [1] 3

is.function(`%>%`)
#> [1] TRUE
is.function(`<-`)
#> [1] TRUE
is.function(`+`)
#> [1] TRUE
is.function(`if`)
#> [1] TRUE
is.function(`|>`)
#> Error in eval(expr, envir, enclos): object '|>' not found

^{Created on 2023-07-11 with reprex v2.0.2}

technocrat · July 12, 2023, 1:54am

Despite the Official Received Wisdom

Everything in R is a function

syntax and its sugar, like the native pipe seems to get a past. Where we once wrote f(lhe,rhe) we can now write lhe |> rhe. (left hand expression, right hand expression). The pipe isn't unique in being a non-function. Check out everything on the {base}help page down at the bottom in the link to{misc}`.

AlexisW · July 12, 2023, 12:35pm

The help page ?`|>` contains some more details:

Currently, pipe operations are implemented as syntax transformations. So an expression written as x |> f(y) is parsed as f(x, y) .

And one of the examples illustrates it directly:

quote(mtcars |> subset(cyl == 4) |> nrow())
#> nrow(subset(mtcars, cyl == 4))

^{Created on 2023-07-12 with reprex v2.0.2}
So this replacement happens before the actual processing of the code.

Another example:

is.function(`<-`)
#> [1] TRUE
is.function(`->`)
#> Error in eval(expr, envir, enclos): object '->' not found
quote( 5 -> a )
#> a <- 5

^{Created on 2023-07-12 with reprex v2.0.2}

As to where to find its code definition, I'm not fully sure, as this is heavy C and Bison code, but I believe it's this function xxpipe defined here, simplified code:

static SEXP xxpipe(SEXP lhs, SEXP rhs, YYLTYPE *lloc_rhs)
{
    SEXP ans;

    SEXP fun = CAR(rhs);
    SEXP args = CDR(rhs);

    PRESERVE_SV(ans = lcons(fun, lcons(lhs, args)));
    return ans;
}

Where I think lcons() is to create a language construct, using the content of lhs as argument to rhs.

@technocrat

I don't understand this sentence, is a link missing?

nirgrahamuk · July 12, 2023, 2:23pm

You did well to find that xxpipe definition, I peeked at that commit history and there was this gem :

The pipe implementation as a syntax transformation was motivated by
suggestions from Jim Hester and Lionel Henry

ablack3 · July 12, 2023, 2:32pm

Thank you for the explanation! Here is why I am asking. I'm experimenting with a way to extend dbplyr sql translations. In particular here is an example of adding a dateadd function that translates to the correct sql on different database systems. This is a minimal example of what I'm thinking just to get the idea across. There are issues with it though (i.e. it only works with the Magrittr pipe).

Anyway if you have an idea of how to do this I would very much appreciate any suggestions.

Thanks!

dateadd <- function(date, number, interval = "day") {

  dot <- get(".", envir = parent.frame())
  
  sql <- switch (class(dot$src$con)[1],
                 "duckdb_connection" = glue::glue("({date} + {number}*INTERVAL'1 {interval}')"),
                 "redshift" = glue::glue("DATEADD({interval}, {number}, {date})"),
                 "oracle" = glue::glue("({date} + NUMTODSINTERVAL({number}, 'day'))"),
                 "postgresql" = glue::glue("({date} + {number}*INTERVAL'1 {interval}')"),
                 "sql server" = glue::glue("DATEADD({interval}, {number}, {date})"),
                 "spark" = glue::glue("date_add({date}, {number})"),
                 "sqlite" = glue::glue("CAST(STRFTIME('%s', DATETIME({date}, 'unixepoch', ({number})||' {interval}s')) AS REAL)"),
                 "bigquery" = glue::glue("DATE_ADD({date}, INTERVAL {number} {toupper(interval)})"),
                 "snowflake" = glue::glue('DATEADD({interval}, {number}, {date})'),
                 stop(glue::glue("Connection type {class(dot$src$con)[1]} is not supported!"))
  )
  dbplyr::sql(as.character(sql))
}

con <- DBI::dbConnect(duckdb::duckdb())

date_tbl <- dplyr::copy_to(con, data.frame(date1 = as.Date("1999-01-01")),
                           name = "tmpdate", overwrite = TRUE, temporary = TRUE)

library(magrittr)
# works
date_tbl %>%  
 dplyr::mutate(date2 = !!dateadd("date1", 1, interval = "year")) 
#> # Source:   SQL [1 x 2]
#> # Database: DuckDB 0.8.2-dev77 [root@Darwin 21.6.0:R 4.2.2/:memory:]
#>   date1      date2     
#>   <date>     <date>    
#> 1 1999-01-01 2000-01-01

# fails
dplyr::mutate(date_tbl, date2 = !!dateadd("date1", 1, interval = "year")) 
#> Error in get(".", envir = parent.frame()): object '.' not found

# fails
date_tbl |>  
  dplyr::mutate(date2 = !!dateadd("date1", 1, interval = "year")) 
#> Error in get(".", envir = parent.frame()): object '.' not found

DBI::dbDisconnect(con, shutdown = TRUE)

^{Created on 2023-07-12 with reprex v2.0.2}

nirgrahamuk · July 12, 2023, 2:59pm

There is almost certainly a better way to do this, but what I write here at least 'works'

dateadd <- function(date, number, interval = "day") {
  
  dot <- try(get(".", envir = parent.frame()),silent = TRUE)
  if(inherits(dot, "try-error")){
    con_object <- try(get("con", envir = parent.frame()))
  } else {
    con_object <- dot$src$con
  }
  sql <- switch (class(con_object)[1],
                 "duckdb_connection" = glue::glue("({date} + {number}*INTERVAL'1 {interval}')"),
                 "redshift" = glue::glue("DATEADD({interval}, {number}, {date})"),
                 "oracle" = glue::glue("({date} + NUMTODSINTERVAL({number}, 'day'))"),
                 "postgresql" = glue::glue("({date} + {number}*INTERVAL'1 {interval}')"),
                 "sql server" = glue::glue("DATEADD({interval}, {number}, {date})"),
                 "spark" = glue::glue("date_add({date}, {number})"),
                 "sqlite" = glue::glue("CAST(STRFTIME('%s', DATETIME({date}, 'unixepoch', ({number})||' {interval}s')) AS REAL)"),
                 "bigquery" = glue::glue("DATE_ADD({date}, INTERVAL {number} {toupper(interval)})"),
                 "snowflake" = glue::glue('DATEADD({interval}, {number}, {date})'),
                 stop(glue::glue("Connection type {class(con_object)[1]} is not supported!"))
  )
  dbplyr::sql(as.character(sql))
}

ablack3 · July 12, 2023, 5:59pm

nirgrahamuk:

  dot <- try(get(".", envir = parent.frame()),silent = TRUE)
  if(inherits(dot, "try-error")){
    con_object <- try(get("con", envir = parent.frame()))
  } else {
    con_object <- dot$src$con
  }

I think this will only work if the connection object is named con. I generally won't know the name of the user's connection object will be though. If it is conn then it fails.

# Function that will be exported from a package ----
dateadd <- function(date, number, interval = "day") {

  dot <- try(get(".", envir = parent.frame()), silent = TRUE)
  
  if (inherits(dot, "try-error")) {
    con_object <- try(get("con", envir = parent.frame()))
  } else {
    con_object <- dot$src$con
  }
  
  sql <- switch (class(con_object)[1],
                 "duckdb_connection" = glue::glue("({date} + {number}*INTERVAL'1 {interval}')"),
                 "postgresql" = glue::glue("({date} + {number}*INTERVAL'1 {interval}')"),
                 "redshift" = glue::glue("DATEADD({interval}, {number}, {date})"),
                 stop(glue::glue("Connection type {class(dot$src$con)[1]} is not supported!"))
  )
  dbplyr::sql(as.character(sql))
}

# User's code ----
conn <- DBI::dbConnect(duckdb::duckdb())

date_tbl <- dplyr::copy_to(conn, data.frame(date1 = as.Date("1999-01-01")),
                           name = "tmpdate", overwrite = TRUE, temporary = TRUE)

library(magrittr)

date_tbl %>%  
 dplyr::mutate(date2 = !!dateadd("date1", 1, interval = "year")) 
#> # Source:   SQL [1 x 2]
#> # Database: DuckDB 0.8.2-dev77 [root@Darwin 21.6.0:R 4.2.2/:memory:]
#>   date1      date2     
#>   <date>     <date>    
#> 1 1999-01-01 2000-01-01

# fails
dplyr::mutate(date_tbl, date2 = !!dateadd("date1", 1, interval = "year")) 
#> Error in get("con", envir = parent.frame()) : object 'con' not found
#> Error in dot$src: $ operator is invalid for atomic vectors

# fails
date_tbl |>  
  dplyr::mutate(date2 = !!dateadd("date1", 1, interval = "year")) 
#> Error in get("con", envir = parent.frame()) : object 'con' not found
#> Error in dot$src: $ operator is invalid for atomic vectors

DBI::dbDisconnect(conn, shutdown = TRUE)

^{Created on 2023-07-12 with reprex v2.0.2}

nirgrahamuk · July 12, 2023, 8:08pm

Good point. I thought the purpose of dbplyr was for you to run dplyr code on connections, i would investigate how this is normally done. If i have time to dig around on this i will out of curiosity.

technocrat · July 12, 2023, 8:13pm

Sorry to be obscure. It’s a reference, rather than a link. I meant the help page for base has a misc section at the very bottom that leads to the large where all the stuff named with symbols is to be found

AlexisW · July 12, 2023, 8:57pm

I'm curious, if I just type ?base I get a very short description with no {misc} section. Which help page are you referring to?

AlexisW · July 12, 2023, 9:38pm

With this dot that can indeed only work if using a magrittr pipe (that defines .). Without magrittr, I tried playing a bit with parent.frame() but I don't think mutate() allow us to access its .data argument.

Also, the idea of getting the value from the parent frame feels a bit... shoddy. Another solution, still far from ideal, would be to pass the connection as an argument:

date_tbl |> 
  dplyr::mutate(date2 = !!dateadd("date1", 1, interval = "year", con = date_tbl))

It's a bit unwieldy but explicit. Short of rewriting dbpyr internals I can't really think of a better way.

EconProf · July 12, 2023, 10:03pm

Go to the packages tab and click on the base package. Scroll just pass the -- Z -- section to -- misc --.

AlexisW · July 12, 2023, 10:29pm

I see, thank you. Although I would say the vast majority of them are still functions.

technocrat · July 12, 2023, 10:51pm

Sorry again. Go to the index part?

nirgrahamuk · July 13, 2023, 8:29am

just following up on this; I dug around a little, and it seems, only hacky approaches will be able to be offered just as long as there is not a proper extensibility approach for dbplyr ; for reference see

github.com/tidyverse/dbplyr

function translations for DATEDIFF and DATEADD

opened 02:25PM - 15 Dec 22 UTC

ablack3

feature

**Feature Request** DATEDIFF and DATEADD are commonly used SQL functions with… slightly different syntax on different database platforms. I would like to use dbplyr's translation approach to generate SQL statements DATEDIFF and DATEADD that are correct for various database platforms. I can use sql passthrough but then the dplyr code won't be translated and work on multiple database platforms. I would like to write one dplyr expression to calculate the difference between dates (or addition of an interval to a date) that will be translated correctly on both sql server and postgres. Below is a reprex that generates correct SQL on postgres but not sql server. ``` r library(tidyverse) df <- tibble(date1 = as.Date("2020-02-02"), date2 = as.Date("2020-02-03")) db <- dbplyr::lazy_frame(df, con = dbplyr::simulate_postgres()) # Date add db %>% mutate(date3 = date1 + lubridate::years(1)) %>% show_query() #> <SQL> #> SELECT *, `date1` + CAST('1 years' AS INTERVAL) AS `date3` #> FROM `df` # Date diff db %>% mutate(days = date2 - date1) %>% show_query() #> <SQL> #> SELECT *, `date2` - `date1` AS `days` #> FROM `df` db <- dbplyr::lazy_frame(df, con = dbplyr::simulate_mssql()) # Date add db %>% mutate(date3 = date1 + lubridate::years(1)) %>% show_query() #> <SQL> #> SELECT *, `date1` + years(1.0) AS `date3` #> FROM `df` # Date diff db %>% mutate(days = date2 - date1) %>% show_query() #> <SQL> #> SELECT *, `date2` - `date1` AS `days` #> FROM `df` ``` <sup>Created on 2022-12-15 with [reprex v2.0.2](https://reprex.tidyverse.org)</sup>

one could make the parent.frame checking approach more elaborate, for example, you can get the names of all the listed objects in the parent.frame, find the first one that is a tbl_lazy and get a connetion from that

library(dbplyr)
library(dplyr)
myfunc <- function(){
  nms <-ls(envir = parent.frame(),all.names = TRUE)
  is_tbl_lazy <- lapply(nms, \(x)inherits(get(x,envir=parent.frame()),what = "tbl_lazy"))
  names(is_tbl_lazy) <- nms
  get_tbl <- get(names(head(which(is_tbl_lazy==TRUE),n=1)),envir=parent.frame())
  class(dbplyr::remote_con(get_tbl))[[1]]
}
conn <- DBI::dbConnect(duckdb::duckdb())

date_tbl <- dplyr::copy_to(conn, data.frame(date1 = as.Date("1999-01-01")),
                           name = "tmpdate", overwrite = TRUE, temporary = TRUE)

date_tbl |>
  mutate(what_connection=!!myfunc())

system · July 20, 2023, 8:29am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.