This is an automated email from the ASF dual-hosted git repository.
ppa pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new aa22af743d IGNITE-21940 Sql. Cover SQL F304 (EXCEPT ALL table
operator) feature by tests (#3635)
aa22af743d is described below
commit aa22af743d7fefc6ddfaa66ba9f3b057a49ed36d
Author: Pavel Pereslegin <[email protected]>
AuthorDate: Mon Apr 22 16:07:05 2024 +0300
IGNITE-21940 Sql. Cover SQL F304 (EXCEPT ALL table operator) feature by
tests (#3635)
---
.../integrationTest/sql/set/test_except_all.test | 59 ++++++++++++++++++++++
1 file changed, 59 insertions(+)
diff --git
a/modules/sql-engine/src/integrationTest/sql/set/test_except_all.test
b/modules/sql-engine/src/integrationTest/sql/set/test_except_all.test
new file mode 100644
index 0000000000..82a4d49467
--- /dev/null
+++ b/modules/sql-engine/src/integrationTest/sql/set/test_except_all.test
@@ -0,0 +1,59 @@
+# 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
