SQL: INTERSECT and EXCEPT #6033
Description
Per Postgres docs, SQL offers UNION, INTERSECT, and EXCEPT that perform set operations. SuperSQL already has partial support for UNION, so support for the others should be added as well.
Details
Repro is with super commit 800e81e. This was found via a query from a sqllogictest.
The test case from that sqllogictest is pretty lengthy, so here's a simplified example.
With test data:
$ psql postgres
psql (17.5 (Homebrew))
Type "help" for help.
postgres=# CREATE TABLE students (
student_id INT,
name TEXT
);
CREATE TABLE
postgres=# CREATE TABLE honor_roll (
student_id INT
);
CREATE TABLE
postgres=# INSERT INTO students VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana');
INSERT 0 4
postgres=# INSERT INTO honor_roll VALUES
(2),
(3),
(5);
INSERT 0 3
To get the students not on the honor roll:
postgres=# SELECT student_id FROM students
EXCEPT
SELECT student_id FROM honor_roll;
student_id
------------
4
1
(2 rows)
To get the students on the honor roll that are not in the students table:
postgres=# SELECT student_id FROM honor_roll
INTERSECT
SELECT student_id FROM students;
student_id
------------
3
2
(2 rows)
Meanwhile in SuperDB at the moment these are both parse errors.
$ super -version
Version: 800e81eaf
$ super -s students
{student_id:1::int32,name:"Alice"}
{student_id:2::int32,name:"Bob"}
{student_id:3::int32,name:"Charlie"}
{student_id:4::int32,name:"Diana"}
$ super -s honor_roll
{student_id:2::int32}
{student_id:3::int32}
{student_id:5::int32}
$ super -c "
SELECT student_id FROM students
EXCEPT
SELECT student_id FROM honor_roll;"
parse error at line 4, column 1:
SELECT student_id FROM honor_roll;
^ ===
$ super -c "
SELECT student_id FROM honor_roll
INTERSECT
SELECT student_id FROM students;"
parse error at line 4, column 1:
SELECT student_id FROM students;
^ ===
Also note that like UNION, both INTERSECT and EXCEPT offer the DISTINCT and ALL variations.