dbplyr query of MS SQL database worked fine in R 3.5.0 but fails in R 3.5.1

tidyverse
1

Is this a bug or a feature I do not understand?

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 show this for show_query (everything worked fine)

<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:

<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\"\nFROM (SELECT TOP(10) *\nFROM blinded.HF_F_ENCOUNTER) \"dbplyr_001\"'\n nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL ", call = NULL, cppstack = NULL), class = c("odbc::odbc_error", "C++Error", "error", "condition")))

Bug or feature?

2

It is not so clear for me so I need confirmation :relaxed:

This is the SQL that works:

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

And this is the new SQL generated by dbplyr :

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"
  • what are the version of dbplyr ?
  • What is the version of the MSSQL database ?
  • Have you look into dbplyr issue on github to see if there is something related ?

It is possible that the translation have changed and do no work on an old database version for example

2 Likes
3

Thanks for your reply.

I edited the posting above to be clearer.

The show_query code that worked from older versions (R 3.4.2 and R 3.5.0) of R was shown firstfirst. That one had the “SUM(CAST(CONVERT(BIT …” construct. sessionInfo: dbplyr_1.2.2 odbc_1.1.6 DBI_1.0.0

I showed the code that failed with new versions (R 3.5.1 and R 3.6.0) of R last. That one had the “SUM(CAST(“AGE_IN_YEARS” …” construct. sessionInfo: dbplyr_1.4.0 odbc_1.1.6 DBI_1.0.0

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".

The release notes don’t seem to describe what may be the cause:

I found nothing relevant from the descriptions on GitHub:

I guess I should post this matter there.

4

It seems that you already open issue there

good idea !

It is good practice to reference when cross posting.

5

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.