Inconsistency between ifelse and case_when using dbplyr

[tidyverse]

Inconsistency between ifelse and case_when using dbplyr

[edgararuiz-zz]

Hi, on the surface, it looks like the strategy that helped us resolve the use of is_null() as a filter() and as a mutate() may also need to be implemented in case_when(). I'll take a look.

@cderv commented on Aug 27, 2018, 6:01 AM UTC:

First, this is a {dbplyr} issue. When you have one, please fill them in dbplyr GH issue.

This is why it is related to some dbplyr issues: tidyverse/dbplyr#112, tidyverse/dbplyr#102 and the PR tidyverse/dbplyr#142 that should solve those. I think it should also solve your issue.

This PR replaces the last part with when(TRUE) by a ELSE clause. You can try by intalling the branch (devtools::install_github("tidyverse/dbplyr#142")) then the translation for case_when will use a ELSE clause.

# to get the PR
# devtools::install_github("tidyverse/dbplyr#142")
library(dbplyr)
# The translation as a ELSE clause
translate_sql(
  case_when(
    col1 == col2 ~ "a",
    TRUE ~ "b")
)
#> <SQL> CASE
#> WHEN ("col1" = "col2") THEN ('a')
#> ELSE ('b')
#> END

# IF ELSE clause is different
translate_sql(
  if_else(col1 == col2, "a","b")
)
#> <SQL> CASE WHEN ("col1" = "col2") THEN ('a') WHEN NOT("col1" = "col2") THEN ('b') END

# but you can recreate not using `TRUE~"b`
translate_sql(
  case_when(
    col1 == col2 ~ "a",
    !col1 == col2 ~ "b")
)
#> <SQL> CASE
#> WHEN ("col1" = "col2") THEN ('a')
#> WHEN (NOT("col1" = "col2")) THEN ('b')
#> END

Created on 2018-08-27 by the reprex package (v0.2.0).

You could try if this fix also deals with your exception that you linked to.

[simon-anasta]

Sorry, not convinced that the original issue has been correctly understood: Previous discussion was focused on the solution for case_when(..., TRUE ~ "b") being translated to ELSE 'b'. This is not the issue I am encountering.

The issue appears to be the SQL syntax that the two commands ifelse and case_when get translated into. The case_when syntax does not appear to be valid SQL.

# libraries
library(DBI)
library(dplyr)
library(dbplyr)

# establish connection to database table
connection_string = "database.specific.string"
# mine looks something like "DRIVER=...; Trusted_Connection=...; DATABASE=...' SERVER=..."
db_connection = dbConnect(odbc::odbc(), .connection_string = connection_string)
my_table = tbl(db_connection, from = my_table_name)

# attempted query
tmp = my_table %>%
    mutate(new_col = case_when(col1 == col2 ~ "a",
                               col1 != col2 ~ "b"))

# check SQL code for query
show_query(tmp)

The resulting SQL query is:

SELECT 
    col1, col2,
    CASE
       WHEN CONVERT(BIT, IIF(col1 = col2, 1.0, 0.0))) THEN ('a')
       WHEN CONVERT(BIT, IIF(col1 <> col2, 1.0, 0.0))) THEN ('b')
    END AS new_col
FROM my_database.my_table_name

Running this code throws an error

An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'

However the ifelse query works as expected:

# attempted query
tmp = my_table %>%
    mutate(new_col = ifelse(col1 == col2, "a", "b"))

# check SQL code for query
show_query(tmp)

The resulting SQL query is:

SELECT 
    col1, col2,
    CASE
       WHEN (CONVERT(BIT, IIF(col1 = col2, 1.0, 0.0))) = TRUE) THEN ('a')
       WHEN (CONVERT(BIT, IIF(col1 = col2, 1.0, 0.0))) = FALSE) THEN ('b')
    END AS new_col
FROM my_database.my_table_name

The difference between these two queries is that only ifelse results in an appended = TRUE or = FALSE, while case_when does not. This appears to be the cause.

I propose that = TRUE be appended to the syntax for case_when.

Note that in both cases the SQL syntax have been produced using show_query. Using translate_sql to produce the SQL code (as per @cdev 's response above) does not produce the SQL syntax that gets run on the server.

[cderv]

Thanks for the clarification.

Your example is not a reprex so it was difficult to reproduce. However, looking up, I assume your example is using a Microsoft SQL server, am I right ? The special CONVERT(BIT, IIF are only added for mssql backend. It is a special translation for == or !=.

dbplyr/R/db-odbc-mssql.R

Lines 45 to 46 in ce9273e

	`!=` = mssql_logical_infix("!="),
	`==` = mssql_logical_infix("="),

dbplyr/R/db-odbc-mssql.R

Lines 194 to 206 in ce9273e

	mssql_logical_infix <- function(f) {
	assert_that(is_string(f))
	f <- toupper(f)
	function(x, y) {
	condition <- build_sql(x, " ", sql(f), " ", y)
	if (sql_current_select()) {
	sql_expr(convert(BIT, iif(!!condition, 1, 0)))
	} else {
	condition
	}
	}
	}

I don't think the standard odbc case_when translation is incorrect and incoherent with standard ifelse but instead SQL server needs a special translation for case_when like it has for if, ifelse and if_else.

dbplyr/R/db-odbc-mssql.R

Lines 57 to 59 in ce9273e

	`if` = mssql_sql_if,
	if_else = function(condition, true, false) mssql_sql_if(condition, true, false),
	ifelse = function(test, yes, no) mssql_sql_if(test, yes, no),

dbplyr/R/db-odbc-mssql.R

Lines 225 to 236 in ce9273e

	mssql_sql_if <- function(cond, if_true, if_false = NULL) {
	build_sql(
	"CASE",
	" WHEN ((", cond, ") = 'TRUE')", " THEN (", if_true, ")",
	if (!is.null(if_false)){
	build_sql(" WHEN ((", cond, ") = 'FALSE')", " THEN (", if_false, ")")
	} else {
	build_sql(" ELSE ('')")
	},
	" END"
	)
	}

In this code, condition is translated using logical infix translation linked above.

This is a MSSQL translation issue but I don't have a SQL server available to go further and test what should be done.

[simon-anasta]

You're welcome. Yes, my example is using a Microsoft SQL server.

If it is helpful, I may be able to test some options on the server - though I'm not familiar with how you usually do this.

[cderv]

It would be helpful to have the correct SQL query for case_when on microsoft SQL server then a translation could be added after some tests.

In fact, I do not know mssql and I wonder why CONVERT(BIT, IIF is needed. I do not see it, in the doc. So it would make sense to be consistent with if_else translation but not sure if necessary to keep the same translation.

[simon-anasta]

I do not think CONVERT(BIT, IIF is required either. I have certainly never needed anything like it. IIF in SQL server is very similar to if_else in R.

Should they be useful, below are some code examples drawn for SQL server drawn from my projects:

SELECT
     /* regular selection */
	column_0

     /* two simple case when statements */
	,CASE WHEN column_1 BETWEEN 0 AND 3 THEN column_2 ELSE NULL END AS column_1
	,CASE WHEN column_3 = '1' THEN 1 ELSE 0 END AS column_4

     /* a case when with multiple options */
	,CASE WHEN column_5 <= 30 THEN 'type1'
           WHEN column_6 <= 50 THEN 'type2'
           ELSE 'type3'
      END AS column_6

     /* the else is optional; NULL is used if no else is provided, these two are equivalent */
	,CASE WHEN column_7 = 1 column_8 ELSE NULL END AS column_7
	,CASE WHEN column_7 = 1 column_8 END AS column_7

     /* logical conditions can be combined */
	,CASE WHEN column_8 = 1 AND column_9 = 0 THEN 1 ELSE 0 END AS column_10

     /* IIF is like a binary case-when, these two are equivalent */
     ,CASE WHEN column_11 = 1 THEN 'a' ELSE 'b' END AS column_11
     ,IFF(column_11 = 1, 'a', 'b') AS column_11

     /* an alternative syntax that */
     ,CASE column_12
          WHEN 1 THEN 'a'
          WHEN 2 THEN 'b'
          WHEN 3 THEN 'c'
          ELSE 'd'
      END column_13
     /* which is equivalent to the following */
     ,CASE 
          WHEN column_12 = 1 THEN 'a'
          WHEN column_12 = 2 THEN 'b'
          WHEN column_12 = 3 THEN 'c'
          ELSE 'd'
      END column_13

FROM database.schema.table

[edgararuiz-zz]

Ok, it looks like @cderv 's PR fixes the TRUE ~ x portion, and PR #103 removes the unnecessary BIT operations:

library(devtools)
install_github("tidyverse/dbplyr", ref = github_pull(103))
#> Skipping install of 'dbplyr' from a github remote, the SHA1 (3a1a8931) has not changed since last install.
#>   Use `force = TRUE` to force installation

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

mf <- lazy_frame(x = 1, y = 1, src = simulate_mssql())

mf %>% 
  mutate(z = case_when(x == y ~ 1, TRUE ~ 2)) %>% 
  show_query()
#> <SQL> SELECT `x`, `y`, CASE
#> WHEN (`x` = `y`) THEN (1.0)
#> WHEN (TRUE) THEN (2.0)
#> END AS `z`
#> FROM `df`

Created on 2018-09-21 by the reprex package (v0.2.0).