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