-
Notifications
You must be signed in to change notification settings - Fork 4.4k
/
int4.sql
157 lines (101 loc) · 4.13 KB
/
int4.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
--
-- INT4
--
CREATE TABLE INT4_TBL(f1 int4);
INSERT INTO INT4_TBL(f1) VALUES (' 0 ');
INSERT INTO INT4_TBL(f1) VALUES ('123456 ');
INSERT INTO INT4_TBL(f1) VALUES (' -123456');
INSERT INTO INT4_TBL(f1) VALUES ('34.5');
-- largest and smallest values
INSERT INTO INT4_TBL(f1) VALUES ('2147483647');
INSERT INTO INT4_TBL(f1) VALUES ('-2147483647');
-- bad input values -- should give errors
INSERT INTO INT4_TBL(f1) VALUES ('1000000000000');
INSERT INTO INT4_TBL(f1) VALUES ('asdf');
INSERT INTO INT4_TBL(f1) VALUES (' ');
INSERT INTO INT4_TBL(f1) VALUES (' asdf ');
INSERT INTO INT4_TBL(f1) VALUES ('- 1234');
INSERT INTO INT4_TBL(f1) VALUES ('123 5');
INSERT INTO INT4_TBL(f1) VALUES ('');
SELECT '' AS five, * FROM INT4_TBL;
SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int2 '0';
SELECT '' AS four, i.* FROM INT4_TBL i WHERE i.f1 <> int4 '0';
SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int2 '0';
SELECT '' AS one, i.* FROM INT4_TBL i WHERE i.f1 = int4 '0';
SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int2 '0';
SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 < int4 '0';
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int2 '0';
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 <= int4 '0';
SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int2 '0';
SELECT '' AS two, i.* FROM INT4_TBL i WHERE i.f1 > int4 '0';
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int2 '0';
SELECT '' AS three, i.* FROM INT4_TBL i WHERE i.f1 >= int4 '0';
-- positive odds
SELECT '' AS one, i.* FROM INT4_TBL i WHERE (i.f1 % int2 '2') = int2 '1';
-- any evens
SELECT '' AS three, i.* FROM INT4_TBL i WHERE (i.f1 % int4 '2') = int2 '0';
SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i;
SELECT '' AS five, i.f1, i.f1 * int2 '2' AS x FROM INT4_TBL i
WHERE abs(f1) < 1073741824;
SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i;
SELECT '' AS five, i.f1, i.f1 * int4 '2' AS x FROM INT4_TBL i
WHERE abs(f1) < 1073741824;
SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i;
SELECT '' AS five, i.f1, i.f1 + int2 '2' AS x FROM INT4_TBL i
WHERE f1 < 2147483646;
SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i;
SELECT '' AS five, i.f1, i.f1 + int4 '2' AS x FROM INT4_TBL i
WHERE f1 < 2147483646;
SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i;
SELECT '' AS five, i.f1, i.f1 - int2 '2' AS x FROM INT4_TBL i
WHERE f1 > -2147483647;
SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i;
SELECT '' AS five, i.f1, i.f1 - int4 '2' AS x FROM INT4_TBL i
WHERE f1 > -2147483647;
SELECT '' AS five, i.f1, i.f1 / int2 '2' AS x FROM INT4_TBL i;
SELECT '' AS five, i.f1, i.f1 / int4 '2' AS x FROM INT4_TBL i;
--
-- more complex expressions
--
-- variations on unary minus parsing
SELECT -2+3 AS one;
SELECT 4-2 AS two;
SELECT 2- -1 AS three;
SELECT 2 - -2 AS four;
SELECT int2 '2' * int2 '2' = int2 '16' / int2 '4' AS true;
SELECT int4 '2' * int2 '2' = int2 '16' / int4 '4' AS true;
SELECT int2 '2' * int4 '2' = int4 '16' / int2 '4' AS true;
SELECT int4 '1000' < int4 '999' AS false;
SELECT 4! AS twenty_four;
SELECT !!3 AS six;
SELECT 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 AS ten;
SELECT 2 + 2 / 2 AS three;
SELECT (2 + 2) / 2 AS two;
-- corner case
SELECT (-1::int4<<31)::text;
SELECT ((-1::int4<<31)+1)::text;
-- check sane handling of INT_MIN overflow cases
SELECT (-2147483648)::int4 * (-1)::int4;
SELECT (-2147483648)::int4 / (-1)::int4;
SELECT (-2147483648)::int4 % (-1)::int4;
SELECT (-2147483648)::int4 * (-1)::int2;
SELECT (-2147483648)::int4 / (-1)::int2;
SELECT (-2147483648)::int4 % (-1)::int2;
-- check rounding when casting from float
SELECT x, x::int4 AS int4_value
FROM (VALUES (-2.5::float8),
(-1.5::float8),
(-0.5::float8),
(0.0::float8),
(0.5::float8),
(1.5::float8),
(2.5::float8)) t(x);
-- check rounding when casting from numeric
SELECT x, x::int4 AS int4_value
FROM (VALUES (-2.5::numeric),
(-1.5::numeric),
(-0.5::numeric),
(0.0::numeric),
(0.5::numeric),
(1.5::numeric),
(2.5::numeric)) t(x);