MS SQL needs to translate FALSE & TRUE in two ways

[robchallen]

Creating a new variable in a MSSQL dbplyr backed table that involves a logical value in R generates an SQL error as R TRUE gets converted to MSSQL TRUE which doesn't exist in the context of a select statement (...why? I don't know)

Workaround is not to use TRUE in R and use as.logical(1) instead.

library(dbplyr)
library(DBI)

#assume a DBI connection con

tmp = data.frame(test=c(1))
con %>% copy_to(tmp) 

# Created a temporary table named: ##tmp
tmp %>% mutate(test2 = TRUE) 

# Error: <SQL> 'SELECT TOP(11) "test", TRUE AS "test2" FROM "##tmp"'   nanodbc/nanodbc.cpp:1587: 42S22: [Microsoft][ODBC Driver 13 for SQL Serve
--

# workaround
tmp %>% mutate(test2 = as.logical(1))

[robchallen]

[#250] was a similar issue

[hadley]

We need to generate different translations of T/F depending what clause we're currently in. We've probably forgotten to pass apply this detail when escaping a literal TRUE or FALSE