RFC-0007 Bag Operators DISTINCT Inconsistencies

[johnedquinn]

I believe there are some inconsistencies with the wording of RFC-0007 with regards to distinctness.

From the RFC:

T1 UNION DISTINCT T2     = DISTINCT(SQL_UNION(T1, T2))
T1 INTERSECT DISTINCT T2 = DISTINCT(SQL_INTERSECT(T1, T2))
T1 EXCEPT DISTINCT T2    = DISTINCT(SQL_EXCEPT(T1, T2))

However, SQL doesn't actually operate this way. See this DB Fiddle Example for PostgreSQL. Please use the link and update the query to use both EXCEPT and EXCEPT ALL.

When you use EXCEPT (distinct), it will return the following table:

id
11
12

When you use EXCEPT ALL, it will return the following table:

id
10
11
12

As you can see, the use of DISTINCT does NOT perform the DISTINCT operation on the output of SQL_EXCEPT. It's a bit more complicated than that. Specifically, for EXCEPT DISTINCT, it executes DISTINCT on both inputs, then it runs EXCEPT on the new inputs. A similar thing occurs for INTERSECT.