1--
2-- FLOAT4
3--
4
5CREATE TABLE FLOAT4_TBL (f1  float4);
6
7INSERT INTO FLOAT4_TBL(f1) VALUES ('    0.0');
8INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30   ');
9INSERT INTO FLOAT4_TBL(f1) VALUES ('     -34.84    ');
10INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20');
11INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');
12
13-- test for over and under flow
14INSERT INTO FLOAT4_TBL(f1) VALUES ('10e70');
15INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e70');
16INSERT INTO FLOAT4_TBL(f1) VALUES ('10e-70');
17INSERT INTO FLOAT4_TBL(f1) VALUES ('-10e-70');
18
19-- bad input
20INSERT INTO FLOAT4_TBL(f1) VALUES ('');
21INSERT INTO FLOAT4_TBL(f1) VALUES ('       ');
22INSERT INTO FLOAT4_TBL(f1) VALUES ('xyz');
23INSERT INTO FLOAT4_TBL(f1) VALUES ('5.0.0');
24INSERT INTO FLOAT4_TBL(f1) VALUES ('5 . 0');
25INSERT INTO FLOAT4_TBL(f1) VALUES ('5.   0');
26INSERT INTO FLOAT4_TBL(f1) VALUES ('     - 3.0');
27INSERT INTO FLOAT4_TBL(f1) VALUES ('123            5');
28
29-- special inputs
30SELECT 'NaN'::float4;
31SELECT 'nan'::float4;
32SELECT '   NAN  '::float4;
33SELECT 'infinity'::float4;
34SELECT '          -INFINiTY   '::float4;
35-- bad special inputs
36SELECT 'N A N'::float4;
37SELECT 'NaN x'::float4;
38SELECT ' INFINITY    x'::float4;
39
40SELECT 'Infinity'::float4 + 100.0;
41SELECT 'Infinity'::float4 / 'Infinity'::float4;
42SELECT 'nan'::float4 / 'nan'::float4;
43SELECT 'nan'::numeric::float4;
44
45SELECT '' AS five, * FROM FLOAT4_TBL;
46
47SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3';
48
49SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3';
50
51SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1;
52
53SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE  f.f1 < '1004.3';
54
55SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1;
56
57SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE  f.f1 <= '1004.3';
58
59SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f
60   WHERE f.f1 > '0.0';
61
62SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f
63   WHERE f.f1 > '0.0';
64
65SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f
66   WHERE f.f1 > '0.0';
67
68SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f
69   WHERE f.f1 > '0.0';
70
71-- test divide by zero
72SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f;
73
74SELECT '' AS five, * FROM FLOAT4_TBL;
75
76-- test the unary float4abs operator
77SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f;
78
79UPDATE FLOAT4_TBL
80   SET f1 = FLOAT4_TBL.f1 * '-1'
81   WHERE FLOAT4_TBL.f1 > '0.0';
82
83SELECT '' AS five, * FROM FLOAT4_TBL;
84
85-- test edge-case coercions to integer
86SELECT '32767.4'::float4::int2;
87SELECT '32767.6'::float4::int2;
88SELECT '-32768.4'::float4::int2;
89SELECT '-32768.6'::float4::int2;
90SELECT '2147483520'::float4::int4;
91SELECT '2147483647'::float4::int4;
92SELECT '-2147483648.5'::float4::int4;
93SELECT '-2147483900'::float4::int4;
94SELECT '9223369837831520256'::float4::int8;
95SELECT '9223372036854775807'::float4::int8;
96SELECT '-9223372036854775808.5'::float4::int8;
97SELECT '-9223380000000000000'::float4::int8;
98