.keep_all=TRUE breaks distinct for MS SQL Server

[carlganz]

It seems to work with SQLite but the use of row_number in distinct(.keep_all=TRUE) isn't quite right for MS SQL Server

library(dplyr)
library(dbplyr)

### need to setup your own SQL Server connection
con <- DBI::dbConnect(odbc::odbc)
copy_to(con, mtcars)
mtcars2 <- tbl(con, "mtcars")

### distinct works as expected
mtcars2 %>% distinct()

### gives error "The function 'ROW_NUMBER' must have an OVER clause with ORDER BY"
mtcars2 %>% distinct(carb, .keep_all = TRUE)

[hadley]

Minimal reprex:

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

lf <- lazy_frame(x = 1, y = 2, con = simulate_mysql())

lf |> distinct(x)
#> <SQL>
#> SELECT DISTINCT `x`
#> FROM `df`
lf |> distinct(x, .keep_all = TRUE)
#> <SQL>
#> SELECT `x`, `y`
#> FROM (
#>   SELECT *, ROW_NUMBER() OVER (PARTITION BY `x`) AS `q01`
#>   FROM `df`
#> ) `q01`
#> WHERE (`q01` = 1)

^{Created on 2022-12-04 with reprex v2.0.2}

[hadley]

I suspect this will work in very few places since ROW_NUMBER() will always need an ORDER BY. Maybe we should just make this an error?

[mgirlich]

Apparently, it works in most databases according to modern SQL - of course it doesn't work in MS SQL and Oracle DB... If we want to support this, we could add a dummy window order. @hadley what do you think?

[hadley]

Yeah, it would be nice to add a nice dummy window order, I think.

[mgirlich]

@carlganz As I don't have an SQL Server database I can't test this. Can you install the PR version via remotes::install_github("tidyverse/dbplyr#1267") and test whether this now works?