Sort order of NAs

[krlmlr]

NAs sort first in (some) SQL databases (at least MySQL, SQLite and SQL Server), but last in tibbles and PostgreSQL. I wonder what's the best way to document this, we don't seem to have ?dbplyr::arrange yet.

library(tidyverse)

dbplyr::memdb_frame(a = c(1, NA)) %>%
  arrange(a)
#> # Source:     table<szlvekywfo> [?? x 1]
#> # Database:   sqlite 3.22.0 [:memory:]
#> # Ordered by: a
#>       a
#>   <dbl>
#> 1    NA
#> 2     1

tibble(a = c(1, NA)) %>%
  arrange(a)
#> # A tibble: 2 x 1
#>       a
#>   <dbl>
#> 1     1
#> 2    NA

dbplyr::memdb_frame(a = c(1, NA)) %>%
  arrange(if_else(is.na(a), 1, 0), a)
#> # Source:     table<liromxakig> [?? x 1]
#> # Database:   sqlite 3.22.0 [:memory:]
#> # Ordered by: if_else(is.na(a), 1, 0), a
#>       a
#>   <dbl>
#> 1     1
#> 2    NA

^{Created on 2019-01-03 by the reprex package (v0.2.1)}

[mkearney]

DIBS

[hadley]

@mkearney just in case it's not obvious, you'll need to document arrange.tbl_lazy since currently it's not documented. The closest existing template would be copy_to.src_sql.

[yutannihilation]

FYI, dplyr::arrange() now documents about NA, which is yet different from databases tidyverse/dplyr#4083.