Revisit SetOperationType

[ajcvickers]

As discussed in API review - "reduce to three values + distinct, or @smitpatel to suggest something else which only he understands."

[smitpatel]

@roji - Can you post summary in terms of what are supported SQL but unique to set operations (like when is push down required, when it can work without etc.)

[roji]

I'll try to do a quick rundown for the design meeting.

Set operation types

The "standard" supported set operations seem to be UNION, UNION ALL, INTERSECT and EXCEPT (although MySQL only supports UNION and UNION ALL). In addition to these, PostgreSQL also supports INTERSECT ALL and EXCEPT ALL (#16273 tracks any core support for that).

Our current design is to have a SelectExpression represent set operations, by having a SetOperationType enum on it. The value None represents a regular SelectExpression whereas the other values represent the set operations. Currently this enum only represents the standard four operations.

@divega's nice idea was to instead repurpose SelectExpression's IsDistinct to express the distinct dimension of the set operation. This would reduce the enum to have only 3 values (plus none), assuming we want to keep the current enum-based design. I'm not sure any further extensibility is required beyond these three set operations (times two for distinctness) - I've never heard of a fourth type of set operation.

Clauses inside set operands

Both MySQL and PostgreSQL support OrderBy/Take/Skip inside set operands. Consider the following:

(SELECT 1 ORDER BY 1 LIMIT 1)
UNION
(SELECT 2 ORDER BY 1 LIMIT 1)
ORDER BY 1;

This is what leads me to consider set operations as a full expression over two other expressions, as opposed to a clause on a select expression.

Clauses on set operation

PostgreSQL, MySQL and Sqlite support Order, Take and Skip on the set operation itself:

SELECT 1 UNION SELECT 2 ORDER BY 1 LIMIT 1 OFFSET 1;

No other clauses are supported AFAIK, so anything like a predicate causes pushdown. We don't currently render the above, but push down instead (see #16244). SQL Server is a bit peculiar: Take without Offset is expressed with TOP(), but that's not supported on set operations. So that situation needs to be pushed down (on SqlServer only, see #16238).

Precedence

PostgreSQL, Sqlite and SqlServer all assign higher precedence to INTERSECT than to UNION (MySQL only supports UNION/UNION ALL), so:

SELECT 1 UNION SELECT 2 INTERSECT SELECT 3;

is evaluated as

SELECT 1 UNION (SELECT 2 INTERSECT SELECT 3);

and yields 1 (rather than empty resultset). This makes set operations similar to binary expressions, and why for now each set operation has exactly two children, and not more. The current SQL generation logic is to always produce parentheses around operands which are themselves set operations, and whose set operation is different from the parent's.

Hopefully this summarizes the essential information..

[roji]

@smitpatel, if you're busy and the main point is to get rid of the SetOperationType enum, I could implement the following simple design:

• Replace SelectExpression.SetOperationType with a SetOperation property that references a new SetOperationExpression.
• SetOperationExpression would be abstract, have left and right for the operands, and three expression subclasses for Union, Intersect and Except. Distinctness could still be taken from SelectExpression as @divega suggested before.

If this design is sufficient for you, I can poach this and implement.