Changeset: deb091a77eb8 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/deb091a77eb8 Added Files: sql/test/Tests/multi_column_anti.test Modified Files: sql/test/Tests/All Branch: default Log Message:
Added some tests for multi-column anti join with NULLs. Only runs with --pipeline, since it's otherwise known that MonetDB produces wrong results. (however, this test triggers an assertion with --pipeline) diffs (truncated from 3241 to 300 lines): diff --git a/sql/test/Tests/All b/sql/test/Tests/All --- a/sql/test/Tests/All +++ b/sql/test/Tests/All @@ -153,3 +153,5 @@ blob_query inet4 inet6 + +PIPELINE?multi_column_anti diff --git a/sql/test/Tests/multi_column_anti.test b/sql/test/Tests/multi_column_anti.test new file mode 100644 --- /dev/null +++ b/sql/test/Tests/multi_column_anti.test @@ -0,0 +1,3227 @@ +statement ok +start transaction; + +statement ok +create table l (id int, a int, b int, c int); + +statement ok +insert into l values + (0, null, 2, 7), + (1, null, 3, 6), + (2, 2, null, 7), + (3, 2, null, 6), + (4, 3, 3, null), + (5, 3, 4, null); + +statement ok +create table r (id int, x int, y int, z int); + +statement ok +insert into r values + (0, 1, 1, 5), + (1, null, 1, 5), + (2, 2, 2, 7), + (3, 2, 3, 8), + (4, 3, 3, 4), + (5, null, 3, 6); + +query IIII nosort +select * from l where (a,b,c) not in (select x, y, z from r); +---- +5 +3 +4 +NULL + +statement ok +rollback; + +-- empty res +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, 1000, 10000); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, 1000, 10000); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, 1000, 10000); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, NULL, 10000); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, 1000, NULL); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, NULL, 1000, 10000); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, NULL, 10000); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, 1000, NULL); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, NULL, 10000); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, 1000, NULL); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, NULL, NULL); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, NULL, NULL, 10000); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, NULL, NULL); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, NULL, NULL); +---- + + +query I nosort +select id from (select 1, 10, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, NULL, NULL, NULL); +---- + + + +-- empty res +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, 1000, 10000); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, 1000, 10000); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, 1000, 10000); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, NULL, 10000); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, 1000, NULL); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, NULL, 1000, 10000); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, NULL, 10000); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, 1000, NULL); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, NULL, 10000); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, 1000, NULL); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, NULL, NULL); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, NULL, NULL, 10000); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, NULL, NULL); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, NULL, NULL); +---- + + +query I nosort +select id from (select 1, NULL, 100, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, NULL, NULL, NULL); +---- + + +-- empty res +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, 1000, 10000); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, 1000, 10000); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, 1000, 10000); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, NULL, 10000); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, 1000, NULL); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, NULL, 1000, 10000); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, NULL, 10000); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, 1000, NULL); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, NULL, 10000); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, 1000, NULL); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, NULL, NULL); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, NULL, NULL, 10000); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, NULL, NULL); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, NULL, NULL); +---- + + +query I nosort +select id from (select 1, 10, NULL, 1000, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, NULL, NULL, NULL); +---- + + + +-- empty res +query I nosort +select id from (select 1, 10, 100, NULL, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, 1000, 10000); +---- + + +query I nosort +select id from (select 1, 10, 100, NULL, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select NULL, 100, 1000, 10000); +---- + + +query I nosort +select id from (select 1, 10, 100, NULL, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, NULL, 1000, 10000); +---- + + +query I nosort +select id from (select 1, 10, 100, NULL, 10000) t(id, a,b,c,d) where (a,b,c,d) not in (select 10, 100, NULL, 10000); _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
