dplyr is translating FALSE to "0" in SQL.

[iangow]

Translating FALSE to 0 works in SQLite, which has few real types. But it breaks existing PostgreSQL code.

library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)
y <- c(1, 2, 3, NA)
       
df <- 
    memdb_frame(y) %>%
    mutate(y_big = coalesce(y > 2, FALSE))

df
#> Source:     lazy query [?? x 2]
#> Database:   sqlite 3.11.1 [:memory:]
#> 
#>       y y_big
#>   <dbl> <int>
#> 1     1     0
#> 2     2     0
#> 3     3     1
#> 4    NA     0
    
pg <- src_postgres()

df <-
    copy_to(pg, tibble(y),
            name = "adhaksdhjkalsda",
            overwrite = TRUE) %>%
    mutate(y_big = coalesce(y > 2, FALSE))  
df %>% show_query()
#> <SQL>
#> SELECT "y", COALESCE("y" > 2.0, 0) AS "y_big"
#> FROM "adhaksdhjkalsda"

df
#> Source:     lazy query [?? x 2]
#> Database:   postgres 9.6.2 [[email protected]:5432/crsp]
#> Error in postgresqlExecStatement(conn, statement, ...): RS-DBI driver: (could not Retrieve the result : ERROR:  COALESCE types boolean and integer cannot be matched
#> LINE 1: SELECT "y", COALESCE("y" > 2.0, 0) AS "y_big"
#>                                         ^
#> )

df <-
    copy_to(pg, tibble(y),
            name = "adhaksdhjkalsda",
            overwrite = TRUE) %>%
    mutate(y_big = coalesce(y > 2, sql("FALSE")))  
df %>% show_query()
#> <SQL>
#> SELECT "y", COALESCE("y" > 2.0, FALSE) AS "y_big"
#> FROM "adhaksdhjkalsda"

df
#> Source:     lazy query [?? x 2]
#> Database:   postgres 9.6.2 [[email protected]:5432/crsp]
#> 
#>       y y_big
#>   <dbl> <lgl>
#> 1     1 FALSE
#> 2     2 FALSE
#> 3     3  TRUE
#> 4    NA FALSE

[hadley]

See #2052 for discussion, and https://www.postgresql.org/docs/9.1/static/datatype-boolean.html, which suggests that 0 and 1 are ok.

[iangow]

Documentation seems literally correct: '0' and '1' are fine, but 0 and 1 are not:

library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)
y <- c(1, 2, 3, NA)

pg <- src_postgres()

df <-
    copy_to(pg, tibble(y),
            name = "adhaksdhjkalsda",
            overwrite = TRUE) %>%
    mutate(y_big = coalesce(y > 2, FALSE))  
df %>% show_query()
#> <SQL>
#> SELECT "y", COALESCE("y" > 2.0, 0) AS "y_big"
#> FROM "adhaksdhjkalsda"

df <-
    copy_to(pg, tibble(y),
            name = "adhaksdhjkalsda",
            overwrite = TRUE) %>%
    mutate(y_big = coalesce(y > 2, sql("'0'")))  
df %>% show_query()
#> <SQL>
#> SELECT "y", COALESCE("y" > 2.0, '0') AS "y_big"
#> FROM "adhaksdhjkalsda"

df
#> Source:     lazy query [?? x 2]
#> Database:   postgres 9.6.2 [[email protected]:5432/crsp]
#> 
#>       y y_big
#>   <dbl> <lgl>
#> 1     1 FALSE
#> 2     2 FALSE
#> 3     3  TRUE
#> 4    NA FALSE

[iangow]

Literals TRUE and FALSE appear to be part of SQL standard (also see here), even if PostgreSQL is the only system to use these.

[hadley]

Hmmm, and there's currently no double dispatch on vector type and database backend.

But generally dplyr translation assumes SQL-92, so maybe it's ok to just translate to '0' and '1. Will need to add a backend test.

[iangow]

Seems to work OK with SQLite (if it broke there, then clearly not worth trying):

library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)
y <- c(1, 2, 3, NA)
       
df <- 
    memdb_frame(y) %>%
    mutate(y_big = coalesce(y > 2, sql("'0'")))

df
#> Source:     lazy query [?? x 2]
#> Database:   sqlite 3.11.1 [:memory:]
#> 
#>       y y_big
#>   <dbl> <int>
#> 1     1     0
#> 2     2     0
#> 3     3     1
#> 4    NA     0

[hadley]

Unfortunately MySQL yields:

SELECT `x`, COALESCE(`x` > 0.0, '1') AS `y`
FROM `yxuqwdshce`

Source:     lazy query [?? x 2]
Database:   mysql 10.1.18-MariaDB [hadley@localhost:/test]
      x     y
  <dbl> <chr>
1     1     1
2    NA     1

[hadley]

I can't find any single translation that works across these three backends

[hadley]

In the long run, hopefully DBI can provide a new generic: r-dbi/DBI#172. In the short-run, need another method like sql_escape_string, i.e. sql_escape_logical that dispatches on the connection.

[iangow]

Thanks

I can't find any single translation that works across these three backends

It seems TRUE and FALSE work for MySQL (don't have to test) and PostgreSQL. There's no such thing as booleans in SQLite, so it seems that the type that needs to be converted there. Maybe that's what you're referring to with "no double dispatch on vector type and database backend."

[hadley]

Argh, it's worse than that since there seems to be a major logic failing and con doesn't get passed down to escape in many scenarios. This hasn't caused a problem in the past because it only affects literals.

[hadley]

Rather than continuing to painstaking thread the current connection through every function call, it'll probably be easier to generalise win_current_con() to current_con() and only set it in a few places.