Add support for UNION

[StevenHibble]

UNION [ALL] is probably the most popular set operation in SQL. It may be the easiest to implement as well.

This might affect the structure of parse_query's output. That will probably have effects on tidyquery.

Initial thoughts on options:

1. Keep everything flat
2. Add another level to the structure

parse_query("select x from df1 union select x from df2")

Option 1:

$`select`
$`select`[[1]]
x

$from
$from[[1]]
df1

$set_op
[1] union

$`select`
$`select`[[1]]
x

$from
$from[[1]]
df2

Option 2:

[[1]]
[[1]]$`select`
[[1]]$`select`[[1]]
x

[[1]]$from
[[1]]$from[[1]]
df1

$set_op
[1] union

[[3]]
[[3]]$`select`
[[3]]$`select`[[1]]
x

[[3]]$from
[[3]]$from[[1]]
df2

[ianmcook]

I think the best approach is to create a new sublist named secondary_queries which is a list containing one or more queries. This sublist should have an attribute named relationship which is a vector of character strings such as "union all" or "union distinct":

Option 3:

$select
$select[[1]]
x

$from
$from[[1]]
df1

$secondary_queries
$secondary_queries[[1]]
$secondary_queries[[1]]$select
$secondary_queries[[1]]$select[[1]]
x

$secondary_queries[[1]]$from
$secondary_queries[[1]]$from[[1]]
df2

attr(,"relationship")
[1] "union distinct"

This same structure could later support CTEs and subqueries (by naming the items in secondary_queries and then referring to those table names elsewhere in the query). I think it's best to use one unified structure like this to support set operations, CTEs, and subqueries.

[StevenHibble]

That may be tough to parse with complicated nesting structures. CTEs are particularly difficult because they may be seconday_queries to multiple levels. For example, in this query, some_cte is a subquery of both df1 (the main/leader set) and df2 (the secondary set):

WITH some_cte
     AS (SELECT ...)

  SELECT *
  FROM df1
  WHERE x IN (SELECT y from some_cte)
  UNION ALL
  SELECT *
  FROM df2
  WHERE x IN (SELECT y from some_cte)

If we keep the overall structure similar to the original query, it may be easier to parse. I'm not familiar with the changes that would need to be made in tidyquery, though.

[ianmcook]

Yep, this will require some deeper thinking about the design before we make any decisions. I have some other urgent work to do in the next few weeks, so I can't commit much time to it now, but I'm happy to keep discussing ideas here.

I think the secondary_queries approach could handle cases like in your example above; when I get a chance, I'll describe how.