modules/sql-engine/src/integrationTest/sql/set/test_except_all.test

# name: test/sql/set/test_except_all.test
# description: EXCEPT ALL table operator
# feature: F304
# [group] Set operations

query II
select x, count(*) from
  (select * from (values(1),(2),(2),(2),(3),(3),(3),(3),(3),(3),(4),(4),(4),(4)) s(x)
    except all
    select * from (values(1),(2),(3),(3),(3)) t(x))
group by x order by x
----
2	2
3	3
4	4

# except all + intersect all
query II
select x, count(*) from
((select * from (values(1),(2),(2),(3),(3),(3),(4),(4),(4),(4)) s(x)
  except all
  select * from (values(1),(3),(3)) t(x)) intersect all select * from (values(2),(2),(2),(4),(3),(3)) u(x)) s
group by x order by x
----
2	2
3	1
4	1

statement ok
CREATE TABLE T(q1 bigint, q2 bigint);

statement ok
INSERT INTO T VALUES(123,456),(123,4567890123456789),(4567890123456789,123),(4567890123456789,4567890123456789),(4567890123456789,-4567890123456789);

query I
SELECT q2 FROM T EXCEPT ALL SELECT q1 FROM T ORDER BY 1;
----
-4567890123456789
456

query I
SELECT q2 FROM T EXCEPT ALL SELECT DISTINCT q1 FROM T ORDER BY 1;
----
-4567890123456789
456
4567890123456789

query I
SELECT q1 FROM T EXCEPT ALL SELECT q2 FROM T ORDER BY 1;
----
123
4567890123456789

query I
SELECT q1 FROM T EXCEPT ALL SELECT DISTINCT q2 FROM T ORDER BY 1;
----
123
4567890123456789
4567890123456789