filter() on boolean/legal variables only works with 1 and 0

[DaZaM82]

I have a table with a boolean/legal column. I used filter(my_lgl_column == TRUE) and this throws an error as follows:
Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'TRUE'. [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.

I found that changing the filter to 1's and 0's corrects this issue: filter(my_lgl_column == 1)

Although both would work with a local dataframe as well, would be good if dbplyr could convert TRUE/FALSE to 1/0 in the query script.

I'm working with SQL Server and odbc connection.

conn <- dbConnect(
  odbc::odbc(),
  driver = "ODBC Driver 17 for SQL Server",
  database = "NAME", # dummy name
  server = "servernane.net", # dummy server
  Trusted_Connection = "yes"
)
df <- data.frame(col = c(TRUE, FALSE, TRUE, FALSE))
df_sql <- dbWriteTable(conn, "test", df, overwrite = TRUE)

df_sql %>% filter(col == TRUE) #fails
df_sql %>% filter(col == 1) #success

[mgirlich]

What's the class of your connection?

[DaZaM82]

What's the class of your connection?

I believe it is S4 object of class Microsoft SQL Server
I'm not an expert on this so if that's not the answer you are looking for then please let me know how to determine it in RStudio desktop.

[hadley]

Have you tried df_sql %>% filter(col)?

[hadley]

Fixed by #1288