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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
|
--
-- NUMEROLOGY
-- Test various combinations of numeric types and functions.
--
--
-- numeric literals
--
SELECT 0b100101;
SELECT 0o273;
SELECT 0x42F;
-- cases near int4 overflow
SELECT 0b1111111111111111111111111111111;
SELECT 0b10000000000000000000000000000000;
SELECT 0o17777777777;
SELECT 0o20000000000;
SELECT 0x7FFFFFFF;
SELECT 0x80000000;
SELECT -0b10000000000000000000000000000000;
SELECT -0b10000000000000000000000000000001;
SELECT -0o20000000000;
SELECT -0o20000000001;
SELECT -0x80000000;
SELECT -0x80000001;
-- cases near int8 overflow
SELECT 0b111111111111111111111111111111111111111111111111111111111111111;
SELECT 0b1000000000000000000000000000000000000000000000000000000000000000;
SELECT 0o777777777777777777777;
SELECT 0o1000000000000000000000;
SELECT 0x7FFFFFFFFFFFFFFF;
SELECT 0x8000000000000000;
SELECT -0b1000000000000000000000000000000000000000000000000000000000000000;
SELECT -0b1000000000000000000000000000000000000000000000000000000000000001;
SELECT -0o1000000000000000000000;
SELECT -0o1000000000000000000001;
SELECT -0x8000000000000000;
SELECT -0x8000000000000001;
-- error cases
SELECT 123abc;
SELECT 0x0o;
SELECT 0.a;
SELECT 0.0a;
SELECT .0a;
SELECT 0.0e1a;
SELECT 0.0e;
SELECT 0.0e+a;
PREPARE p1 AS SELECT $1a;
SELECT 0b;
SELECT 1b;
SELECT 0b0x;
SELECT 0o;
SELECT 1o;
SELECT 0o0x;
SELECT 0x;
SELECT 1x;
SELECT 0x0y;
-- underscores
SELECT 1_000_000;
SELECT 1_2_3;
SELECT 0x1EEE_FFFF;
SELECT 0o2_73;
SELECT 0b_10_0101;
SELECT 1_000.000_005;
SELECT 1_000.;
SELECT .000_005;
SELECT 1_000.5e0_1;
-- error cases
SELECT _100;
SELECT 100_;
SELECT 100__000;
SELECT _1_000.5;
SELECT 1_000_.5;
SELECT 1_000._5;
SELECT 1_000.5_;
SELECT 1_000.5e_1;
--
-- Test implicit type conversions
-- This fails for Postgres v6.1 (and earlier?)
-- so let's try explicit conversions for now - tgl 97/05/07
--
CREATE TABLE TEMP_FLOAT (f1 FLOAT8);
INSERT INTO TEMP_FLOAT (f1)
SELECT float8(f1) FROM INT4_TBL;
INSERT INTO TEMP_FLOAT (f1)
SELECT float8(f1) FROM INT2_TBL;
SELECT f1 FROM TEMP_FLOAT
ORDER BY f1;
-- int4
CREATE TABLE TEMP_INT4 (f1 INT4);
INSERT INTO TEMP_INT4 (f1)
SELECT int4(f1) FROM FLOAT8_TBL
WHERE (f1 > -2147483647) AND (f1 < 2147483647);
INSERT INTO TEMP_INT4 (f1)
SELECT int4(f1) FROM INT2_TBL;
SELECT f1 FROM TEMP_INT4
ORDER BY f1;
-- int2
CREATE TABLE TEMP_INT2 (f1 INT2);
INSERT INTO TEMP_INT2 (f1)
SELECT int2(f1) FROM FLOAT8_TBL
WHERE (f1 >= -32767) AND (f1 <= 32767);
INSERT INTO TEMP_INT2 (f1)
SELECT int2(f1) FROM INT4_TBL
WHERE (f1 >= -32767) AND (f1 <= 32767);
SELECT f1 FROM TEMP_INT2
ORDER BY f1;
--
-- Group-by combinations
--
CREATE TABLE TEMP_GROUP (f1 INT4, f2 INT4, f3 FLOAT8);
INSERT INTO TEMP_GROUP
SELECT 1, (- i.f1), (- f.f1)
FROM INT4_TBL i, FLOAT8_TBL f;
INSERT INTO TEMP_GROUP
SELECT 2, i.f1, f.f1
FROM INT4_TBL i, FLOAT8_TBL f;
SELECT DISTINCT f1 AS two FROM TEMP_GROUP ORDER BY 1;
SELECT f1 AS two, max(f3) AS max_float, min(f3) as min_float
FROM TEMP_GROUP
GROUP BY f1
ORDER BY two, max_float, min_float;
-- GROUP BY a result column name is not legal per SQL92, but we accept it
-- anyway (if the name is not the name of any column exposed by FROM).
SELECT f1 AS two, max(f3) AS max_float, min(f3) AS min_float
FROM TEMP_GROUP
GROUP BY two
ORDER BY two, max_float, min_float;
SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1
FROM TEMP_GROUP
GROUP BY f1
ORDER BY two, min_minus_1;
SELECT f1 AS two,
max(f2) + min(f2) AS max_plus_min,
min(f3) - 1 AS min_minus_1
FROM TEMP_GROUP
GROUP BY f1
ORDER BY two, min_minus_1;
DROP TABLE TEMP_INT2;
DROP TABLE TEMP_INT4;
DROP TABLE TEMP_FLOAT;
DROP TABLE TEMP_GROUP;
|
