1--
2-- INT2
3--
4
5CREATE TABLE INT2_TBL(f1 int2);
6
7INSERT INTO INT2_TBL(f1) VALUES ('0   ');
8
9INSERT INTO INT2_TBL(f1) VALUES ('  1234 ');
10
11INSERT INTO INT2_TBL(f1) VALUES ('    -1234');
12
13INSERT INTO INT2_TBL(f1) VALUES ('34.5');
14
15-- largest and smallest values
16INSERT INTO INT2_TBL(f1) VALUES ('32767');
17
18INSERT INTO INT2_TBL(f1) VALUES ('-32767');
19
20-- bad input values -- should give errors
21INSERT INTO INT2_TBL(f1) VALUES ('100000');
22INSERT INTO INT2_TBL(f1) VALUES ('asdf');
23INSERT INTO INT2_TBL(f1) VALUES ('    ');
24INSERT INTO INT2_TBL(f1) VALUES ('- 1234');
25INSERT INTO INT2_TBL(f1) VALUES ('4 444');
26INSERT INTO INT2_TBL(f1) VALUES ('123 dt');
27INSERT INTO INT2_TBL(f1) VALUES ('');
28
29
30SELECT * FROM INT2_TBL;
31
32SELECT i.* FROM INT2_TBL i WHERE i.f1 <> int2 '0';
33
34SELECT i.* FROM INT2_TBL i WHERE i.f1 <> int4 '0';
35
36SELECT i.* FROM INT2_TBL i WHERE i.f1 = int2 '0';
37
38SELECT i.* FROM INT2_TBL i WHERE i.f1 = int4 '0';
39
40SELECT i.* FROM INT2_TBL i WHERE i.f1 < int2 '0';
41
42SELECT i.* FROM INT2_TBL i WHERE i.f1 < int4 '0';
43
44SELECT i.* FROM INT2_TBL i WHERE i.f1 <= int2 '0';
45
46SELECT i.* FROM INT2_TBL i WHERE i.f1 <= int4 '0';
47
48SELECT i.* FROM INT2_TBL i WHERE i.f1 > int2 '0';
49
50SELECT i.* FROM INT2_TBL i WHERE i.f1 > int4 '0';
51
52SELECT i.* FROM INT2_TBL i WHERE i.f1 >= int2 '0';
53
54SELECT i.* FROM INT2_TBL i WHERE i.f1 >= int4 '0';
55
56-- positive odds
57SELECT i.* FROM INT2_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
58
59-- any evens
60SELECT i.* FROM INT2_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
61
62SELECT i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i;
63
64SELECT i.f1, i.f1 * int2 '2' AS x FROM INT2_TBL i
65WHERE abs(f1) < 16384;
66
67SELECT i.f1, i.f1 * int4 '2' AS x FROM INT2_TBL i;
68
69SELECT i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i;
70
71SELECT i.f1, i.f1 + int2 '2' AS x FROM INT2_TBL i
72WHERE f1 < 32766;
73
74SELECT i.f1, i.f1 + int4 '2' AS x FROM INT2_TBL i;
75
76SELECT i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i;
77
78SELECT i.f1, i.f1 - int2 '2' AS x FROM INT2_TBL i
79WHERE f1 > -32767;
80
81SELECT i.f1, i.f1 - int4 '2' AS x FROM INT2_TBL i;
82
83SELECT i.f1, i.f1 / int2 '2' AS x FROM INT2_TBL i;
84
85SELECT i.f1, i.f1 / int4 '2' AS x FROM INT2_TBL i;
86
87-- corner cases
88SELECT (-1::int2<<15)::text;
89SELECT ((-1::int2<<15)+1::int2)::text;
90
91-- check sane handling of INT16_MIN overflow cases
92SELECT (-32768)::int2 * (-1)::int2;
93SELECT (-32768)::int2 / (-1)::int2;
94SELECT (-32768)::int2 % (-1)::int2;
95
96-- check rounding when casting from float
97SELECT x, x::int2 AS int2_value
98FROM (VALUES (-2.5::float8),
99             (-1.5::float8),
100             (-0.5::float8),
101             (0.0::float8),
102             (0.5::float8),
103             (1.5::float8),
104             (2.5::float8)) t(x);
105
106-- check rounding when casting from numeric
107SELECT x, x::int2 AS int2_value
108FROM (VALUES (-2.5::numeric),
109             (-1.5::numeric),
110             (-0.5::numeric),
111             (0.0::numeric),
112             (0.5::numeric),
113             (1.5::numeric),
114             (2.5::numeric)) t(x);
115