Could || translate to concatenation?

[MichaelChirico]

dbplyr/R/backend-.R

Line 109 in 99a70f5

`||` = sql_infix("OR"),

IMO it's almost always a mistake to use || to mean OR in an SQL context -- it would only make sense to use || on a 1-row table, vanishingly rare.

|| is standard SQL for "concatenation":

https://stackoverflow.com/a/1374512/3576984

Therefore I would find it very natural to use || for concatenation inside {dbplyr} queries too (paste0 has always felt clunky 😉).

Looks like || ↔ OR has been true since time immemorial: 68186fc

But I don't see any discussion (admittedly, searching for an operator is hard).

[ejneer]

Sounds like a tough sell. I feel like I've used this pattern before:

tbl(con, "bools") |> mutate(z = x || y)
# Source:   SQL [?? x 3]
# Database: DuckDB 1.4.1 [ejneer@Darwin 24.6.0:R 4.4.3/:memory:]
  x     y     z    
  <lgl> <lgl> <lgl>
1 TRUE  FALSE TRUE 
2 TRUE  FALSE TRUE 
3 FALSE FALSE FALSE

and %||% already accomplishes concatenation

tbl(con, "bools") |> mutate(z = x %||% y)
# Source:   SQL [?? x 3]
# Database: DuckDB 1.4.1 [ejneer@Darwin 24.6.0:R 4.4.3/:memory:]
  x     y     z         
  <lgl> <lgl> <chr>     
1 TRUE  FALSE truefalse 
2 TRUE  FALSE truefalse 
3 FALSE FALSE falsefalse

[MichaelChirico]

Got it, I don't think I knew %||% works for concatentation. I see it mentioned here:

dbplyr/vignettes/translation-function.Rmd

Lines 162 to 166 in 0df138e

	Or use `||` for string concatenation (although most backends will translate `paste()` and `paste0()` for you):
	```{r}
	translate_sql(x %||% y, con = con)
	```

But I don't see it as a standard definition in {dbplyr} (again, it's a bit hard to search, so maybe I'm missing something, but regex ['"`]%\|\|% doesn't match anything here or in {duckplyr}).

I see in {bigrquery} that it gets the translation I'd expect, i.e., equivalent to R's null-coalescing operator:

https://github.com/r-dbi/bigrquery/blob/1054d0fd977f43620c2d69e627bd7b9eb4de1dfe/R/dplyr.R#L321

It looks like {RClickhouse} previously used %||% for concatenation, but no longer:

https://github.com/IMSMWU/RClickhouse/blob/1a54f1a34d31936565a3dddc1825e866e36c0188/R/dplyr.R#L70

IMSMWU/RClickhouse@15eadc9

[ejneer]

Yeah thats the vignette I had in mind. I haven't seen any other documentation of it. Good catch with bigrquery, I wasn't aware of any translations for %||%. It also predates the blurb in the vignette...

Thinking through it, I totally glossed over the implications of your original point...

...it would only make sense to use || on a 1-row table, vanishingly rare...

?Logic explicity says

Using vectors of more than one element in ‘&&’ or ‘||’ will give an error.

Which is exactly what I (in effect) did in my example above...

[MichaelChirico]

Yea, so in that sense, the translation to OR is not exactly faithful... but OTOH, the back-compatibility concerns probably make this a pretty hard sell.