Skip to content

Commit 832c0a4

Browse files
scrappyscrappy
committed
split out a bunch more tests from misc.source so that the tester knows
what's being tested :)
1 parent cac5546 commit 832c0a4

File tree

9 files changed

+654
-562
lines changed

9 files changed

+654
-562
lines changed

‎src/test/regress/input/misc.source

Lines changed: 0 additions & 547 deletions
Large diffs are not rendered by default.

‎src/test/regress/sql/aggregates.sql

Lines changed: 132 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,132 @@
1+
--
2+
-- replace
3+
--
4+
--
5+
-- BTREE
6+
--
7+
UPDATE onek
8+
SET unique1 = onek.unique1 + 1;
9+
10+
UPDATE onek
11+
SET unique1 = onek.unique1 - 1;
12+
13+
--
14+
-- BTREE partial
15+
--
16+
-- UPDATE onek2
17+
-- SET unique1 = onek2.unique1 + 1;
18+
19+
--UPDATE onek2
20+
-- SET unique1 = onek2.unique1 - 1;
21+
22+
--
23+
-- BTREE shutting out non-functional updates
24+
--
25+
-- the following two tests seem to take a long time on some
26+
-- systems. This non-func update stuff needs to be examined
27+
-- more closely. - jolly (2/22/96)
28+
--
29+
UPDATE temp
30+
SET stringu1 = reverse_c16(onek.stringu1)
31+
WHERE onek.stringu1 = 'JBAAAA' and
32+
onek.stringu1 = temp.stringu1;
33+
34+
UPDATE temp
35+
SET stringu1 = reverse_c16(onek2.stringu1)
36+
WHERE onek2.stringu1 = 'JCAAAA' and
37+
onek2.stringu1 = temp.stringu1;
38+
39+
DROP TABLE temp;
40+
41+
--UPDATE person*
42+
-- SET age = age + 1;
43+
44+
--UPDATE person*
45+
-- SET age = age + 3
46+
-- WHERE name = 'linda';
47+
48+
--
49+
-- copy
50+
--
51+
COPY onek TO '_OBJWD_/results/onek.data';
52+
53+
DELETE FROM onek;
54+
55+
COPY onek FROM '_OBJWD_/results/onek.data';
56+
57+
SELECT unique1 FROM onek WHERE unique1 < 2;
58+
59+
DELETE FROM onek2;
60+
61+
COPY onek2 FROM '_OBJWD_/results/onek.data';
62+
63+
SELECT unique1 FROM onek2 WHERE unique1 < 2;
64+
65+
COPY BINARY stud_emp TO '_OBJWD_/results/stud_emp.data';
66+
67+
DELETE FROM stud_emp;
68+
69+
COPY BINARY stud_emp FROM '_OBJWD_/results/stud_emp.data';
70+
71+
SELECT * FROM stud_emp;
72+
73+
-- COPY aggtest FROM stdin;
74+
-- 56 7.8
75+
-- 100 99.097
76+
-- 0 0.09561
77+
-- 42 324.78
78+
-- .
79+
-- COPY aggtest TO stdout;
80+
81+
82+
--
83+
-- test the random function
84+
--
85+
-- count the number of tuples originally
86+
SELECT count(*) FROM onek;
87+
88+
-- select roughly 1/10 of the tuples
89+
SELECT count(*) FROM onek where oidrand(onek.oid, 10);
90+
91+
-- select again, the count should be different
92+
SELECT count(*) FROM onek where oidrand(onek.oid, 10);
93+
94+
--
95+
-- AGGREGATES
96+
--
97+
SELECT avg(four) AS avg_1 FROM onek;
98+
99+
SELECT avg(a) AS avg_49 FROM aggtest WHERE a < 100;
100+
101+
SELECT avg(b) AS avg_107_943 FROM aggtest;
102+
103+
SELECT avg(gpa) AS avg_3_4 FROM student;
104+
105+
106+
SELECT sum(four) AS sum_1500 FROM onek;
107+
108+
SELECT sum(a) AS sum_198 FROM aggtest;
109+
110+
SELECT sum(b) AS avg_431_773 FROM aggtest;
111+
112+
SELECT sum(gpa) AS avg_6_8 FROM student;
113+
114+
115+
SELECT max(four) AS max_3 FROM onek;
116+
117+
SELECT max(a) AS max_100 FROM aggtest;
118+
119+
SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
120+
121+
SELECT max(student.gpa) AS max_3_7 FROM student;
122+
123+
124+
SELECT count(four) AS cnt_1000 FROM onek;
125+
126+
127+
SELECT newavg(four) AS avg_1 FROM onek;
128+
129+
SELECT newsum(four) AS sum_1500 FROM onek;
130+
131+
SELECT newcnt(four) AS cnt_1000 FROM onek;
132+

‎src/test/regress/sql/alter_table.sql

Lines changed: 159 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,159 @@
1+
--
2+
-- add attribute
3+
--
4+
CREATE TABLE temp (initial int4);
5+
6+
ALTER TABLE temp ADD COLUMN a int4;
7+
8+
ALTER TABLE temp ADD COLUMN b char16;
9+
10+
ALTER TABLE temp ADD COLUMN c text;
11+
12+
ALTER TABLE temp ADD COLUMN d float8;
13+
14+
ALTER TABLE temp ADD COLUMN e float4;
15+
16+
ALTER TABLE temp ADD COLUMN f int2;
17+
18+
ALTER TABLE temp ADD COLUMN g polygon;
19+
20+
ALTER TABLE temp ADD COLUMN h abstime;
21+
22+
ALTER TABLE temp ADD COLUMN i char;
23+
24+
ALTER TABLE temp ADD COLUMN j abstime[];
25+
26+
ALTER TABLE temp ADD COLUMN k dt;
27+
28+
ALTER TABLE temp ADD COLUMN l tid;
29+
30+
ALTER TABLE temp ADD COLUMN m xid;
31+
32+
ALTER TABLE temp ADD COLUMN n oid8;
33+
34+
--ALTER TABLE temp ADD COLUMN o lock;
35+
ALTER TABLE temp ADD COLUMN p smgr;
36+
37+
ALTER TABLE temp ADD COLUMN q point;
38+
39+
ALTER TABLE temp ADD COLUMN r lseg;
40+
41+
ALTER TABLE temp ADD COLUMN s path;
42+
43+
ALTER TABLE temp ADD COLUMN t box;
44+
45+
ALTER TABLE temp ADD COLUMN u tinterval;
46+
47+
ALTER TABLE temp ADD COLUMN v oidint4;
48+
49+
ALTER TABLE temp ADD COLUMN w oidname;
50+
51+
ALTER TABLE temp ADD COLUMN x float8[];
52+
53+
ALTER TABLE temp ADD COLUMN y float4[];
54+
55+
ALTER TABLE temp ADD COLUMN z int2[];
56+
57+
INSERT INTO temp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
58+
v, w, x, y, z)
59+
VALUES (4, 'char16', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
60+
'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
61+
314159, '(1,1)', 512,
62+
'1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
63+
'(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["current" "infinity"]',
64+
'1/3', '1,char16', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
65+
66+
SELECT * FROM temp;
67+
68+
DROP TABLE temp;
69+
70+
-- the wolf bug - schema mods caused inconsistent row descriptors
71+
CREATE TABLE temp (
72+
initial int4
73+
) ARCHIVE = light;
74+
75+
ALTER TABLE temp ADD COLUMN a int4;
76+
77+
ALTER TABLE temp ADD COLUMN b char16;
78+
79+
ALTER TABLE temp ADD COLUMN c text;
80+
81+
ALTER TABLE temp ADD COLUMN d float8;
82+
83+
ALTER TABLE temp ADD COLUMN e float4;
84+
85+
ALTER TABLE temp ADD COLUMN f int2;
86+
87+
ALTER TABLE temp ADD COLUMN g polygon;
88+
89+
ALTER TABLE temp ADD COLUMN h abstime;
90+
91+
ALTER TABLE temp ADD COLUMN i char;
92+
93+
ALTER TABLE temp ADD COLUMN j abstime[];
94+
95+
ALTER TABLE temp ADD COLUMN k dt;
96+
97+
ALTER TABLE temp ADD COLUMN l tid;
98+
99+
ALTER TABLE temp ADD COLUMN m xid;
100+
101+
ALTER TABLE temp ADD COLUMN n oid8;
102+
103+
--ALTER TABLE temp ADD COLUMN o lock;
104+
ALTER TABLE temp ADD COLUMN p smgr;
105+
106+
ALTER TABLE temp ADD COLUMN q point;
107+
108+
ALTER TABLE temp ADD COLUMN r lseg;
109+
110+
ALTER TABLE temp ADD COLUMN s path;
111+
112+
ALTER TABLE temp ADD COLUMN t box;
113+
114+
ALTER TABLE temp ADD COLUMN u tinterval;
115+
116+
ALTER TABLE temp ADD COLUMN v oidint4;
117+
118+
ALTER TABLE temp ADD COLUMN w oidname;
119+
120+
ALTER TABLE temp ADD COLUMN x float8[];
121+
122+
ALTER TABLE temp ADD COLUMN y float4[];
123+
124+
ALTER TABLE temp ADD COLUMN z int2[];
125+
126+
INSERT INTO temp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u,
127+
v, w, x, y, z)
128+
VALUES (4, 'char16', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)',
129+
'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}',
130+
314159, '(1,1)', 512,
131+
'1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)',
132+
'(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["current" "infinity"]',
133+
'1/3', '1,char16', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}');
134+
135+
SELECT * FROM temp[,];
136+
137+
DROP TABLE temp;
138+
139+
140+
--
141+
-- rename -
142+
-- should preserve indices
143+
--
144+
ALTER TABLE tenk1 RENAME TO ten_k;
145+
146+
-- 20 values, sorted
147+
SELECT unique1 FROM ten_k WHERE unique1 < 20;
148+
149+
-- 20 values, sorted
150+
SELECT unique2 FROM ten_k WHERE unique2 < 20;
151+
152+
-- 100 values, sorted
153+
SELECT hundred FROM ten_k WHERE hundred = 50;
154+
155+
ALTER TABLE ten_k RENAME TO tenk1;
156+
157+
-- 5 values, sorted
158+
SELECT unique1 FROM tenk1 WHERE unique1 < 5;
159+

0 commit comments

Comments
 (0)