MSSQL translator should convert as.logical into CAST AS BIT instead of CAST AS BOOLEAN

[jcfisher]

In MS SQL Server, as.logical translates to CAST AS BOOLEAN. However, in SQL Server, it looks like BOOLEAN doesn't exist. It seems to use BIT instead of BOOLEAN.

It seems like this might be a simple fix. A small example follows. Let me know if there's anything I can do to help fix, if this is of interest.

library(tidyverse)
library(dbplyr)
con <- DBI::dbConnect(
  odbc::odbc(),
  driver = "{SQL Server}",
  database = "AdventureWorks2012",
  uid = "sqlfamily",
  pwd = "sqlf@m1ly",
  server = "mhknbn2kdz.database.windows.net",
  port = 1433
)

# Fails
con %>% 
  tbl(in_schema("Production", "WorkOrder")) %>% 
  transmute(large_order = as.logical(if_else(OrderQty < 5, 0L, 1L)))
# Error: <SQL> 'SELECT  TOP 10 "large_order" AS "large_order"
# FROM (SELECT "WorkOrderID", "ProductID", "OrderQty", "StockedQty", "ScrappedQty", "StartDate", "EndDate", "DueDate", "ScrapReasonID", "ModifiedDate", CAST(CASE WHEN ("OrderQty" < 5.0) THEN (0) # ELSE (1) END AS BOOLEAN) AS "large_order"
# FROM Production.WorkOrder) "zvtstdmghk"'
#   nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Type BOOLEAN is not a defined system type. 

# Works
con %>% 
  tbl(sql('SELECT CAST(CASE WHEN ("OrderQty" < 5.0) THEN (0) ELSE (1) END AS BIT) AS "large_order"
           FROM Production.WorkOrder'))
# # Source:   SQL [?? x 1]
# # Database: Microsoft SQL Server 12.00.1100[sqlfamily@mhknbn2kdz/AdventureWorks2012]
#    large_order
#          <lgl>
#  1        TRUE
#  2        TRUE
#  3        TRUE
#  4        TRUE
#  5        TRUE
#  6        TRUE
#  7       FALSE
#  8        TRUE
#  9       FALSE
# 10       FALSE
# # ... with more rows

[edgararuiz-zz]

Hi, yes, that is a bit of a challenge because logical tests in MS SQL work differently across SQL clauses. A short fix is to combine the two approaches, and create the 0/1 variable using regular R code, and then use sql() to cast the result into a logical. Here is an example using airlines data:

tbl(con, "airlines") %>%
   mutate(long_name = ifelse(nchar(name) > 15, 1 , 0)) %>%
   mutate(long_name = sql("CAST(long_name as BIT)"))

# Source:   lazy query [?? x 4]
# Database: Microsoft SQL Server
#   14.00.1000[dbo@LAPTOP-368HS2ML\SQLEXPRESS/datawarehouse]
   row_names name                 carrier long_name
   <chr>     <chr>                <chr>   <lgl>    
 1 3         1Time Airline        1T      FALSE    
 2 10        40-Mile Air          Q5      FALSE    
 3 13        Ansett Australia     AN      TRUE     
 4 14        Abacus International 1B      TRUE     
 5 15        Abelag Aviation      W9      FALSE    
 6 21        Aigle Azur           ZI      FALSE    
 7 22        Aloha Airlines       AQ      FALSE    
 8 24        American Airlines    AA      TRUE     
 9 28        Asiana Airlines      OZ      FALSE    
10 29        Askari Aviation      4K      FALSE    
# ... with more rows

This will at least allow you to avoid creating a tbl() from a SQL statement.

[jcfisher]

Thanks!

[hadley]

@edgararuiz we should change the default translation, right?