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

Cannot generate SQL for is.na() with dbplyr using odbc #3007

Closed
juliasilge opened this Issue Aug 1, 2017 · 2 comments

Comments

Projects
None yet
2 participants
@juliasilge

juliasilge commented Aug 1, 2017

With older versions of dbplyr, I can generate functioning SQL that includes is.na() and is.null() to use within filter() for a remote table. (I'm using the odbc package.) For more recent commits, since about mid-June, this is not working.

If I install dbplyr from commit 6d4e61a52e57177afa9a2881b7a2b617d70f5eef, then I am able to use is.na() (and also is.null()) to generate SQL without any problems.

library(DBI)
library(dbplyr)
library(dplyr)

con <- dbConnect(odbc::odbc(),
                 driver = "libtdsodbc.so",
                 database = "AdventureWorks2012",
                 uid = "sqlfamily",
                 pwd = "sqlf@m1ly",
                 Server = "mhknbn2kdz.database.windows.net",
                 port = 1433)

src <- src_dbi(con)


src %>% 
  tbl("bigProduct") %>%
  filter(!is.na(ProductNumber))
#> # Source:   lazy query [?? x 23]
#> # Database: Microsoft SQL Server
#> #   12.00.0700[@mhknbn2kdz/AdventureWorks2012]
#>    ProductID                      Name ProductNumber MakeFlag
#>        <int>                     <chr>         <chr>    <lgl>
#>  1      1001       Adjustable Race1000  AR-5381-1000    FALSE
#>  2      1002          Bearing Ball1000  BA-8327-1000    FALSE
#>  3      1003       BB Ball Bearing1000  BE-2349-1000     TRUE
#>  4      1004 Headset Ball Bearings1000  BE-2908-1000    FALSE
#>  5      1005                 Blade1000  BL-2036-1000     TRUE
#>  6      1006           LL Crankarm1000  CA-5965-1000    FALSE
#>  7      1007           ML Crankarm1000  CA-6738-1000    FALSE
#>  8      1008           HL Crankarm1000  CA-7457-1000    FALSE
#>  9      1009       Chainring Bolts1000  CB-2903-1000    FALSE
#> 10      1010         Chainring Nut1000  CN-6137-1000    FALSE
#> # ... with more rows, and 19 more variables: FinishedGoodsFlag <lgl>,
#> #   Color <chr>, SafetyStockLevel <int>, ReorderPoint <int>,
#> #   StandardCost <dbl>, ListPrice <dbl>, Size <chr>,
#> #   SizeUnitMeasureCode <chr>, WeightUnitMeasureCode <chr>, Weight <dbl>,
#> #   DaysToManufacture <int>, ProductLine <chr>, Class <chr>, Style <chr>,
#> #   ProductSubcategoryID <int>, ProductModelID <int>,
#> #   SellStartDate <dttm>, SellEndDate <dttm>, DiscontinuedDate <dttm>

If I install dbplyr from any commits later, such as commit 2a4c041821282c3ecbbfa7f72fa67446911c69ef, then I cannot use is.na() (or is.null()) to generate SQL.

library(DBI)
library(dbplyr)
library(dplyr)

con <- dbConnect(odbc::odbc(),
                 driver = "libtdsodbc.so",
                 database = "AdventureWorks2012",
                 uid = "sqlfamily",
                 pwd = "sqlf@m1ly",
                 Server = "mhknbn2kdz.database.windows.net",
                 port = 1433)

src <- src_dbi(con)


src %>% 
  tbl("bigProduct") %>%
  filter(!is.na(ProductNumber))
#> Error: <SQL> 'SELECT  TOP 10 *
#> FROM "bigProduct"
#> WHERE (NOT(CASE WHEN "ProductNumber" IS NULL THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END))'
#>   nanodbc/nanodbc.cpp:1587: 42000: [FreeTDS][SQL Server]Statement(s) could not be prepared.
@juliasilge

This comment has been minimized.

juliasilge commented Aug 1, 2017

Maybe this is the same as #2940.

@edgararuiz edgararuiz referenced this issue Aug 18, 2017

Merged

Fix na mssql #34

@hadley

This comment has been minimized.

Member

hadley commented Aug 23, 2017

Duplicate of #2940 and PR in flight

@hadley hadley closed this Aug 23, 2017

@lock lock bot locked as resolved and limited conversation to collaborators Jun 7, 2018

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.