Skip to content

Commit d108a31

Browse files
scrappyscrappy
committed
There are the broken out 'sql' queries from queries.source
tests allows us to have a 'for...done' loop inside of regress.sh for both doing the tests, and determining fail/ok results
1 parent 9607e69 commit d108a31

File tree

9 files changed

+582
-0
lines changed

9 files changed

+582
-0
lines changed

‎src/test/regress/sql/abstime.sql

Lines changed: 87 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,87 @@
1+
-- **** testing built-in time types: abstime, reltime, and tinterval ****
2+
3+
--
4+
-- timezones may vary based not only on location but the operating
5+
-- system. the main correctness issue is that the OS may not get
6+
-- DST right for times prior to unix epoch (jan 1 1970).
7+
--
8+
9+
CREATE TABLE ABSTIME_TBL (f1 abstime);
10+
11+
INSERT INTO ABSTIME_TBL (f1) VALUES ('Jan 14, 1973 03:14:21');
12+
13+
-- was INSERT INTO ABSTIME_TBL (f1) VALUES ('now'::abstime):
14+
INSERT INTO ABSTIME_TBL (f1) VALUES ('Mon May 1 00:30:30 1995'::abstime);
15+
16+
INSERT INTO ABSTIME_TBL (f1) VALUES ('epoch'::abstime);
17+
18+
INSERT INTO ABSTIME_TBL (f1) VALUES ('current'::abstime);
19+
20+
INSERT INTO ABSTIME_TBL (f1) VALUES ('infinity'::abstime);
21+
22+
INSERT INTO ABSTIME_TBL (f1) VALUES ('-infinity'::abstime);
23+
24+
INSERT INTO ABSTIME_TBL (f1) VALUES ('May 10, 1943 23:59:12');
25+
26+
27+
-- what happens if we specify slightly misformatted abstime?
28+
INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 35, 1946 10:00:00');
29+
30+
INSERT INTO ABSTIME_TBL (f1) VALUES ('Feb 28, 1984 25:08:10');
31+
32+
33+
-- badly formatted abstimes: these should result in invalid abstimes
34+
INSERT INTO ABSTIME_TBL (f1) VALUES ('bad date format');
35+
36+
INSERT INTO ABSTIME_TBL (f1) VALUES ('Jun 10, 1843');
37+
38+
-- test abstime operators
39+
40+
SELECT '' AS eleven, ABSTIME_TBL.*;
41+
42+
SELECT '' AS eight, ABSTIME_TBL.*
43+
WHERE ABSTIME_TBL.f1 < 'Jun 30, 2001'::abstime;
44+
45+
SELECT '' AS eight, ABSTIME_TBL.*
46+
WHERE ABSTIME_TBL.f1 > '-infinity'::abstime;
47+
48+
SELECT '' AS eight, ABSTIME_TBL.*
49+
WHERE 'May 10, 1943 23:59:12'::abstime <> ABSTIME_TBL.f1;
50+
51+
SELECT '' AS one, ABSTIME_TBL.*
52+
WHERE 'current'::abstime = ABSTIME_TBL.f1;
53+
54+
SELECT '' AS five, ABSTIME_TBL.*
55+
WHERE 'epoch'::abstime >= ABSTIME_TBL.f1;
56+
57+
SELECT '' AS six, ABSTIME_TBL.*
58+
WHERE ABSTIME_TBL.f1 <= 'Jan 14, 1973 03:14:21'::abstime;
59+
60+
SELECT '' AS six, ABSTIME_TBL.*
61+
WHERE ABSTIME_TBL.f1 <?>
62+
'["Apr 1 1945 00:00:00" "Dec 30 1999 23:00:00"]'::tinterval;
63+
64+
-- these four queries should return the same answer
65+
-- the "infinity" and "-infinity" tuples in ABSTIME_TBL cannot be added and
66+
-- therefore, should not show up in the results.
67+
SELECT '' AS five, ABSTIME_TBL.*
68+
WHERE (ABSTIME_TBL.f1 + '@ 3 year'::reltime) -- +3 years
69+
< 'Jan 14 14:00:00 1977'::abstime;
70+
71+
SELECT '' AS five, ABSTIME_TBL.*
72+
WHERE (ABSTIME_TBL.f1 + '@ 3 year ago'::reltime) -- -3 years
73+
< 'Jan 14 14:00:00 1971'::abstime;
74+
75+
SELECT '' AS five, ABSTIME_TBL.*
76+
WHERE (ABSTIME_TBL.f1 - '@ 3 year'::reltime) -- -(+3) years
77+
< 'Jan 14 14:00:00 1971'::abstime;
78+
79+
SELECT '' AS five, ABSTIME_TBL.*
80+
WHERE (ABSTIME_TBL.f1 - '@ 3 year ago'::reltime) -- -(-3) years
81+
< 'Jan 14 14:00:00 1977'::abstime;
82+
83+
84+
SELECT '' AS twenty, ABSTIME_TBL.*, RELTIME_TBL.*
85+
WHERE (ABSTIME_TBL.f1 + RELTIME_TBL.f1)
86+
< 'Jan 14 14:00:00 1971'::abstime;
87+

‎src/test/regress/sql/boolean.sql

Lines changed: 95 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,95 @@
1+
--
2+
-- boolean.source
3+
--
4+
-- $Header: /cvsroot/pgsql/src/test/regress/sql/boolean.sql,v 1.1 1997/04/05 11:08:15 scrappy Exp $
5+
--
6+
7+
--
8+
-- sanity check - if this fails go insane!
9+
--
10+
SELECT 1 AS one;
11+
12+
13+
-- ******************testing built-in type bool********************
14+
15+
-- check bool type-casting as well as and, or, not in qualifications--
16+
17+
SELECT 't'::bool AS true;
18+
19+
SELECT 'f'::bool AS false;
20+
21+
SELECT 't'::bool or 'f'::bool AS true;
22+
23+
SELECT 't'::bool and 'f'::bool AS false;
24+
25+
SELECT not 'f'::bool AS true;
26+
27+
SELECT 't'::bool = 'f'::bool AS false;
28+
29+
SELECT 't'::bool <> 'f'::bool AS true;
30+
31+
32+
CREATE TABLE BOOLTBL1 (f1 bool);
33+
34+
INSERT INTO BOOLTBL1 (f1) VALUES ('t'::bool);
35+
36+
INSERT INTO BOOLTBL1 (f1) VALUES ('True'::bool);
37+
38+
INSERT INTO BOOLTBL1 (f1) VALUES ('true'::bool);
39+
40+
41+
-- BOOLTBL1 should be full of true's at this point
42+
SELECT '' AS t_3, BOOLTBL1.*;
43+
44+
45+
SELECT '' AS t_3, BOOLTBL1.*
46+
FROM BOOLTBL1
47+
WHERE f1 = 'true'::bool;
48+
49+
50+
SELECT '' AS t_3, BOOLTBL1.*
51+
FROM BOOLTBL1
52+
WHERE f1 <> 'false'::bool;
53+
54+
SELECT '' AS zero, BOOLTBL1.*
55+
FROM BOOLTBL1
56+
WHERE booleq('false'::bool, f1);
57+
58+
INSERT INTO BOOLTBL1 (f1) VALUES ('f'::bool);
59+
60+
SELECT '' AS f_1, BOOLTBL1.*
61+
FROM BOOLTBL1
62+
WHERE f1 = 'false'::bool;
63+
64+
65+
CREATE TABLE BOOLTBL2 (f1 bool);
66+
67+
INSERT INTO BOOLTBL2 (f1) VALUES ('f'::bool);
68+
69+
INSERT INTO BOOLTBL2 (f1) VALUES ('false'::bool);
70+
71+
INSERT INTO BOOLTBL2 (f1) VALUES ('False'::bool);
72+
73+
-- this evaluates to a false value
74+
INSERT INTO BOOLTBL2 (f1)
75+
VALUES ('XXX'::bool);
76+
77+
78+
-- BOOLTBL2 should be full of false's at this point
79+
SELECT '' AS f_4, BOOLTBL2.*;
80+
81+
82+
SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.*
83+
WHERE BOOLTBL2.f1 <> BOOLTBL1.f1;
84+
85+
86+
SELECT '' AS tf_12, BOOLTBL1.*, BOOLTBL2.*
87+
WHERE boolne(BOOLTBL2.f1,BOOLTBL1.f1);
88+
89+
90+
SELECT '' AS ff_4, BOOLTBL1.*, BOOLTBL2.*
91+
WHERE BOOLTBL2.f1 = BOOLTBL1.f1 and BOOLTBL1.f1 = 'false'::bool;
92+
93+
94+
SELECT '' AS tf_12_ff_4, BOOLTBL1.*, BOOLTBL2.*
95+
WHERE BOOLTBL2.f1 = BOOLTBL1.f1 or BOOLTBL1.f1 = 'true'::bool;

‎src/test/regress/sql/box.sql

Lines changed: 117 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,117 @@
1+
-- ****************** test built-in type box ********************
2+
3+
--
4+
-- box logic
5+
-- o
6+
-- 3 o--|X
7+
-- | o|
8+
-- 2 +-+-+ |
9+
-- | | | |
10+
-- 1 | o-+-o
11+
-- | |
12+
-- 0 +---+
13+
--
14+
-- 0 1 2 3
15+
--
16+
17+
-- boxes are specified by two points, given by four floats x1,y1,x2,y2
18+
19+
20+
CREATE TABLE BOX_TBL (f1 box);
21+
22+
INSERT INTO BOX_TBL (f1) VALUES ('(2.0,2.0,0.0,0.0)');
23+
24+
INSERT INTO BOX_TBL (f1) VALUES ('(1.0,1.0,3.0,3.0)');
25+
26+
-- degenerate cases where the box is a line or a point
27+
-- note that lines and points boxes all have zero area
28+
INSERT INTO BOX_TBL (f1) VALUES ('(2.5, 2.5, 2.5,3.5)');
29+
30+
INSERT INTO BOX_TBL (f1) VALUES ('(3.0, 3.0,3.0,3.0)');
31+
32+
-- badly formatted box inputs
33+
INSERT INTO BOX_TBL (f1) VALUES ('(2.3, 4.5)');
34+
35+
INSERT INTO BOX_TBL (f1) VALUES ('asdfasdf(ad');
36+
37+
38+
SELECT '' AS four, BOX_TBL.*;
39+
40+
SELECT '' AS four, b.*, box_area(b.f1) as barea
41+
FROM BOX_TBL b;
42+
43+
-- overlap
44+
SELECT '' AS three, b.f1
45+
FROM BOX_TBL b
46+
WHERE b.f1 && '(2.5,2.5,1.0,1.0)'::box;
47+
48+
-- left-or-overlap (x only)
49+
SELECT '' AS two, b1.*
50+
FROM BOX_TBL b1
51+
WHERE b1.f1 &< '(2.0,2.0,2.5,2.5)'::box;
52+
53+
-- right-or-overlap (x only)
54+
SELECT '' AS two, b1.*
55+
FROM BOX_TBL b1
56+
WHERE b1.f1 &> '(2.0,2.0,2.5,2.5)'::box;
57+
58+
-- left of
59+
SELECT '' AS two, b.f1
60+
FROM BOX_TBL b
61+
WHERE b.f1 << '(3.0,3.0,5.0,5.0)'::box;
62+
63+
-- area <=
64+
SELECT '' AS four, b.f1
65+
FROM BOX_TBL b
66+
WHERE b.f1 <= '(3.0,3.0,5.0,5.0)'::box;
67+
68+
-- area <
69+
SELECT '' AS two, b.f1
70+
FROM BOX_TBL b
71+
WHERE b.f1 < '(3.0,3.0,5.0,5.0)'::box;
72+
73+
-- area =
74+
SELECT '' AS two, b.f1
75+
FROM BOX_TBL b
76+
WHERE b.f1 = '(3.0,3.0,5.0,5.0)'::box;
77+
78+
-- area >
79+
SELECT '' AS two, b.f1
80+
FROM BOX_TBL b -- zero area
81+
WHERE b.f1 > '(3.5,3.0,4.5,3.0)'::box;
82+
83+
-- area >=
84+
SELECT '' AS four, b.f1
85+
FROM BOX_TBL b -- zero area
86+
WHERE b.f1 >= '(3.5,3.0,4.5,3.0)'::box;
87+
88+
-- right of
89+
SELECT '' AS two, b.f1
90+
FROM BOX_TBL b
91+
WHERE '(3.0,3.0,5.0,5.0)'::box >> b.f1;
92+
93+
-- contained in
94+
SELECT '' AS three, b.f1
95+
FROM BOX_TBL b
96+
WHERE b.f1 @ '(0,0,3,3)'::box;
97+
98+
-- contains
99+
SELECT '' AS three, b.f1
100+
FROM BOX_TBL b
101+
WHERE '(0,0,3,3)'::box ~ b.f1;
102+
103+
-- box equality
104+
SELECT '' AS one, b.f1
105+
FROM BOX_TBL b
106+
WHERE '(1,1,3,3)'::box ~= b.f1;
107+
108+
-- center of box, left unary operator
109+
SELECT '' AS four, @@(b1.f1) AS p
110+
FROM BOX_TBL b1;
111+
112+
-- wholly-contained
113+
SELECT '' AS one, b1.*, b2.*
114+
FROM BOX_TBL b1, BOX_TBL b2
115+
WHERE b1.f1 ~ b2.f1 and not b1.f1 ~= b2.f1;
116+
117+

‎src/test/regress/sql/char.sql

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
-- ****************** test built-in type char **************
2+
--
3+
-- all inputs are SILENTLY truncated at 1 character
4+
--
5+
6+
CREATE TABLE CHAR_TBL(f1 char);
7+
8+
INSERT INTO CHAR_TBL (f1) VALUES ('a');
9+
10+
INSERT INTO CHAR_TBL (f1) VALUES ('A');
11+
12+
-- any of the following three input formats are acceptable
13+
INSERT INTO CHAR_TBL (f1) VALUES ('1');
14+
15+
INSERT INTO CHAR_TBL (f1) VALUES (2);
16+
17+
INSERT INTO CHAR_TBL (f1) VALUES ('3');
18+
19+
-- zero-length char
20+
INSERT INTO CHAR_TBL (f1) VALUES ('');
21+
22+
-- try char's of greater than 1 length
23+
INSERT INTO CHAR_TBL (f1) VALUES ('cd');
24+
25+
26+
SELECT '' AS seven, CHAR_TBL.*;
27+
28+
SELECT '' AS six, c.*
29+
FROM CHAR_TBL c
30+
WHERE c.f1 <> 'a';
31+
32+
SELECT '' AS one, c.*
33+
FROM CHAR_TBL c
34+
WHERE c.f1 = 'a';
35+
36+
SELECT '' AS five, c.*
37+
FROM CHAR_TBL c
38+
WHERE c.f1 < 'a';
39+
40+
SELECT '' AS six, c.*
41+
FROM CHAR_TBL c
42+
WHERE c.f1 <= 'a';
43+
44+
SELECT '' AS one, c.*
45+
FROM CHAR_TBL c
46+
WHERE c.f1 > 'a';
47+
48+
SELECT '' AS two, c.*
49+
FROM CHAR_TBL c
50+
WHERE c.f1 >= 'a';
51+
52+

0 commit comments

Comments
 (0)