str_detect always treats pattern as fixed string

[dlindelof]

When str_detect is used in a call to filter on a tbl, I expect pattern to be treated as a regular expression. Instead, it seems that it is treated as a fixed pattern:

> MASTER %>% filter(str_detect(FOO, "pen$")) %>% show_query
<SQL>
SELECT *
FROM "MASTER"
WHERE (INSTR("FOO", 'pen$') > 0)

The SQL INSTR function performs a case-insensitive search, and does not treat its second argument as a regex.

[colearendt]

What database are you using here?

[kieran-mace]

I have this issue too. I am using teradata.

[PNorvaisas]

I have the same problem with MySQL database. Need to collect data to be able to use patterns in str_detect.

[yutannihilation]

str_detect() can be translated as INSTR(), CHARINDEX(), or CHARINDEX(), but neither of them is a regex function.

dbplyr/R/db-postgres.r

Lines 68 to 70 in 79d2a03

	str_detect = function(string, pattern){
	sql_expr(strpos(!!string, !!pattern) > 0L)
	}

dbplyr/R/db-odbc-mssql.R

Lines 93 to 96 in 79d2a03

	str_detect = function(string, pattern){
	build_sql(
	"CHARINDEX(", pattern, ", ", string, ") > 0"
	)}

According to #151 (comment), this seems a design decision.

There's unfortunately no way that dbplyr can translate every stringr feature into every database backend.

You can directly specify a function particular to the backend, though. For example (this is only for illustration purpose and the SQLs are invalid for SQLite):

library(DBI)
library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, iris)

# MySQL has REGEXP
tbl(con, "iris") %>%
  filter(FOO %regexp% "pen$") %>%
  show_query
#> <SQL>
#> SELECT *
#> FROM `iris`
#> WHERE (`FOO` REGEXP 'pen$')

# Teradata has REGEXP_LIKE
tbl(con, "iris") %>%
  filter(regexp_like(FOO, "pen$")) %>%
  show_query
#> <SQL>
#> SELECT *
#> FROM `iris`
#> WHERE (REGEXP_LIKE(`FOO`, 'pen$'))

^{Created on 2018-10-12 by the reprex package (v0.2.1)}

[yutannihilation]

@KMace
BTW, for tidyverse/stringr#262, you might feel you were using regex, but it was, sadly, just a bug (c.f. #81)... The dev version of dbplyr will give you 0 rows with the very same code.

[kieran-mace]

@yutannihilation
Oh wow. Thanks for that heads up.

[hadley]

@colearendt do you want to take this one on too?