MS SQL mutate(ifelse()) translation not working

[bogdanrau]

I'm running into an issue when using mutate and ifelse (or if) on a SQL Server backend. The following sample query:

df <- tbl(con, in_schema("schema", "table_name")) %>%
   mutate(field = ifelse(field %in% c('test1', 'test2', 'test3'), 'string1', 'string2') %>%
   collect()

... will give the following translation:

SELECT CASE WHEN (("field" IN ('test1', 'test2', 'test3')) = 'TRUE') THEN ('string1') 
            WHEN (("field" IN ('test1', 'test2', 'test3')) =  'FALSE') THEN ('string2') 
            END AS "field"

Which will return an error. The correct translation should be:

SELECT CASE WHEN ("field" IN ('test1', 'test2', 'test3')) THEN 'string1' 
            WHEN ("field" NOT IN ('test1', 'test2', 'test3')) THEN 'string2' 
            END AS "field"

Will correcting this in the mssql_if function create any issues in other queries, or is there a better way to do mutate(ifelse()) in mssql?

dbplyr/R/db-odbc-mssql.R

Line 225 in 477480e

mssql_sql_if <- function(cond, if_true, if_false = NULL) {

[bogdanrau]

Sorry guys. Just opened up a ticket in dplyr instead (as suggested in the readme). Closing this guy. Opened tidyverse/dplyr#3632

[hadley]

No you should file here; I need to update the readme.

[bogdanrau]

Thanks Hadley. @edgararuiz, any insight on what a fix might be?

[edgararuiz-zz]

Ok, I think that the update may have to be done in the negating function here:

dbplyr/R/db-odbc-mssql.R

Line 183 in 477480e

mssql_not_sql_prefix <- function() {

I'll work on a PR for this

[edgararuiz-zz]

Quick update, it looks like your suggestion fixes this issue, but it negatively impacts the custom MS SQL infix function we're using, so I have to work some more on this

dbplyr/R/db-odbc-mssql.R

Line 194 in 477480e

mssql_logical_infix <- function(f) {

[bogdanrau]

Thanks for your help on this @edgararuiz! We're transitioning from a MySQL db to MSSQL and it's been super smooth thus far minus this kink.

[ablack3]

Did this issue get worked out or is there a workaround? I'm having the same problem.

[edgararuiz-zz]

@ablack3 - Yes, I have a PR that fixes it. The PR is ready to be reviewed: #103