Changeset: 26807083568b for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=26807083568b Added Files: sql/test/subquery/Tests/exists.sql sql/test/subquery/Tests/exists.stable.err sql/test/subquery/Tests/exists.stable.out Modified Files: sql/test/subquery/Tests/All sql/test/subquery/Tests/any_all.sql sql/test/subquery/Tests/any_all.stable.out sql/test/subquery/Tests/correlated.sql Branch: Nov2019 Log Message:
Added any,all,exists operators tests diffs (truncated from 719 to 300 lines): diff --git a/sql/test/subquery/Tests/All b/sql/test/subquery/Tests/All --- a/sql/test/subquery/Tests/All +++ b/sql/test/subquery/Tests/All @@ -5,4 +5,5 @@ subquery2 subquery3 scalar any_all +exists correlated diff --git a/sql/test/subquery/Tests/any_all.sql b/sql/test/subquery/Tests/any_all.sql --- a/sql/test/subquery/Tests/any_all.sql +++ b/sql/test/subquery/Tests/any_all.sql @@ -38,4 +38,122 @@ SELECT 1 > ALL(SELECT * FROM integers); SELECT NULL > ANY(SELECT * FROM integers); -- NULL SELECT NULL > ALL(SELECT * FROM integers); -- NULL +SELECT i=ANY(SELECT i FROM integers WHERE i=i1.i) FROM integers i1 ORDER BY i; +--True +--True +--True +--False + +/*Wrong results +SELECT i>ALL(SELECT (i+i1.i-1)/2 FROM integers WHERE i IS NOT NULL) FROM integers i1 ORDER BY i;*/ +--False +--False +--True +--NULL + +/*Wrong results +SELECT i=ALL(SELECT i FROM integers WHERE i<>i1.i) FROM integers i1 ORDER BY i;*/ +--False +--False +--False +--True + +SELECT i FROM integers i1 WHERE i=ANY(SELECT i FROM integers WHERE i=i1.i) ORDER BY i; +--1 +--2 +--3 + +/*BROKEN +SELECT i FROM integers i1 WHERE i<>ANY(SELECT i FROM integers WHERE i=i1.i) ORDER BY i;*/ +-- (Empty result set) + +SELECT i FROM integers i1 WHERE i=ANY(SELECT i FROM integers WHERE i<>i1.i) ORDER BY i; +-- (Empty result set) + +SELECT i FROM integers i1 WHERE i>ANY(SELECT i FROM integers WHERE i<>i1.i) ORDER BY i; +--2 +--3 + +SELECT i FROM integers i1 WHERE i>ALL(SELECT (i+i1.i-1)/2 FROM integers WHERE i IS NOT NULL) ORDER BY i; +--3 + +SELECT i=ALL(SELECT i FROM integers WHERE i=i1.i) FROM integers i1 ORDER BY i; +--True +--True +--True +--True + +SELECT i=ANY(SELECT i FROM integers WHERE i=i1.i) FROM integers i1 ORDER BY i; +--True +--True +--True +--False + +SELECT i<>ALL(SELECT i FROM integers WHERE i=i1.i) FROM integers i1 ORDER BY i; +--True +--False +--False +--False + +SELECT i<>ANY(SELECT i FROM integers WHERE i=i1.i) FROM integers i1 ORDER BY i; +--False +--False +--False +--False + +SELECT i=ANY(SELECT i FROM integers WHERE i<>i1.i) FROM integers i1 ORDER BY i; +--False +--False +--False +--False + +SELECT i>ANY(SELECT i FROM integers WHERE i<>i1.i) FROM integers i1 ORDER BY i; +--False +--True +--True +--False + +/*Wrong results +SELECT i>ALL(SELECT (i+i1.i-1)/2 FROM integers) FROM integers i1 ORDER BY i;*/ +--False +--False +--NULL +--NULL + +/*Wrong results +SELECT i>ALL(SELECT (i+i1.i-1)/2 FROM integers WHERE i IS NOT NULL) FROM integers i1 ORDER BY i;*/ +--False +--False +--True +--NULL + +SELECT i=ANY(SELECT i FROM integers WHERE i=i1.i OR i IS NULL) FROM integers i1 ORDER BY i; +--True +--True +--True +--NULL + +/*Wrong results +SELECT i=ALL(SELECT i FROM integers WHERE i=i1.i OR i IS NULL) FROM integers i1 ORDER BY i;*/ +--NULL +--NULL +--NULL +--NULL + +SELECT MIN(i)>ANY(SELECT i FROM integers WHERE i>MIN(i1.i)) FROM integers i1; +--False + +SELECT SUM(i)>ANY(SELECT i FROM integers WHERE i>MIN(i1.i)) FROM integers i1; +--True + +/*BROKEN +SELECT (SELECT SUM(i)+SUM(i1.i) FROM integers)>ANY(SELECT i FROM integers WHERE i>MIN(i1.i)) FROM integers i1;*/ +--True + +SELECT i=ANY(SELECT i FROM integers WHERE i=i1.i AND i>10) FROM integers i1 ORDER BY i; +--False +--False +--False +--False + DROP TABLE integers; diff --git a/sql/test/subquery/Tests/any_all.stable.out b/sql/test/subquery/Tests/any_all.stable.out --- a/sql/test/subquery/Tests/any_all.stable.out +++ b/sql/test/subquery/Tests/any_all.stable.out @@ -23,26 +23,26 @@ stdout of test 'any_all` in directory 's # 19:15:04 > #SELECT 1 = ANY(SELECT 1); -- true -% .L5 # table_name -% L5 # name +% .L11 # table_name +% L11 # name % boolean # type % 5 # length [ true ] #SELECT 1 = ANY(SELECT NULL); -- NULL -% .L5 # table_name -% L5 # name +% .L11 # table_name +% L11 # name % boolean # type % 5 # length [ NULL ] #SELECT 1 = ANY(SELECT 2); -- false -% .L5 # table_name -% L5 # name +% .L11 # table_name +% L11 # name % boolean # type % 5 # length [ false ] #SELECT NULL = ANY(SELECT 2); -- NULL -% .L5 # table_name -% L5 # name +% .L11 # table_name +% L11 # name % boolean # type % 5 # length [ NULL ] @@ -74,79 +74,198 @@ stdout of test 'any_all` in directory 's #INSERT INTO integers VALUES (1), (2), (3); [ 3 ] #SELECT 2 > ANY(SELECT * FROM integers); -- true -% .L10 # table_name -% L10 # name +% .L7 # table_name +% L7 # name % boolean # type % 5 # length [ true ] #SELECT 1 > ANY(SELECT * FROM integers); -- false -% .L10 # table_name -% L10 # name +% .L7 # table_name +% L7 # name % boolean # type % 5 # length [ false ] #SELECT 4 > ALL(SELECT * FROM integers); -- true -% .L10 # table_name -% L10 # name +% .L7 # table_name +% L7 # name % boolean # type % 5 # length [ true ] #SELECT 1 > ALL(SELECT * FROM integers); -- false -% .L10 # table_name -% L10 # name +% .L7 # table_name +% L7 # name % boolean # type % 5 # length [ false ] #SELECT NULL > ANY(SELECT * FROM integers); -- NULL -% .L10 # table_name -% L10 # name +% .L7 # table_name +% L7 # name % boolean # type % 5 # length [ NULL ] #SELECT NULL > ALL(SELECT * FROM integers); -- NULL -% .L10 # table_name -% L10 # name +% .L7 # table_name +% L7 # name % boolean # type % 5 # length [ NULL ] #INSERT INTO integers VALUES (NULL); [ 1 ] #SELECT 2 > ANY(SELECT * FROM integers); -- true -% .L10 # table_name -% L10 # name +% .L7 # table_name +% L7 # name % boolean # type % 5 # length [ true ] #SELECT 1 > ANY(SELECT * FROM integers); -- NULL -% .L10 # table_name -% L10 # name +% .L7 # table_name +% L7 # name % boolean # type % 5 # length [ NULL ] #SELECT 4 > ALL(SELECT * FROM integers); -- NULL -% .L10 # table_name -% L10 # name +% .L7 # table_name +% L7 # name % boolean # type % 5 # length [ NULL ] #SELECT 1 > ALL(SELECT * FROM integers); -- false -% .L10 # table_name -% L10 # name +% .L7 # table_name +% L7 # name % boolean # type % 5 # length [ false ] #SELECT NULL > ANY(SELECT * FROM integers); -- NULL -% .L10 # table_name -% L10 # name +% .L7 # table_name +% L7 # name % boolean # type % 5 # length [ NULL ] #SELECT NULL > ALL(SELECT * FROM integers); -- NULL -% .L10 # table_name -% L10 # name +% .L7 # table_name +% L7 # name % boolean # type % 5 # length [ NULL ] +#SELECT i=ANY(SELECT i FROM integers WHERE i=i1.i) FROM integers i1 ORDER BY i; +% .L6 # table_name +% L6 # name +% boolean # type +% 5 # length +[ false ] +[ true ] +[ true ] +[ true ] +#SELECT i FROM integers i1 WHERE i=ANY(SELECT i FROM integers WHERE i=i1.i) ORDER BY i; +% sys.i1 # table_name +% i # name +% int # type +% 1 # length +[ 1 ] +[ 2 ] +[ 3 ] +#SELECT i FROM integers i1 WHERE i=ANY(SELECT i FROM integers WHERE i<>i1.i) ORDER BY i; +% sys.i1 # table_name +% i # name +% int # type +% 1 # length +#SELECT i FROM integers i1 WHERE i>ANY(SELECT i FROM integers WHERE i<>i1.i) ORDER BY i; +% sys.i1 # table_name +% i # name +% int # type _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
