Changeset: 439edab3bcc5 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=439edab3bcc5
Added Files:
sql/test/pg_regress/monetdb/copy2.sql
sql/test/pg_regress/monetdb/create_aggregate.sql
sql/test/pg_regress/monetdb/create_index.sql
sql/test/pg_regress/monetdb/create_misc.sql
sql/test/pg_regress/monetdb/create_operator.sql
sql/test/pg_regress/monetdb/create_type.sql
sql/test/pg_regress/monetdb/create_view.sql
sql/test/pg_regress/monetdb/date.sql
sql/test/pg_regress/monetdb/domain.sql
sql/test/pg_regress/monetdb/drop.sql
sql/test/pg_regress/monetdb/errors.sql
sql/test/pg_regress/monetdb/float4.sql
sql/test/pg_regress/monetdb/float8.sql
sql/test/pg_regress/monetdb/foreign_key.sql
sql/test/pg_regress/monetdb/geometry.sql
sql/test/pg_regress/monetdb/hash_index.sql
sql/test/pg_regress/monetdb/horology.sql
sql/test/pg_regress/monetdb/inet.sql
sql/test/pg_regress/monetdb/inherit.sql
sql/test/pg_regress/monetdb/insert.sql
sql/test/pg_regress/monetdb/int2.sql
sql/test/pg_regress/monetdb/int4.sql
sql/test/pg_regress/monetdb/int8.sql
sql/test/pg_regress/monetdb/interval.sql
sql/test/pg_regress/monetdb/join.sql
sql/test/pg_regress/monetdb/limit.sql
sql/test/pg_regress/monetdb/lseg.sql
sql/test/pg_regress/monetdb/mule_internal.sql
sql/test/pg_regress/monetdb/name.sql
sql/test/pg_regress/monetdb/namespace.sql
sql/test/pg_regress/monetdb/numeric.sql
sql/test/pg_regress/monetdb/numeric_big.sql
sql/test/pg_regress/monetdb/numerology.sql
sql/test/pg_regress/monetdb/oid.sql
sql/test/pg_regress/monetdb/oidjoins.sql
sql/test/pg_regress/monetdb/opr_sanity.sql
sql/test/pg_regress/monetdb/path.sql
sql/test/pg_regress/monetdb/plpgsql.sql
sql/test/pg_regress/monetdb/point.sql
sql/test/pg_regress/monetdb/polygon.sql
sql/test/pg_regress/monetdb/polymorphism.sql
sql/test/pg_regress/monetdb/portals.sql
sql/test/pg_regress/monetdb/portals_p2.sql
sql/test/pg_regress/monetdb/prepare.sql
sql/test/pg_regress/monetdb/privileges.sql
sql/test/pg_regress/monetdb/random.sql
sql/test/pg_regress/monetdb/rangefuncs.sql
sql/test/pg_regress/monetdb/reltime.sql
sql/test/pg_regress/monetdb/rowtypes.sql
sql/test/pg_regress/monetdb/rules.sql
sql/test/pg_regress/monetdb/sanity_check.sql
sql/test/pg_regress/monetdb/select.sql
sql/test/pg_regress/monetdb/select_distinct.sql
sql/test/pg_regress/monetdb/select_distinct_on.sql
sql/test/pg_regress/monetdb/select_having.sql
sql/test/pg_regress/monetdb/select_implicit.sql
sql/test/pg_regress/monetdb/select_into.sql
sql/test/pg_regress/monetdb/select_views.sql
sql/test/pg_regress/monetdb/sequence.sql
sql/test/pg_regress/monetdb/sql_ascii.sql
sql/test/pg_regress/monetdb/stats.sql
sql/test/pg_regress/monetdb/strings.sql
sql/test/pg_regress/monetdb/subselect.sql
sql/test/pg_regress/monetdb/temp.sql
sql/test/pg_regress/monetdb/time.sql
sql/test/pg_regress/monetdb/timestamp.sql
sql/test/pg_regress/monetdb/timestamptz.sql
sql/test/pg_regress/monetdb/timetz.sql
sql/test/pg_regress/monetdb/tinterval.sql
sql/test/pg_regress/monetdb/transactions.sql
sql/test/pg_regress/monetdb/triggers.sql
sql/test/pg_regress/monetdb/truncate.sql
sql/test/pg_regress/monetdb/type_sanity.sql
sql/test/pg_regress/monetdb/union.sql
sql/test/pg_regress/monetdb/update.sql
sql/test/pg_regress/monetdb/vacuum.sql
sql/test/pg_regress/monetdb/without_oid.sql
Branch: Jan2014
Log Message:
adding sql test files
(transplanted from 7fe906fcda85a41b3add2d44b0437cc24f800c68)
diffs (truncated from 15049 to 300 lines):
diff --git a/sql/test/pg_regress/monetdb/copy2.sql
b/sql/test/pg_regress/monetdb/copy2.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/pg_regress/monetdb/copy2.sql
@@ -0,0 +1,134 @@
+CREATE TABLE x (
+ a serial,
+ b int,
+ c text not null default 'stuff',
+ d text,
+ e text
+);
+
+CREATE FUNCTION fn_x_before () RETURNS TRIGGER AS '
+ BEGIN
+ NEW.e := ''before trigger fired''::text;
+ return NEW;
+ COMMIT;
+' language 'plpgsql';
+
+CREATE FUNCTION fn_x_after () RETURNS TRIGGER AS '
+ BEGIN
+ UPDATE x set e=''after trigger fired'' where c=''stuff'';
+ return NULL;
+ COMMIT;
+' language 'plpgsql';
+
+CREATE TRIGGER trg_x_after AFTER INSERT ON x
+FOR EACH ROW EXECUTE PROCEDURE fn_x_after();
+
+CREATE TRIGGER trg_x_before BEFORE INSERT ON x
+FOR EACH ROW EXECUTE PROCEDURE fn_x_before();
+
+COPY x (a, b, c, d, e) from stdin;
+9999 \N \\N \NN \N
+10000 21 31 41 51
+\.
+
+COPY x (b, d) from stdin;
+1 test_1
+\.
+
+COPY x (b, d) from stdin;
+2 test_2
+3 test_3
+4 test_4
+5 test_5
+\.
+
+COPY x (a, b, c, d, e) from stdin;
+10001 22 32 42 52
+10002 23 33 43 53
+10003 24 34 44 54
+10004 25 35 45 55
+10005 26 36 46 56
+\.
+
+-- non-existent column in column list: should fail
+COPY x (xyz) from stdin;
+
+-- too many columns in column list: should fail
+COPY x (a, b, c, d, e, d, c) from stdin;
+
+-- missing data: should fail
+COPY x from stdin;
+
+\.
+COPY x from stdin;
+2000 230 23 23
+\.
+COPY x from stdin;
+2001 231 \N \N
+\.
+
+-- extra data: should fail
+COPY x from stdin;
+2002 232 40 50 60 70 80
+\.
+
+-- various COPY options: delimiters, oids, NULL string
+COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
+500000,x,45,80,90
+500001,x,\x,\\x,\\\x
+500002,x,\,,\\\,,\\
+\.
+
+COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
+3000;;c;;
+\.
+
+COPY x from stdin WITH DELIMITER AS ':' NULL AS '\\X';
+4000:\X:C:\X:\X
+4001:1:empty::
+4002:2:null:\X:\X
+4003:3:Backslash:\\:\\
+4004:4:BackslashX:\\X:\\X
+4005:5:N:\N:\N
+4006:6:BackslashN:\\N:\\N
+4007:7:XX:\XX:\XX
+4008:8:Delimiter:\::\:
+\.
+
+-- check results of copy in
+SELECT * FROM x;
+
+-- COPY w/ oids on a table w/o oids should fail
+CREATE TABLE no_oids (
+ a int,
+ b int
+) WITHOUT OIDS;
+
+INSERT INTO no_oids (a, b) VALUES (5, 10);
+INSERT INTO no_oids (a, b) VALUES (20, 30);
+
+-- should fail
+COPY no_oids FROM stdin WITH OIDS;
+COPY no_oids TO stdout WITH OIDS;
+
+-- check copy out
+COPY x TO stdout;
+COPY x (c, e) TO stdout;
+COPY x (b, e) TO stdout WITH NULL 'I''m null';
+
+CREATE TABLE y (
+ col1 text,
+ col2 text
+);
+
+INSERT INTO y VALUES ('Jackson, Sam', '\\h');
+INSERT INTO y VALUES ('It is "perfect".','\t');
+INSERT INTO y VALUES ('', NULL);
+
+COPY y TO stdout WITH CSV;
+COPY y TO stdout WITH CSV QUOTE '''' DELIMITER '|';
+COPY y TO stdout WITH CSV FORCE QUOTE col2 ESCAPE '\\';
+
+DROP TABLE x, y;
+DROP FUNCTION fn_x_before();
+DROP FUNCTION fn_x_after();
diff --git a/sql/test/pg_regress/monetdb/create_aggregate.sql
b/sql/test/pg_regress/monetdb/create_aggregate.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/pg_regress/monetdb/create_aggregate.sql
@@ -0,0 +1,31 @@
+--
+-- CREATE_AGGREGATE
+--
+
+-- all functions CREATEd
+CREATE AGGREGATE newavg (
+ sfunc = int4_accum, basetype = integer, stype = _numeric,
+ finalfunc = numeric_avg,
+ initcond1 = '{0,0,0}'
+);
+
+-- test comments
+
+
+
+
+-- without finalfunc; test obsolete spellings 'sfunc1' etc
+CREATE AGGREGATE newsum (
+ sfunc1 = int4pl, basetype = integer, stype1 = integer,
+ initcond1 = '0'
+);
+
+-- value-independent transition function
+CREATE AGGREGATE newcnt (
+ sfunc = int4inc, basetype = 'any', stype = integer,
+ initcond = '0'
+);
+
+
+
+
diff --git a/sql/test/pg_regress/monetdb/create_index.sql
b/sql/test/pg_regress/monetdb/create_index.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/pg_regress/monetdb/create_index.sql
@@ -0,0 +1,135 @@
+--
+-- CREATE_INDEX
+-- Create ancillary data structures (i.e. indices)
+--
+
+--
+-- BTREE
+--
+CREATE INDEX onek_unique1 ON onek USING btree(unique1 int4_ops);
+
+CREATE INDEX onek_unique2 ON onek USING btree(unique2 int4_ops);
+
+CREATE INDEX onek_hundred ON onek USING btree(hundred int4_ops);
+
+CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops);
+
+CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
+
+CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops);
+
+CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops);
+
+CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops);
+
+CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops);
+
+CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred int4_ops);
+
+CREATE INDEX rix ON road USING btree (name text_ops);
+
+CREATE INDEX iix ON ihighway USING btree (name text_ops);
+
+CREATE INDEX six ON shighway USING btree (name text_ops);
+
+-- test comments
+
+
+
+
+--
+-- BTREE ascending/descending cases
+--
+-- we load integer/text from pure descending data (each key is a new
+-- low key) and name/f8 from pure ascending data (each key is a new
+-- high key). we had a bug where new low keys would sometimes be
+-- "lost".
+--
+CREATE INDEX bt_i4_index ON bt_i4_heap USING btree (seqno int4_ops);
+
+CREATE INDEX bt_name_index ON bt_name_heap USING btree (seqno name_ops);
+
+CREATE INDEX bt_txt_index ON bt_txt_heap USING btree (seqno text_ops);
+
+CREATE INDEX bt_f8_index ON bt_f8_heap USING btree (seqno float8_ops);
+
+--
+-- BTREE partial indices
+--
+CREATE INDEX onek2_u1_prtl ON onek2 USING btree(unique1 int4_ops)
+ where unique1 < 20 or unique1 > 980;
+
+CREATE INDEX onek2_u2_prtl ON onek2 USING btree(unique2 int4_ops)
+ where stringu1 < 'B';
+
+CREATE INDEX onek2_stu1_prtl ON onek2 USING btree(stringu1 name_ops)
+ where onek2.stringu1 >= 'J' and onek2.stringu1 < 'K';
+
+--
+-- RTREE
+--
+-- rtrees use a quadratic page-splitting algorithm that takes a
+-- really, really long time. we don't test all rtree opclasses
+-- in the regression test (we check them using the sequoia 2000
+-- benchmark).
+--
+CREATE INDEX rect2ind ON fast_emp4000 USING rtree (home_base);
+
+-- there's no easy way to check that this command actually is using
+-- the index, unfortunately. (EXPLAIN would work, but its output
+-- changes too often for me to want to put an EXPLAIN in the test...)
+SELECT * FROM fast_emp4000
+ WHERE home_base @ '(200,200),(2000,1000)'::string
+ ORDER BY home_base USING <<;
+
+--
+-- HASH
+--
+CREATE INDEX hash_i4_index ON hash_i4_heap USING hash (random int4_ops);
+
+CREATE INDEX hash_name_index ON hash_name_heap USING hash (random name_ops);
+
+CREATE INDEX hash_txt_index ON hash_txt_heap USING hash (random text_ops);
+
+CREATE INDEX hash_f8_index ON hash_f8_heap USING hash (random float8_ops);
+
+-- CREATE INDEX hash_ovfl_index ON hash_ovfl_heap USING hash (x int4_ops);
+
+
+--
+-- Test functional index
+--
+CREATE TABLE func_index_heap (f1 text, f2 text);
+CREATE UNIQUE INDEX func_index_index on func_index_heap (textcat(f1,f2));
+
+INSERT INTO func_index_heap VALUES('ABC','DEF');
+INSERT INTO func_index_heap VALUES('AB','CDEFG');
+INSERT INTO func_index_heap VALUES('QWE','RTY');
+-- this should fail because of unique index:
+INSERT INTO func_index_heap VALUES('ABCD', 'EF');
+-- but this shouldn't:
+INSERT INTO func_index_heap VALUES('QWERTY');
+
+
+--
+-- Same test, expressional index
+--
+DROP TABLE func_index_heap;
+CREATE TABLE func_index_heap (f1 text, f2 text);
+CREATE UNIQUE INDEX func_index_index on func_index_heap ((f1 || f2) text_ops);
+
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
