Changeset: cb304b53f140 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=cb304b53f140 Added Files: sql/test/mergetables/Tests/mergequery.test Modified Files: sql/test/merge-partitions/Tests/mergepart31.test sql/test/subquery/Tests/subquery6.test Branch: mtest Log Message:
Updated tests with recent fixes diffs (truncated from 316 to 300 lines): diff --git a/sql/test/merge-partitions/Tests/mergepart31.test b/sql/test/merge-partitions/Tests/mergepart31.test --- a/sql/test/merge-partitions/Tests/mergepart31.test +++ b/sql/test/merge-partitions/Tests/mergepart31.test @@ -157,12 +157,112 @@ project ( | select ( | | [ boolean "true" ] +query T rowsort +plan select 1 from splitted where stamp > TIMESTAMP '2020-01-01 00:00:00' and stamp <= TIMESTAMP '2020-01-01 00:00:00' +---- +) [ tinyint "1" ] +project ( +| ) [ timestamp(7) "2020-01-01 00:00:00.000000" < "splitted"."stamp" <= timestamp(7) "2020-01-01 00:00:00.000000" BETWEEN ] +| select ( +| | table(sys.third_decade) [ "splitted"."stamp" ] COUNT + +query T rowsort +plan select 1 from splitted where stamp >= TIMESTAMP '2000-01-01 00:00:00' and stamp < TIMESTAMP '2020-01-01 00:00:00' +---- +12 values hashing to 72d7507e7d12f402b333d6dc1e51e58c + +query T rowsort +plan select 1 from splitted where stamp > TIMESTAMP '2010-01-01 00:00:00' and stamp < TIMESTAMP '2020-01-01 00:00:00' +---- +) [ tinyint "1" ] +project ( +| ) [ timestamp(7) "2010-01-01 00:00:00.000000" < "splitted"."stamp" < timestamp(7) "2020-01-01 00:00:00.000000" BETWEEN ] +| select ( +| | table(sys.second_decade) [ "splitted"."stamp" ] COUNT + +query T rowsort +plan select 1 from splitted where stamp >= TIMESTAMP '2010-01-01 00:00:00' and stamp < TIMESTAMP '2020-01-01 00:00:00' +---- +) [ tinyint "1" ] +project ( +| ) [ timestamp(7) "2010-01-01 00:00:00.000000" <= "splitted"."stamp" < timestamp(7) "2020-01-01 00:00:00.000000" BETWEEN ] +| select ( +| | table(sys.second_decade) [ "splitted"."stamp" ] COUNT + +query T rowsort +plan select 1 from splitted where stamp > TIMESTAMP '2001-01-02 00:00:00' and stamp < TIMESTAMP '2015-01-01 00:00:00' +---- +12 values hashing to c29b7331b26c384cd72d3e4b966ea3c2 + +query T rowsort +plan select 1 from splitted where stamp > TIMESTAMP '2010-01-01 00:00:00' and stamp < TIMESTAMP '2010-01-01 00:00:00' +---- +) [ tinyint "1" ] +project ( +| ) [ boolean "false" ] +| select ( +| | [ boolean "true" ] + +query T rowsort +plan select 1 from splitted where stamp > TIMESTAMP '2009-01-01 00:00:00' and stamp <= TIMESTAMP '2010-01-01 00:00:00' +---- +12 values hashing to 551700cb417d52bcc2ea397abfdf1452 + +query T rowsort +plan select 1 from splitted where stamp > TIMESTAMP '2009-01-01 00:00:00' and stamp <= TIMESTAMP '2020-01-01 00:00:00' +---- +19 values hashing to 28e40a9f6da6ce8a27a58d449adaea87 + +statement ok +CREATE TABLE fourth_decade (stamp TIMESTAMP, val INT) + +statement ok +ALTER TABLE splitted ADD TABLE fourth_decade AS PARTITION FROM RANGE MINVALUE TO TIMESTAMP '2000-01-01 00:00:00' + +statement ok +INSERT INTO splitted VALUES (TIMESTAMP '1999-01-01 00:00:00', 7) + +query T rowsort +plan select 1 from splitted where stamp >= TIMESTAMP '2000-01-01 00:00:00' and stamp <= TIMESTAMP '2001-01-01 00:00:00' +---- +) [ tinyint "1" ] +project ( +| ) [ timestamp(7) "2000-01-01 00:00:00.000000" <= "splitted"."stamp" <= timestamp(7) "2001-01-01 00:00:00.000000" BETWEEN ] +| select ( +| | table(sys.first_decade) [ "splitted"."stamp" ] COUNT + +query T rowsort +plan select 1 from splitted where stamp > TIMESTAMP '1999-01-01 00:00:00' and stamp <= TIMESTAMP '2001-01-01 00:00:00' +---- +12 values hashing to a9b9c455c94b91e080ed32e6bc75dd4b + +query T rowsort +plan select 1 from splitted where stamp = TIMESTAMP '2010-01-01 00:00:00' +---- +) [ tinyint "1" ] +project ( +| ) [ "splitted"."stamp" = timestamp(7) "2010-01-01 00:00:00.000000" ] +| select ( +| | table(sys.second_decade) [ "splitted"."stamp" ] COUNT + +query T rowsort +plan select 1 from splitted where stamp = TIMESTAMP '2000-01-01 00:00:00' +---- +) [ tinyint "1" ] +project ( +| ) [ "splitted"."stamp" = timestamp(7) "2000-01-01 00:00:00.000000" ] +| select ( +| | table(sys.first_decade) [ "splitted"."stamp" ] COUNT + statement ok ALTER TABLE splitted DROP TABLE second_decade statement ok ALTER TABLE splitted DROP TABLE third_decade +statement ok +ALTER TABLE splitted DROP TABLE fourth_decade + query T rowsort plan select 1 from splitted where stamp = TIMESTAMP '2010-01-01 00:00:00' ---- diff --git a/sql/test/mergetables/Tests/mergequery.test b/sql/test/mergetables/Tests/mergequery.test new file mode 100644 --- /dev/null +++ b/sql/test/mergetables/Tests/mergequery.test @@ -0,0 +1,174 @@ +statement ok +START TRANSACTION + +statement ok +CREATE TABLE part1 ( x double, y double, z double) + +statement ok +COPY 4 RECORDS INTO part1 FROM stdin USING DELIMITERS ' ',E'\n' +<COPY_INTO_DATA> +0.0 0.0 0.0 +1.0 0.0 0.0 +0.0 1.0 0.0 +1.0 1.0 0.0 + +statement ok +CREATE TABLE part2 ( x double, y double, z double) + +statement ok +COPY 4 RECORDS INTO part2 FROM stdin USING DELIMITERS ' ',E'\n' +<COPY_INTO_DATA> +2.0 0.0 0.0 +3.0 0.0 0.0 +2.0 1.0 0.0 +3.0 1.0 0.0 + +statement ok +CREATE MERGE TABLE complete ( x double, y double, z double) + +statement ok +ALTER TABLE complete ADD TABLE part1 + +statement ok +ALTER TABLE complete ADD TABLE part2 + +query RRR rowsort +SELECT * FROM COMPLETE +---- +24 values hashing to 8f130e4c6c1b2a3e43a48914d6248638 + +statement ok +alter table part1 set read only + +statement ok +alter table part2 set read only + +statement ok +analyze sys.part1 (x,y,z) minmax + +statement ok +analyze sys.part2 (x,y,z) minmax + +query RRR rowsort +SELECT * FROM complete where x>=0.0 AND x <=1.0 +---- +12 values hashing to 3b6980dc48c09291f03d3afd6e9d8e61 + +query RRR rowsort +SELECT * FROM complete where x>=2.0 AND x <=3.0 +---- +12 values hashing to 10ce97483635bd79429c525f1117b002 + +query T rowsort +PLAN SELECT * FROM complete where x = 0.0 +---- +) [ "complete"."x", "complete"."y", "complete"."z" ] +project ( +| ) [ "complete"."x" = double(53,1) "0" ] +| select ( +| | table(sys.part1) [ "complete"."x", "complete"."y", "complete"."z" ] COUNT + +query RRR rowsort +SELECT * FROM complete where x = 0.0 +---- +0.000 +0.000 +0.000 +0.000 +1.000 +0.000 + +query T rowsort +PLAN SELECT * FROM complete where x = 3.0 +---- +) [ "complete"."x", "complete"."y", "complete"."z" ] +project ( +| ) [ "complete"."x" = double(53,1) "3" ] +| select ( +| | table(sys.part2) [ "complete"."x", "complete"."y", "complete"."z" ] COUNT + +query RRR rowsort +SELECT * FROM complete where x = 3.0 +---- +3.000 +0.000 +0.000 +3.000 +1.000 +0.000 + +query T rowsort +PLAN SELECT * FROM complete where x >= 1.0 AND x < 2.0 +---- +) [ "complete"."x", "complete"."y", "complete"."z" ] +project ( +| ) [ double(53,1) "1" <= "complete"."x" < double(53,1) "2" BETWEEN ] +| select ( +| | table(sys.part1) [ "complete"."x", "complete"."y", "complete"."z" ] COUNT + +query RRR rowsort +SELECT * FROM complete where x >= 1.0 AND x < 2.0 +---- +1.000 +0.000 +0.000 +1.000 +1.000 +0.000 + +query T rowsort +PLAN SELECT * FROM complete where x > 1.0 AND x <= 2.0 +---- +) [ "complete"."x", "complete"."y", "complete"."z" ] +project ( +| ) [ double(53,1) "1" < "complete"."x" <= double(53,1) "2" BETWEEN ] +| select ( +| | table(sys.part2) [ "complete"."x", "complete"."y", "complete"."z" ] COUNT + +query RRR rowsort +SELECT * FROM complete where x > 1.0 AND x <= 2.0 +---- +2.000 +0.000 +0.000 +2.000 +1.000 +0.000 + +query T rowsort +PLAN SELECT * FROM complete where x > 1.0 AND x < 2.0 +---- +) [ double "NULL" as "complete"."x", double "NULL" as "complete"."y", double "NULL" as "complete"."z" ] +project ( +| ) [ boolean "false" ] +| select ( +| | [ boolean "true" ] + +query RRR rowsort +SELECT * FROM complete where x > 1.0 AND x < 2.0 +---- + +query T rowsort +PLAN SELECT * FROM complete where x >= 1.0 AND x <= 2.0 +---- +12 values hashing to 3962f58904fdf0c1ca6acb8a4f69f761 + +query RRR rowsort +SELECT * FROM complete where x >= 1.0 AND x <= 2.0 +---- +12 values hashing to 19ea7cdb36e521974206b7c240f5766e + +query T rowsort +PLAN SELECT * FROM complete WHERE x BETWEEN 0 AND 2 AND Y BETWEEN 0 AND 2 +---- +12 values hashing to 72e202e250c0fa58b3461614b62f88f4 + +query RRR rowsort +SELECT * FROM complete WHERE x BETWEEN 0 AND 2 AND Y BETWEEN 0 AND 2 +---- +18 values hashing to 7991743d9b030b03e873bc2ca4e6a6c1 + +statement ok +ROLLBACK + + diff --git a/sql/test/subquery/Tests/subquery6.test b/sql/test/subquery/Tests/subquery6.test --- a/sql/test/subquery/Tests/subquery6.test +++ b/sql/test/subquery/Tests/subquery6.test @@ -398,7 +398,7 @@ statement error select (select sum(i1.i + i2.i) in (select sum(i1.i + i2.i)) from integers i2 group by i2.i) from integers i1 group by i1.i _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
