Skip to content

Commit 04688df

Browse files
scrappyscrappy
committed
Again, add more tests
1 parent 7956e60 commit 04688df

File tree

15 files changed

+2040
-2
lines changed

15 files changed

+2040
-2
lines changed

‎src/test/regress/sql/char16.sql

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
--**************** testing built-in type char16 **************
2+
--
3+
-- all inputs are silently truncated at 16 characters
4+
--
5+
6+
CREATE TABLE CHAR16_TBL(f1 char16);
7+
8+
INSERT INTO CHAR16_TBL(f1) VALUES ('ABCDEFGHIJKLMNOP');
9+
10+
INSERT INTO CHAR16_TBL(f1) VALUES ('abcdefghijklmnop');
11+
12+
INSERT INTO CHAR16_TBL(f1) VALUES ('asdfghjkl;');
13+
14+
INSERT INTO CHAR16_TBL(f1) VALUES ('343f%2a');
15+
16+
INSERT INTO CHAR16_TBL(f1) VALUES ('d34aaasdf');
17+
18+
INSERT INTO CHAR16_TBL(f1) VALUES ('');
19+
20+
INSERT INTO CHAR16_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUV');
21+
22+
23+
SELECT '' AS seven, CHAR16_TBL.*;
24+
25+
SELECT '' AS six, c.f1 FROM CHAR16_TBL c WHERE c.f1 <> 'ABCDEFGHIJKLMNOP';
26+
27+
SELECT '' AS one, c.f1 FROM CHAR16_TBL c WHERE c.f1 = 'ABCDEFGHIJKLMNOP';
28+
29+
SELECT '' AS three, c.f1 FROM CHAR16_TBL c WHERE c.f1 < 'ABCDEFGHIJKLMNOP';
30+
31+
SELECT '' AS four, c.f1 FROM CHAR16_TBL c WHERE c.f1 <= 'ABCDEFGHIJKLMNOP';
32+
33+
SELECT '' AS three, c.f1 FROM CHAR16_TBL c WHERE c.f1 > 'ABCDEFGHIJKLMNOP';
34+
35+
SELECT '' AS four, c.f1 FROM CHAR16_TBL c WHERE c.f1 >= 'ABCDEFGHIJKLMNOP';
36+
37+
SELECT '' AS seven, c.f1 FROM CHAR16_TBL c WHERE c.f1 ~ '.*';
38+
39+
SELECT '' AS zero, c.f1 FROM CHAR16_TBL c WHERE c.f1 !~ '.*';
40+
41+
SELECT '' AS three, c.f1 FROM CHAR16_TBL c WHERE c.f1 ~ '[0-9]';
42+
43+
SELECT '' AS two, c.f1 FROM CHAR16_TBL c WHERE c.f1 ~ '.*asdf.*';
44+
45+

‎src/test/regress/sql/char8.sql

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,46 @@
1+
-- **************** testing built-in type char8 **************
2+
--
3+
-- all inputs are silently truncated at 8 characters
4+
--
5+
6+
CREATE TABLE CHAR8_TBL(f1 char8);
7+
8+
INSERT INTO CHAR8_TBL(f1) VALUES ('ABCDEFGH');
9+
10+
INSERT INTO CHAR8_TBL(f1) VALUES ('abcdefgh');
11+
12+
INSERT INTO CHAR8_TBL(f1) VALUES ('ZYWZ410-');
13+
14+
INSERT INTO CHAR8_TBL(f1) VALUES ('343f%2a');
15+
16+
INSERT INTO CHAR8_TBL(f1) VALUES ('d34aas');
17+
18+
INSERT INTO CHAR8_TBL(f1) VALUES ('');
19+
20+
INSERT INTO CHAR8_TBL(f1) VALUES ('1234567890');
21+
22+
23+
SELECT '' AS seven, CHAR8_TBL.*;
24+
25+
SELECT '' AS six, c.f1 FROM CHAR8_TBL c WHERE c.f1 <> 'ABCDEFGH';
26+
27+
SELECT '' AS one, c.f1 FROM CHAR8_TBL c WHERE c.f1 = 'ABCDEFGH';
28+
29+
SELECT '' AS three, c.f1 FROM CHAR8_TBL c WHERE c.f1 < 'ABCDEFGH';
30+
31+
SELECT '' AS four, c.f1 FROM CHAR8_TBL c WHERE c.f1 <= 'ABCDEFGH';
32+
33+
SELECT '' AS three, c.f1 FROM CHAR8_TBL c WHERE c.f1 > 'ABCDEFGH';
34+
35+
SELECT '' AS four, c.f1 FROM CHAR8_TBL c WHERE c.f1 >= 'ABCDEFGH';
36+
37+
SELECT '' AS seven, c.f1 FROM CHAR8_TBL c WHERE c.f1 ~ '.*';
38+
39+
SELECT '' AS zero, c.f1 FROM CHAR8_TBL c WHERE c.f1 !~ '.*';
40+
41+
SELECT '' AS four, c.f1 FROM CHAR8_TBL c WHERE c.f1 ~ '[0-9]';
42+
43+
SELECT '' AS three, c.f1 FROM CHAR8_TBL c WHERE c.f1 ~ '.*34.*';
44+
45+
46+

‎src/test/regress/sql/float4.sql

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,65 @@
1+
-- *************testing built-in type float4 ****************
2+
3+
CREATE TABLE FLOAT4_TBL (f1 float4);
4+
5+
INSERT INTO FLOAT4_TBL(f1) VALUES ('0.0');
6+
7+
INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30');
8+
9+
INSERT INTO FLOAT4_TBL(f1) VALUES ('-34.84');
10+
11+
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
12+
13+
INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
14+
15+
-- test for over and under flow
16+
INSERT INTO FLOAT4_TBL(f1) VALUES ('10e40');
17+
18+
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e40');
19+
20+
INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-40');
21+
22+
INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-40');
23+
24+
25+
SELECT '' AS five, FLOAT4_TBL.*;
26+
27+
SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3';
28+
29+
SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3';
30+
31+
SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1;
32+
33+
SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE f.f1 < '1004.3';
34+
35+
SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1;
36+
37+
SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <= '1004.3';
38+
39+
SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f
40+
WHERE f.f1 > '0.0';
41+
42+
SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f
43+
WHERE f.f1 > '0.0';
44+
45+
SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f
46+
WHERE f.f1 > '0.0';
47+
48+
SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f
49+
WHERE f.f1 > '0.0';
50+
51+
-- test divide by zero
52+
SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f;
53+
54+
SELECT '' AS five, FLOAT4_TBL.*;
55+
56+
-- test the unary float4abs operator
57+
SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f;
58+
59+
UPDATE FLOAT4_TBL
60+
SET f1 = FLOAT4_TBL.f1 * '-1'
61+
WHERE FLOAT4_TBL.f1 > '0.0';
62+
63+
SELECT '' AS five, FLOAT4_TBL.*;
64+
65+

‎src/test/regress/sql/float8.sql

Lines changed: 104 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,104 @@
1+
-- *************testing built-in type float8 ****************
2+
3+
CREATE TABLE FLOAT8_TBL(f1 float8);
4+
5+
INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
6+
7+
INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30');
8+
9+
INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
10+
11+
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
12+
13+
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
14+
15+
-- test for over and under flow
16+
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
17+
18+
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
19+
20+
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
21+
22+
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
23+
24+
25+
SELECT '' AS five, FLOAT8_TBL.*;
26+
27+
SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3';
28+
29+
SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3';
30+
31+
SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1;
32+
33+
SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE f.f1 < '1004.3';
34+
35+
SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1;
36+
37+
SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3';
38+
39+
SELECT '' AS three, f.f1, f.f1 * '-10' AS x
40+
FROM FLOAT8_TBL f
41+
WHERE f.f1 > '0.0';
42+
43+
SELECT '' AS three, f.f1, f.f1 + '-10' AS x
44+
FROM FLOAT8_TBL f
45+
WHERE f.f1 > '0.0';
46+
47+
SELECT '' AS three, f.f1, f.f1 / '-10' AS x
48+
FROM FLOAT8_TBL f
49+
WHERE f.f1 > '0.0';
50+
51+
SELECT '' AS three, f.f1, f.f1 - '-10' AS x
52+
FROM FLOAT8_TBL f
53+
WHERE f.f1 > '0.0';
54+
55+
SELECT '' AS one, f.f1 ^ '2.0' AS square_f1
56+
FROM FLOAT8_TBL f where f.f1 = '1004.3';
57+
58+
-- absolute value
59+
SELECT '' AS five, f.f1, @f.f1 AS abs_f1
60+
FROM FLOAT8_TBL f;
61+
62+
-- truncate
63+
SELECT '' AS five, f.f1, %f.f1 AS trunc_f1
64+
FROM FLOAT8_TBL f;
65+
66+
-- round
67+
SELECT '' AS five, f.f1, f.f1 % AS round_f1
68+
FROM FLOAT8_TBL f;
69+
70+
-- square root
71+
SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1
72+
FROM FLOAT8_TBL f
73+
WHERE f.f1 > '0.0';
74+
75+
-- take exp of ln(f.f1)
76+
SELECT '' AS three, f.f1, : ( ; f.f1) AS exp_ln_f1
77+
FROM FLOAT8_TBL f
78+
WHERE f.f1 > '0.0';
79+
80+
-- cube root
81+
SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f;
82+
83+
84+
SELECT '' AS five, FLOAT8_TBL.*;
85+
86+
UPDATE FLOAT8_TBL
87+
SET f1 = FLOAT8_TBL.f1 * '-1'
88+
WHERE FLOAT8_TBL.f1 > '0.0';
89+
90+
SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f;
91+
92+
SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f;
93+
94+
SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 = '0.0' ;
95+
96+
SELECT '' AS bad, (; (f.f1)) from FLOAT8_TBL f where f.f1 < '0.0' ;
97+
98+
SELECT '' AS bad, : (f.f1) from FLOAT8_TBL f;
99+
100+
SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f;
101+
102+
SELECT '' AS five, FLOAT8_TBL.*;
103+
104+

‎src/test/regress/sql/int2.sql

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,77 @@
1+
-- *************testing built-in type int2 ****************
2+
--
3+
-- NOTE: int2 operators never check for over/underflow!
4+
-- Some of these answers are consequently numerically incorrect.
5+
--
6+
7+
CREATE TABLE INT2_TBL(f1 int2);
8+
9+
INSERT INTO INT2_TBL(f1) VALUES ('0');
10+
11+
INSERT INTO INT2_TBL(f1) VALUES ('1234');
12+
13+
INSERT INTO INT2_TBL(f1) VALUES ('-1234');
14+
15+
INSERT INTO INT2_TBL(f1) VALUES ('34.5');
16+
17+
-- largest and smallest values
18+
INSERT INTO INT2_TBL(f1) VALUES ('32767');
19+
20+
INSERT INTO INT2_TBL(f1) VALUES ('-32767');
21+
22+
-- bad input values -- should give warnings
23+
INSERT INTO INT2_TBL(f1) VALUES ('100000');
24+
25+
INSERT INTO INT2_TBL(f1) VALUES ('asdf');
26+
27+
28+
SELECT '' AS five, INT2_TBL.*;
29+
30+
SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> '0'::int2;
31+
32+
SELECT '' AS four, i.* FROM INT2_TBL i WHERE i.f1 <> '0'::int4;
33+
34+
SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = '0'::int2;
35+
36+
SELECT '' AS one, i.* FROM INT2_TBL i WHERE i.f1 = '0'::int4;
37+
38+
SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < '0'::int2;
39+
40+
SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 < '0'::int4;
41+
42+
SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= '0'::int2;
43+
44+
SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 <= '0'::int4;
45+
46+
SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > '0'::int2;
47+
48+
SELECT '' AS two, i.* FROM INT2_TBL i WHERE i.f1 > '0'::int4;
49+
50+
SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= '0'::int2;
51+
52+
SELECT '' AS three, i.* FROM INT2_TBL i WHERE i.f1 >= '0'::int4;
53+
54+
-- positive odds
55+
SELECT '' AS one, i.* FROM INT2_TBL i WHERE (i.f1 % '2'::int2) = '1'::int2;
56+
57+
-- any evens
58+
SELECT '' AS three, i.* FROM INT2_TBL i WHERE (i.f1 % '2'::int4) = '0'::int2;
59+
60+
SELECT '' AS five, i.f1, i.f1 * '2'::int2 AS x FROM INT2_TBL i;
61+
62+
SELECT '' AS five, i.f1, i.f1 * '2'::int4 AS x FROM INT2_TBL i;
63+
64+
SELECT '' AS five, i.f1, i.f1 + '2'::int2 AS x FROM INT2_TBL i;
65+
66+
SELECT '' AS five, i.f1, i.f1 + '2'::int4 AS x FROM INT2_TBL i;
67+
68+
SELECT '' AS five, i.f1, i.f1 - '2'::int2 AS x FROM INT2_TBL i;
69+
70+
SELECT '' AS five, i.f1, i.f1 - '2'::int4 AS x FROM INT2_TBL i;
71+
72+
SELECT '' AS five, i.f1, i.f1 / '2'::int2 AS x FROM INT2_TBL i;
73+
74+
SELECT '' AS five, i.f1, i.f1 / '2'::int4 AS x FROM INT2_TBL i;
75+
76+
77+

0 commit comments

Comments
 (0)