Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

MSSQL regression in boolean/bit coercion #318

Closed
EarlGlynn opened this issue Jun 7, 2019 · 5 comments
Closed

MSSQL regression in boolean/bit coercion #318

EarlGlynn opened this issue Jun 7, 2019 · 5 comments
Labels
bug an unexpected problem or unintended behavior func trans 🌍 Translation of individual functions to SQL

Comments

@EarlGlynn
Copy link

EarlGlynn commented Jun 7, 2019

On R Studio Pro Server, this MS SQL query worked fine in R 3.4.2 and R.3.5.0 but fails in R 3.5.1 and R 3.6.0. [Part of a much larger query originally.]

ageIntervals <-
  fEncounter   %>%  
  head(10)  %>%
  summarize(n90plus = sum(as.integer(AGE_IN_YEARS >= 90), na.rm=TRUE))   %>%
  collect()

R 3.4.2 and 3.5.0 give this for show_query (everything worked fine)
sessionInfo: dbplyr_1.2.2 odbc_1.1.6 DBI_1.0.0

<SQL>
SELECT SUM(CAST(CONVERT(BIT, IIF("AGE_IN_YEARS" >= 90.0, 1.0, 0.0)) AS INT)) AS "n90plus"
FROM (SELECT  TOP 10 *
FROM XXXXXXX.XX_X_ENCOUNTER) "uyzjpitrzg"

R 3.5.1 and R 3.6.0 show this for show_query and fails:
sessionInfo: dbplyr_1.4.0 odbc_1.1.6 DBI_1.0.0

<SQL>
SELECT SUM(CAST("AGE_IN_YEARS" >= 90.0 AS INT)) AS "n90plus"
FROM (SELECT TOP(10) *
FROM XXXXXXX.XX_X_ENCOUNTER) "dbplyr_002"

The failure message:

Error: <SQL> 'SELECT SUM(CAST("AGE_IN_YEARS" >= 90.0 AS INT)) AS "n90plus" FROM (SELECT TOP(10) * FROM XXXXXXX.XX_X_ENCOUNTER) "dbplyr_001"' nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL
17.	stop(structure(list(message = "<SQL> 'SELECT SUM(CAST(\"AGE_IN_YEARS\" >= 90.0 AS INT)) AS \"n90plus

Both examples run on the same R Studio Pro server with the specified versions of R.

Both examples run with exact same database and same DB version: Microsoft SQL Server Version: 12.00.2000. The database is on Azure and I must specify the driver "ODBC Driver 17 for SQL Server".

@hadley
Copy link
Member

hadley commented Jun 7, 2019

I don't think this is related to R versions, but different versions of dbplyr.

Here's a minimal reprex for the problem:

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

lazy_frame(AGE_IN_YEARS = 10, con = simulate_mssql()) %>% 
  head(10)  %>%
  summarize(n90plus = sum(as.integer(AGE_IN_YEARS >= 90), na.rm = TRUE))
#> <SQL>
#> SELECT SUM(CAST(`AGE_IN_YEARS` >= 90.0 AS INT)) AS `n90plus`
#> FROM (SELECT TOP(10) *
#> FROM `df`) `dbplyr_001`

Created on 2019-06-07 by the reprex package (v0.2.1.9000)

@hadley
Copy link
Member

hadley commented Jun 7, 2019

@edgararuiz could you take a look?

@ianmcook
Copy link
Contributor

ianmcook commented Jul 9, 2019

Looks like this is related to the removal of mssql_logical_infix() in #103. In dbplyr 1.2.2, >= translated to mssql_logical_infix(">=") but it now translates to sql_infix(">=").

@hadley hadley added bug an unexpected problem or unintended behavior func trans 🌍 Translation of individual functions to SQL labels Apr 14, 2020
@hadley hadley changed the title dbplyr query of MS SQL database worked fine in R 3.5.0 but fails in R 3.5.1 MS SQL needs to translate FALSE & TRUE in two ways Sep 17, 2020
@hadley hadley changed the title MS SQL needs to translate FALSE & TRUE in two ways MSSQL regression in boolean/bit coercing Sep 17, 2020
@hadley hadley changed the title MSSQL regression in boolean/bit coercing MSSQL regression in boolean/bit coercion Sep 17, 2020
hadley added a commit that referenced this issue Sep 22, 2020
@hadley
Copy link
Member

hadley commented Sep 22, 2020

@ianmcook thanks! Do you use dbplyr with SQL server much?

@ianmcook
Copy link
Contributor

@hadley I have in the past, but I'm not these days

@hadley hadley closed this as completed in 3b1767d Sep 22, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug an unexpected problem or unintended behavior func trans 🌍 Translation of individual functions to SQL
Projects
None yet
Development

No branches or pull requests

3 participants