Add union all

[tomasgreif]

union all keeps rows even they are equal. Adding it would be pretty easy:

sets.r

#' @export
union <- function(x, y, ...) UseMethod("union_all")

tbl-sql.r

#' @export
union_all.tbl_sql <- function(x, y, copy = FALSE, ...) {
  y <- auto_copy(x, y, copy)
  sql <- sql_set_op(x$src$con, x, y, "UNION ALL")
  update(tbl(x$src, sql), group_by = groups(x))
}

[hadley]

Could fall back to bind_rows() for local data.

[iangow]

Note difference between UNION ALL and bind_rows evident below. As discussed at #1890, UNION CORRESPONDING is the equivalent to bind_rows (but not commonly implemented). May not be a good idea to have same verb with different behaviour:

library(dplyr)
pg <- src_postgres()

sql <- "
    WITH data AS (SELECT 1 AS a, 2 AS b)

    SELECT a, b
    FROM data 
    UNION ALL
    SELECT b, a
    FROM data"

tbl(pg, sql(sql))

df <-
    tbl(pg, sql("SELECT 1 AS a, 2 AS b")) %>%
    collect()

df %>% 
    select(b, a) %>% 
    bind_rows(df %>% select(a, b))

SQL output:

> tbl(pg, sql(sql))
Source: postgres 9.6.0 [igow@localhost:5432/crsp]
From: <derived table> [?? x 2]

       a     b
   (int) (int)
1      1     2
2      2     1

R output:

Source: local data frame [2 x 2]

      b     a
  (int) (int)
1     2     1
2     2     1

[iangow]

I guess one could "fix" UNION ALL, but it's well established.