Changeset: cbd3ea432a77 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=cbd3ea432a77
Added Files:
sql/test/pg_regress/monetdb/abstime.sql
sql/test/pg_regress/monetdb/aggregates.sql
sql/test/pg_regress/monetdb/alter_table.sql
sql/test/pg_regress/monetdb/arrays.sql
sql/test/pg_regress/monetdb/bit.sql
sql/test/pg_regress/monetdb/boolean.sql
sql/test/pg_regress/monetdb/box.sql
sql/test/pg_regress/monetdb/btree_index.sql
sql/test/pg_regress/monetdb/case.sql
sql/test/pg_regress/monetdb/circle.sql
sql/test/pg_regress/monetdb/cluster.sql
sql/test/pg_regress/monetdb/comments.sql
sql/test/pg_regress/monetdb/conversion.sql
Modified Files:
sql/test/pg_regress/monetdb/char.sql
Branch: default
Log Message:
adding files
diffs (truncated from 2812 to 300 lines):
diff --git a/sql/test/pg_regress/monetdb/abstime.sql
b/sql/test/pg_regress/monetdb/abstime.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/pg_regress/monetdb/abstime.sql
@@ -0,0 +1,70 @@
+--
+-- ABSTIME
+-- testing built-in time type abstime
+-- uses reltime and tinterval
+--
+
+--
+-- timezones may vary based not only on location but the operating
+-- system. the main correctness issue is that the OS may not get
+-- daylight savings time right for times prior to Unix epoch (jan 1 1970).
+--
+
+CREATE TABLE ABSTIME_TBL (f1 time);
+
+--START TRANSACTION;
+INSERT INTO ABSTIME_TBL (f1) VALUES (current_time);
+INSERT INTO ABSTIME_TBL (f1) VALUES (current_time);
+SELECT * FROM ABSTIME_TBL;
+-- 2 rows
+SELECT count(*) AS two FROM ABSTIME_TBL WHERE f1 = current_time;
+COMMIT;
+
+DELETE FROM ABSTIME_TBL;
+COMMIT;
+
+INSERT INTO ABSTIME_TBL (f1) VALUES ('Jan 14, 1973 03:14:21');
+INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'Mon May 1 00:30:30 1995');
+INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'epoch');
+INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'infinity');
+INSERT INTO ABSTIME_TBL (f1) VALUES (abstime '-infinity');
+INSERT INTO ABSTIME_TBL (f1) VALUES (abstime 'May 10, 1947 23:59:12');
+
+-- what happens if we specify slightly misformatted abstime?
+INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00');
+INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10');
+
+-- badly formatted abstimes: these should result in invalid abstimes
+INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format');
+INSERT INTO ABSTIME_TBL (f1) VALUES ('Jun 10, 1843');
+
+-- test abstime operators
+
+SELECT '' AS eight, ABSTIME_TBL.*;
+
+SELECT '' AS six, ABSTIME_TBL.*
+ WHERE ABSTIME_TBL.f1 < abstime 'Jun 30, 2001';
+
+SELECT '' AS six, ABSTIME_TBL.*
+ WHERE ABSTIME_TBL.f1 > abstime '-infinity';
+
+SELECT '' AS six, ABSTIME_TBL.*
+ WHERE abstime 'May 10, 1947 23:59:12' <> ABSTIME_TBL.f1;
+
+SELECT '' AS three, ABSTIME_TBL.*
+ WHERE abstime 'epoch' >= ABSTIME_TBL.f1;
+
+SELECT '' AS four, ABSTIME_TBL.*
+ WHERE ABSTIME_TBL.f1 <= abstime 'Jan 14, 1973 03:14:21';
+
+SELECT '' AS four, ABSTIME_TBL.*
+ WHERE ABSTIME_TBL.f1 <?>
+ tinterval '["Apr 1 1950 00:00:00" "Dec 30 1999 23:00:00"]';
+
+SELECT '' AS four, f1 AS abstime,
+ date_part('year', f1) AS year, date_part('month', f1) AS month,
+ date_part('day',f1) AS day, date_part('hour', f1) AS hour,
+ date_part('minute', f1) AS minute, date_part('second', f1) AS second
+ FROM ABSTIME_TBL
+ WHERE isfinite(f1)
+ ORDER BY abstime;
diff --git a/sql/test/pg_regress/monetdb/aggregates.sql
b/sql/test/pg_regress/monetdb/aggregates.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/pg_regress/monetdb/aggregates.sql
@@ -0,0 +1,182 @@
+--
+-- AGGREGATES
+--
+
+SELECT avg(four) AS avg_1 FROM onek;
+
+SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
+
+-- In 7.1, avg(float) is computed using double arithmetic.
+-- Round the result to 3 digits to avoid platform-specific results.
+
+SELECT cast(avg(b) as numeric(10,3)) AS avg_107_943 FROM aggtest;
+
+SELECT avg(gpa) AS avg_3_4 FROM student;
+
+
+SELECT sum(four) AS sum_1500 FROM onek;
+
+SELECT sum(a) AS sum_198 FROM aggtest;
+
+SELECT sum(b) AS avg_431_773 FROM aggtest;
+
+SELECT sum(gpa) AS avg_6_8 FROM student;
+
+
+SELECT max(four) AS max_3 FROM onek;
+
+SELECT max(a) AS max_100 FROM aggtest;
+
+SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
+
+SELECT max(student.gpa) AS max_3_7 FROM student;
+
+
+SELECT count(four) AS cnt_1000 FROM onek;
+
+SELECT count(DISTINCT four) AS cnt_4 FROM onek;
+
+select ten, count(*), sum(four) from onek
+group by ten order by ten;
+
+select ten, count(four), sum(DISTINCT four) from onek
+group by ten order by ten;
+
+
+SELECT newavg(four) AS avg_1 FROM onek;
+
+SELECT newsum(four) AS sum_1500 FROM onek;
+
+SELECT newcnt(four) AS cnt_1000 FROM onek;
+
+
+-- test for outer-level aggregates
+
+-- this should work
+select ten, sum(distinct four) from onek a
+group by ten
+having exists (select 1 from onek b where sum(distinct a.four) = b.four);
+
+-- this should fail because subquery has an agg of its own in WHERE
+select ten, sum(distinct four) from onek a
+group by ten
+having exists (select 1 from onek b
+ where sum(distinct a.four + b.four) = b.four);
+
+--
+-- test for bitwise integer aggregates
+--
+CREATE TEMPORARY TABLE bitwise_test(
+ i2 smallint,
+ i4 integer,
+ i8 bigint,
+ i INTEGER,
+ x smallint,
+ y BIT(4)
+);
+
+-- empty case
+SELECT
+ BIT_AND(i2) AS "?",
+ BIT_OR(i4) AS "?"
+FROM bitwise_test;
+
+COPY bitwise_test FROM STDIN NULL 'null';
+1 1 1 1 1 B0101
+3 3 3 null 2 B0100
+7 7 7 3 4 B1100
+\.
+
+SELECT
+ BIT_AND(i2) AS "1",
+ BIT_AND(i4) AS "1",
+ BIT_AND(i8) AS "1",
+ BIT_AND(i) AS "?",
+ BIT_AND(x) AS "0",
+ BIT_AND(y) AS "0100",
+
+ BIT_OR(i2) AS "7",
+ BIT_OR(i4) AS "7",
+ BIT_OR(i8) AS "7",
+ BIT_OR(i) AS "?",
+ BIT_OR(x) AS "7",
+ BIT_OR(y) AS "1101"
+FROM bitwise_test;
+
+--
+-- test boolean aggregates
+--
+-- first test all possible transition and final states
+
+SELECT
+ -- boolean and transitions
+ -- null because strict
+ booland_statefunc(NULL, NULL) IS NULL AS "t",
+ booland_statefunc(TRUE, NULL) IS NULL AS "t",
+ booland_statefunc(FALSE, NULL) IS NULL AS "t",
+ booland_statefunc(NULL, TRUE) IS NULL AS "t",
+ booland_statefunc(NULL, FALSE) IS NULL AS "t",
+ -- and actual computations
+ booland_statefunc(TRUE, TRUE) AS "t",
+ NOT booland_statefunc(TRUE, FALSE) AS "t",
+ NOT booland_statefunc(FALSE, TRUE) AS "t",
+ NOT booland_statefunc(FALSE, FALSE) AS "t";
+
+SELECT
+ -- boolean or transitions
+ -- null because strict
+ boolor_statefunc(NULL, NULL) IS NULL AS "t",
+ boolor_statefunc(TRUE, NULL) IS NULL AS "t",
+ boolor_statefunc(FALSE, NULL) IS NULL AS "t",
+ boolor_statefunc(NULL, TRUE) IS NULL AS "t",
+ boolor_statefunc(NULL, FALSE) IS NULL AS "t",
+ -- actual computations
+ boolor_statefunc(TRUE, TRUE) AS "t",
+ boolor_statefunc(TRUE, FALSE) AS "t",
+ boolor_statefunc(FALSE, TRUE) AS "t",
+ NOT boolor_statefunc(FALSE, FALSE) AS "t";
+
+CREATE TEMPORARY TABLE bool_test(
+ b1 BOOL,
+ b2 BOOL,
+ b3 BOOL,
+ b4 BOOL);
+
+-- empty case
+SELECT
+ BOOL_AND(b1) AS "n",
+ BOOL_OR(b3) AS "n"
+FROM bool_test;
+
+COPY bool_test FROM STDIN NULL 'null';
+TRUE null FALSE null
+FALSE TRUE null null
+null TRUE FALSE null
+\.
+
+SELECT
+ BOOL_AND(b1) AS "f",
+ BOOL_AND(b2) AS "t",
+ BOOL_AND(b3) AS "f",
+ BOOL_AND(b4) AS "n",
+ BOOL_AND(NOT b2) AS "f",
+ BOOL_AND(NOT b3) AS "t"
+FROM bool_test;
+
+SELECT
+ EVERY(b1) AS "f",
+ EVERY(b2) AS "t",
+ EVERY(b3) AS "f",
+ EVERY(b4) AS "n",
+ EVERY(NOT b2) AS "f",
+ EVERY(NOT b3) AS "t"
+FROM bool_test;
+
+SELECT
+ BOOL_OR(b1) AS "t",
+ BOOL_OR(b2) AS "t",
+ BOOL_OR(b3) AS "f",
+ BOOL_OR(b4) AS "n",
+ BOOL_OR(NOT b2) AS "f",
+ BOOL_OR(NOT b3) AS "t"
+FROM bool_test;
diff --git a/sql/test/pg_regress/monetdb/alter_table.sql
b/sql/test/pg_regress/monetdb/alter_table.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/pg_regress/monetdb/alter_table.sql
@@ -0,0 +1,977 @@
+--
+-- ALTER_TABLE
+-- add attribute
+--
+
+CREATE TABLE tmp (initial integer);
+
+
+
+
+
+ALTER TABLE tmp ADD COLUMN a integer default 3;
+
+ALTER TABLE tmp ADD COLUMN b name;
+
+ALTER TABLE tmp ADD COLUMN c text;
+
+ALTER TABLE tmp ADD COLUMN d double;
+
+ALTER TABLE tmp ADD COLUMN e float;
+
+ALTER TABLE tmp ADD COLUMN f smallint;
+
+ALTER TABLE tmp ADD COLUMN g string;
+
+ALTER TABLE tmp ADD COLUMN h abstime;
+
+ALTER TABLE tmp ADD COLUMN i char;
+
+ALTER TABLE tmp ADD COLUMN j abstime[];
+
+ALTER TABLE tmp ADD COLUMN k integer;
+
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
