Support for union() --> UNION DISTINCT

[MichaelChirico]

BigQuery doesn't support unqualified UNION clauses:

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#union

Everything must be UNION ALL or UNION DISTINCT (the latter being what's usually intended by UNION without ALL, but clearer to the reader).

Currently, it's not easy to make this tweak in a flavor:

dbplyr/R/db-sql.R

Lines 671 to 684 in 5726930

	sql_query_union.DBIConnection <- function(con, x, unions, ..., lvl = 0) {
	methods <- ifelse(unions$all, "UNION ALL", "UNION")
	methods <- indent_lvl(style_kw(methods), lvl)
	tables <- unlist(unions$table)
	union_clauses <- vctrs::vec_interleave(as.character(methods), tables)
	out <- paste0(
	x,
	"\n\n",
	paste0(union_clauses, collapse = "\n\n")
	)
	sql(out)
	}

I could write my own sql_query_union() method, but that would require copy-pasting most of the logic in the default DBIConnection method.

Is the right approach to expose another method, akin to sql_join_suffix(), that returns the valid UNION operators?

sql_union_operators.DBIConnection <- function(con) c("UNION", "UNION ALL")

sql_union_operators.BigQueryConnection <- function(con) c("UNION DISTINCT", "UNION ALL")

[MichaelChirico]

The same applies to setdiff() and intersect(), which both require a DISTINCT qualifier:

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#intersect
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#except

[MichaelChirico]

Perhaps (forgive the suggested breaking change 😦) we are better off with qualifier=NULL in the signature here, with qualifier='ALL' corresponding to the current all=TRUE.

Then we could replace in our method with qualifier = 'DISTINCT' and even DISTINCT BY NAME DISTINCT STRICT CORRESPONDING, if we were to decide an R equivalent to those that could be translated.

dbplyr/R/db-sql.R

Line 631 in 5726930

all = FALSE,