Summarize across fails on Microsoft Azure SQL due to IIF statement

[somatusag]

Hello,

I'm having trouble getting across() to work in dbplyr due to what I think is an issue with Microsoft Azure SQL:

a %>% summarise(across(everything(), ~ sum(is.na(.))))

Throws the following error:

Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server] Parse error at line: 1, column: 45: Incorrect syntax near 'IS'. 'SELECT TOP 11 SUM(CAST(IIF((("MyColumn") IS NULL), 1, 0) AS BIT)) AS "MyColumn" FROM (SELECT "MyColumn" FROM "dbo"."MyTable") "q01"'

Running directly from Azure SQL:

SELECT IIF(1>2, 'YES', 'NO')

Also throws an error:

Parse error at line: 1, column: 13: Incorrect syntax near '>'.

This seems to be a documented issue with Azure SQL: https://techcommunity.microsoft.com/t5/azure-synapse-analytics/azure-synapse-analytics-dedicated-sql-pool-iif-statement-support/m-p/2659481#M16

Not sure if there is any workaround for the time being, but please let me know if I can provide any additional information.

Our DW version is: Microsoft Azure SQL Data Warehouse - 10.0.13727.0

[hadley]

Minimal reprex:

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

db <- lazy_frame(x = 1, y = 2, con = simulate_mssql())
db %>% summarise(across(everything(), ~ sum(is.na(.), na.rm = TRUE)))
#> <SQL>
#> SELECT
#>   SUM(CAST(IIF((`x` IS NULL), 1, 0) AS BIT)) AS `x`,
#>   SUM(CAST(IIF((`y` IS NULL), 1, 0) AS BIT)) AS `y`
#> FROM `df`

^{Created on 2022-04-28 by the reprex package (v2.0.1)}

The documentation says:

IIF is not supported in dedicated SQL pools in Azure Synapse Analytics.

We could possibly switch to using a CASE WHEN here, but that's a non-trivial change, and this code is already complex due to the peculiarities of SQL server.

[hadley]

Thanks for filing this bug report! Unfortunately because it's hard to fix and only affects one database engine, we don't have the development resources to fix at this time. It's our policy to close such issues to help stay focussed on the biggest problems, but the issue is still indexed by google, so if other people hit it, they'll be able to find it, and we can consider reopen it if it turns out to be a common problem. Thanks for reporting and I'm sorry we couldn't help more 😞.