Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit31ce2fd

Browse files
committed
"Corrects" the int8/float4/float8 tests under win32.
Claudio Natoli
1 parent9be7ea0 commit31ce2fd

File tree

3 files changed

+585
-0
lines changed

3 files changed

+585
-0
lines changed
Lines changed: 297 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,297 @@
1+
--
2+
-- FLOAT8
3+
--
4+
CREATE TABLE FLOAT8_TBL(f1 float8);
5+
INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
6+
INSERT INTO FLOAT8_TBL(f1) VALUES ('1004.30');
7+
INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
8+
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e+200');
9+
INSERT INTO FLOAT8_TBL(f1) VALUES ('1.2345678901234e-200');
10+
SELECT '' AS five, FLOAT8_TBL.*;
11+
five | f1
12+
------+----------------------
13+
| 0
14+
| 1004.3
15+
| -34.84
16+
| 1.2345678901234e+200
17+
| 1.2345678901234e-200
18+
(5 rows)
19+
20+
SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <> '1004.3';
21+
four | f1
22+
------+----------------------
23+
| 0
24+
| -34.84
25+
| 1.2345678901234e+200
26+
| 1.2345678901234e-200
27+
(4 rows)
28+
29+
SELECT '' AS one, f.* FROM FLOAT8_TBL f WHERE f.f1 = '1004.3';
30+
one | f1
31+
-----+--------
32+
| 1004.3
33+
(1 row)
34+
35+
SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE '1004.3' > f.f1;
36+
three | f1
37+
-------+----------------------
38+
| 0
39+
| -34.84
40+
| 1.2345678901234e-200
41+
(3 rows)
42+
43+
SELECT '' AS three, f.* FROM FLOAT8_TBL f WHERE f.f1 < '1004.3';
44+
three | f1
45+
-------+----------------------
46+
| 0
47+
| -34.84
48+
| 1.2345678901234e-200
49+
(3 rows)
50+
51+
SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE '1004.3' >= f.f1;
52+
four | f1
53+
------+----------------------
54+
| 0
55+
| 1004.3
56+
| -34.84
57+
| 1.2345678901234e-200
58+
(4 rows)
59+
60+
SELECT '' AS four, f.* FROM FLOAT8_TBL f WHERE f.f1 <= '1004.3';
61+
four | f1
62+
------+----------------------
63+
| 0
64+
| 1004.3
65+
| -34.84
66+
| 1.2345678901234e-200
67+
(4 rows)
68+
69+
SELECT '' AS three, f.f1, f.f1 * '-10' AS x
70+
FROM FLOAT8_TBL f
71+
WHERE f.f1 > '0.0';
72+
three | f1 | x
73+
-------+----------------------+-----------------------
74+
| 1004.3 | -10043
75+
| 1.2345678901234e+200 | -1.2345678901234e+201
76+
| 1.2345678901234e-200 | -1.2345678901234e-199
77+
(3 rows)
78+
79+
SELECT '' AS three, f.f1, f.f1 + '-10' AS x
80+
FROM FLOAT8_TBL f
81+
WHERE f.f1 > '0.0';
82+
three | f1 | x
83+
-------+----------------------+----------------------
84+
| 1004.3 | 994.3
85+
| 1.2345678901234e+200 | 1.2345678901234e+200
86+
| 1.2345678901234e-200 | -10
87+
(3 rows)
88+
89+
SELECT '' AS three, f.f1, f.f1 / '-10' AS x
90+
FROM FLOAT8_TBL f
91+
WHERE f.f1 > '0.0';
92+
three | f1 | x
93+
-------+----------------------+-----------------------
94+
| 1004.3 | -100.43
95+
| 1.2345678901234e+200 | -1.2345678901234e+199
96+
| 1.2345678901234e-200 | -1.2345678901234e-201
97+
(3 rows)
98+
99+
SELECT '' AS three, f.f1, f.f1 - '-10' AS x
100+
FROM FLOAT8_TBL f
101+
WHERE f.f1 > '0.0';
102+
three | f1 | x
103+
-------+----------------------+----------------------
104+
| 1004.3 | 1014.3
105+
| 1.2345678901234e+200 | 1.2345678901234e+200
106+
| 1.2345678901234e-200 | 10
107+
(3 rows)
108+
109+
SELECT '' AS one, f.f1 ^ '2.0' AS square_f1
110+
FROM FLOAT8_TBL f where f.f1 = '1004.3';
111+
one | square_f1
112+
-----+------------
113+
| 1008618.49
114+
(1 row)
115+
116+
-- absolute value
117+
SELECT '' AS five, f.f1, @f.f1 AS abs_f1
118+
FROM FLOAT8_TBL f;
119+
five | f1 | abs_f1
120+
------+----------------------+----------------------
121+
| 0 | 0
122+
| 1004.3 | 1004.3
123+
| -34.84 | 34.84
124+
| 1.2345678901234e+200 | 1.2345678901234e+200
125+
| 1.2345678901234e-200 | 1.2345678901234e-200
126+
(5 rows)
127+
128+
-- truncate
129+
SELECT '' AS five, f.f1, %f.f1 AS trunc_f1
130+
FROM FLOAT8_TBL f;
131+
five | f1 | trunc_f1
132+
------+----------------------+----------------------
133+
| 0 | 0
134+
| 1004.3 | 1004
135+
| -34.84 | -34
136+
| 1.2345678901234e+200 | 1.2345678901234e+200
137+
| 1.2345678901234e-200 | 0
138+
(5 rows)
139+
140+
-- round
141+
SELECT '' AS five, f.f1, f.f1 % AS round_f1
142+
FROM FLOAT8_TBL f;
143+
five | f1 | round_f1
144+
------+----------------------+----------------------
145+
| 0 | 0
146+
| 1004.3 | 1004
147+
| -34.84 | -35
148+
| 1.2345678901234e+200 | 1.2345678901234e+200
149+
| 1.2345678901234e-200 | 0
150+
(5 rows)
151+
152+
-- ceil
153+
select ceil(f1) as ceil_f1 from float8_tbl f;
154+
ceil_f1
155+
----------------------
156+
0
157+
1005
158+
-34
159+
1.2345678901234e+200
160+
1
161+
(5 rows)
162+
163+
-- floor
164+
select floor(f1) as floor_f1 from float8_tbl f;
165+
floor_f1
166+
----------------------
167+
0
168+
1004
169+
-35
170+
1.2345678901234e+200
171+
0
172+
(5 rows)
173+
174+
-- sign
175+
select sign(f1) as sign_f1 from float8_tbl f;
176+
sign_f1
177+
---------
178+
0
179+
1
180+
-1
181+
1
182+
1
183+
(5 rows)
184+
185+
-- square root
186+
SELECT sqrt(float8 '64') AS eight;
187+
eight
188+
-------
189+
8
190+
(1 row)
191+
192+
SELECT |/ float8 '64' AS eight;
193+
eight
194+
-------
195+
8
196+
(1 row)
197+
198+
SELECT '' AS three, f.f1, |/f.f1 AS sqrt_f1
199+
FROM FLOAT8_TBL f
200+
WHERE f.f1 > '0.0';
201+
three | f1 | sqrt_f1
202+
-------+----------------------+-----------------------
203+
| 1004.3 | 31.6906926399535
204+
| 1.2345678901234e+200 | 1.11111110611109e+100
205+
| 1.2345678901234e-200 | 1.11111110611109e-100
206+
(3 rows)
207+
208+
-- take exp of ln(f.f1)
209+
SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1
210+
FROM FLOAT8_TBL f
211+
WHERE f.f1 > '0.0';
212+
three | f1 | exp_ln_f1
213+
-------+----------------------+-----------------------
214+
| 1004.3 | 1004.3
215+
| 1.2345678901234e+200 | 1.23456789012338e+200
216+
| 1.2345678901234e-200 | 1.23456789012339e-200
217+
(3 rows)
218+
219+
-- cube root
220+
SELECT ||/ float8 '27' AS three;
221+
three
222+
-------
223+
3
224+
(1 row)
225+
226+
SELECT '' AS five, f.f1, ||/f.f1 AS cbrt_f1 FROM FLOAT8_TBL f;
227+
five | f1 | cbrt_f1
228+
------+----------------------+-----------------------
229+
| 0 | 0
230+
| 1004.3 | 10.014312837827
231+
| -34.84 | -3.26607421344208
232+
| 1.2345678901234e+200 | 4.97933859234765e+066
233+
| 1.2345678901234e-200 | 2.3112042409018e-067
234+
(5 rows)
235+
236+
SELECT '' AS five, FLOAT8_TBL.*;
237+
five | f1
238+
------+----------------------
239+
| 0
240+
| 1004.3
241+
| -34.84
242+
| 1.2345678901234e+200
243+
| 1.2345678901234e-200
244+
(5 rows)
245+
246+
UPDATE FLOAT8_TBL
247+
SET f1 = FLOAT8_TBL.f1 * '-1'
248+
WHERE FLOAT8_TBL.f1 > '0.0';
249+
SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f;
250+
ERROR: type "double precision" value out of range: overflow
251+
SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f;
252+
ERROR: result is out of range
253+
SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 = '0.0' ;
254+
ERROR: cannot take logarithm of zero
255+
SELECT '' AS bad, ln(f.f1) from FLOAT8_TBL f where f.f1 < '0.0' ;
256+
ERROR: cannot take logarithm of a negative number
257+
SELECT '' AS bad, exp(f.f1) from FLOAT8_TBL f;
258+
ERROR: result is out of range
259+
SELECT '' AS bad, f.f1 / '0.0' from FLOAT8_TBL f;
260+
ERROR: division by zero
261+
SELECT '' AS five, FLOAT8_TBL.*;
262+
five | f1
263+
------+-----------------------
264+
| 0
265+
| -34.84
266+
| -1004.3
267+
| -1.2345678901234e+200
268+
| -1.2345678901234e-200
269+
(5 rows)
270+
271+
-- test for over- and underflow
272+
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e400');
273+
ERROR: "10e400" is out of range for type double precision
274+
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e400');
275+
ERROR: "-10e400" is out of range for type double precision
276+
INSERT INTO FLOAT8_TBL(f1) VALUES ('10e-400');
277+
ERROR: "10e-400" is out of range for type double precision
278+
INSERT INTO FLOAT8_TBL(f1) VALUES ('-10e-400');
279+
ERROR: "-10e-400" is out of range for type double precision
280+
-- maintain external table consistency across platforms
281+
-- delete all values and reinsert well-behaved ones
282+
DELETE FROM FLOAT8_TBL;
283+
INSERT INTO FLOAT8_TBL(f1) VALUES ('0.0');
284+
INSERT INTO FLOAT8_TBL(f1) VALUES ('-34.84');
285+
INSERT INTO FLOAT8_TBL(f1) VALUES ('-1004.30');
286+
INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e+200');
287+
INSERT INTO FLOAT8_TBL(f1) VALUES ('-1.2345678901234e-200');
288+
SELECT '' AS five, FLOAT8_TBL.*;
289+
five | f1
290+
------+-----------------------
291+
| 0
292+
| -34.84
293+
| -1004.3
294+
| -1.2345678901234e+200
295+
| -1.2345678901234e-200
296+
(5 rows)
297+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp