inputs/postgreSQL/float4.sql

0001 --
0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
0003 --
0004 --
0005 -- FLOAT4
0006 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/float4.sql
0007
0008 CREATE TABLE FLOAT4_TBL (f1  float) USING parquet;
0009
0010 -- PostgreSQL implicitly casts string literals to data with floating point types, but
0011 -- Spark does not support that kind of implicit casts.
0012 INSERT INTO FLOAT4_TBL VALUES (float('    0.0'));
0013 INSERT INTO FLOAT4_TBL VALUES (float('1004.30   '));
0014 INSERT INTO FLOAT4_TBL VALUES (float('     -34.84    '));
0015 INSERT INTO FLOAT4_TBL VALUES (float('1.2345678901234e+20'));
0016 INSERT INTO FLOAT4_TBL VALUES (float('1.2345678901234e-20'));
0017
0018 -- [SPARK-28024] Incorrect numeric values when out of range
0019 -- test for over and under flow
0020 -- INSERT INTO FLOAT4_TBL VALUES ('10e70');
0021 -- INSERT INTO FLOAT4_TBL VALUES ('-10e70');
0022 -- INSERT INTO FLOAT4_TBL VALUES ('10e-70');
0023 -- INSERT INTO FLOAT4_TBL VALUES ('-10e-70');
0024
0025 -- INSERT INTO FLOAT4_TBL VALUES ('10e400');
0026 -- INSERT INTO FLOAT4_TBL VALUES ('-10e400');
0027 -- INSERT INTO FLOAT4_TBL VALUES ('10e-400');
0028 -- INSERT INTO FLOAT4_TBL VALUES ('-10e-400');
0029
0030 -- [SPARK-27923] Spark SQL insert there bad inputs to NULL
0031 -- bad input
0032 -- INSERT INTO FLOAT4_TBL VALUES ('');
0033 -- INSERT INTO FLOAT4_TBL VALUES ('       ');
0034 -- INSERT INTO FLOAT4_TBL VALUES ('xyz');
0035 -- INSERT INTO FLOAT4_TBL VALUES ('5.0.0');
0036 -- INSERT INTO FLOAT4_TBL VALUES ('5 . 0');
0037 -- INSERT INTO FLOAT4_TBL VALUES ('5.   0');
0038 -- INSERT INTO FLOAT4_TBL VALUES ('     - 3.0');
0039 -- INSERT INTO FLOAT4_TBL VALUES ('123            5');
0040
0041 -- special inputs
0042 SELECT float('NaN');
0043 SELECT float('nan');
0044 SELECT float('   NAN  ');
0045 SELECT float('infinity');
0046 SELECT float('          -INFINiTY   ');
0047 -- [SPARK-27923] Spark SQL insert there bad special inputs to NULL
0048 -- bad special inputs
0049 SELECT float('N A N');
0050 SELECT float('NaN x');
0051 SELECT float(' INFINITY    x');
0052
0053 SELECT float('Infinity') + 100.0;
0054 SELECT float('Infinity') / float('Infinity');
0055 SELECT float('nan') / float('nan');
0056 SELECT float(decimal('nan'));
0057
0058 SELECT '' AS five, * FROM FLOAT4_TBL;
0059
0060 SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3';
0061
0062 SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = '1004.3';
0063
0064 SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE '1004.3' > f.f1;
0065
0066 SELECT '' AS three, f.* FROM FLOAT4_TBL f WHERE  f.f1 < '1004.3';
0067
0068 SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE '1004.3' >= f.f1;
0069
0070 SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE  f.f1 <= '1004.3';
0071
0072 SELECT '' AS three, f.f1, f.f1 * '-10' AS x FROM FLOAT4_TBL f
0073    WHERE f.f1 > '0.0';
0074
0075 SELECT '' AS three, f.f1, f.f1 + '-10' AS x FROM FLOAT4_TBL f
0076    WHERE f.f1 > '0.0';
0077
0078 SELECT '' AS three, f.f1, f.f1 / '-10' AS x FROM FLOAT4_TBL f
0079    WHERE f.f1 > '0.0';
0080
0081 SELECT '' AS three, f.f1, f.f1 - '-10' AS x FROM FLOAT4_TBL f
0082    WHERE f.f1 > '0.0';
0083
0084 -- [SPARK-27923] Spark SQL returns NULL
0085 -- test divide by zero
0086 -- SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f;
0087
0088 SELECT '' AS five, * FROM FLOAT4_TBL;
0089
0090 -- [SPARK-28027] Spark SQL does not support prefix operator @
0091 -- test the unary float4abs operator
0092 -- SELECT '' AS five, f.f1, @f.f1 AS abs_f1 FROM FLOAT4_TBL f;
0093
0094 -- Spark SQL does not support update now.
0095 -- UPDATE FLOAT4_TBL
0096 --    SET f1 = FLOAT4_TBL.f1 * '-1'
0097 --    WHERE FLOAT4_TBL.f1 > '0.0';
0098
0099 -- SELECT '' AS five, * FROM FLOAT4_TBL;
0100
0101 -- [SPARK-28028] Cast numeric to integral type need round
0102 -- [SPARK-28024] Incorrect numeric values when out of range
0103 -- test edge-case coercions to integer
0104 SELECT smallint(float('32767.4'));
0105 SELECT smallint(float('32767.6'));
0106 SELECT smallint(float('-32768.4'));
0107 SELECT smallint(float('-32768.6'));
0108 SELECT int(float('2147483520'));
0109 SELECT int(float('2147483647'));
0110 SELECT int(float('-2147483648.5'));
0111 SELECT int(float('-2147483900'));
0112 SELECT bigint(float('9223369837831520256'));
0113 SELECT bigint(float('9223372036854775807'));
0114 SELECT bigint(float('-9223372036854775808.5'));
0115 SELECT bigint(float('-9223380000000000000'));
0116
0117 -- [SPARK-28061] Support for converting float to binary format
0118 -- Test for correct input rounding in edge cases.
0119 -- These lists are from Paxson 1991, excluding subnormals and
0120 -- inputs of over 9 sig. digits.
0121
0122 -- SELECT float4send('5e-20'::float4);
0123 -- SELECT float4send('67e14'::float4);
0124 -- SELECT float4send('985e15'::float4);
0125 -- SELECT float4send('55895e-16'::float4);
0126 -- SELECT float4send('7038531e-32'::float4);
0127 -- SELECT float4send('702990899e-20'::float4);
0128
0129 -- SELECT float4send('3e-23'::float4);
0130 -- SELECT float4send('57e18'::float4);
0131 -- SELECT float4send('789e-35'::float4);
0132 -- SELECT float4send('2539e-18'::float4);
0133 -- SELECT float4send('76173e28'::float4);
0134 -- SELECT float4send('887745e-11'::float4);
0135 -- SELECT float4send('5382571e-37'::float4);
0136 -- SELECT float4send('82381273e-35'::float4);
0137 -- SELECT float4send('750486563e-38'::float4);
0138
0139 -- Test that the smallest possible normalized input value inputs
0140 -- correctly, either in 9-significant-digit or shortest-decimal
0141 -- format.
0142 --
0143 -- exact val is             1.1754943508...
0144 -- shortest val is          1.1754944000
0145 -- midpoint to next val is  1.1754944208...
0146
0147 -- SELECT float4send('1.17549435e-38'::float4);
0148 -- SELECT float4send('1.1754944e-38'::float4);
0149
0150 -- We do not support creating types, skip the test below
0151 -- test output (and round-trip safety) of various values.
0152 -- To ensure we're testing what we think we're testing, start with
0153 -- float values specified by bit patterns (as a useful side effect,
0154 -- this means we'll fail on non-IEEE platforms).
0155
0156 -- create type xfloat4;
0157 -- create function xfloat4in(cstring) returns xfloat4 immutable strict
0158 --   language internal as 'int4in';
0159 -- create function xfloat4out(xfloat4) returns cstring immutable strict
0160 --   language internal as 'int4out';
0161 -- create type xfloat4 (input = xfloat4in, output = xfloat4out, like = float4);
0162 -- create cast (xfloat4 as float4) without function;
0163 -- create cast (float4 as xfloat4) without function;
0164 -- create cast (xfloat4 as integer) without function;
0165 -- create cast (integer as xfloat4) without function;
0166
0167 -- float4: seeeeeee emmmmmmm mmmmmmmm mmmmmmmm
0168
0169 -- we don't care to assume the platform's strtod() handles subnormals
0170 -- correctly; those are "use at your own risk". However we do test
0171 -- subnormal outputs, since those are under our control.
0172
0173 -- with testdata(bits) as (values
0174 --   -- small subnormals
0175 --   (x'00000001'),
0176 --   (x'00000002'), (x'00000003'),
0177 --   (x'00000010'), (x'00000011'), (x'00000100'), (x'00000101'),
0178 --   (x'00004000'), (x'00004001'), (x'00080000'), (x'00080001'),
0179 --   -- stress values
0180 --   (x'0053c4f4'),  -- 7693e-42
0181 --   (x'006c85c4'),  -- 996622e-44
0182 --   (x'0041ca76'),  -- 60419369e-46
0183 --   (x'004b7678'),  -- 6930161142e-48
0184 --   -- taken from upstream testsuite
0185 --   (x'00000007'),
0186 --   (x'00424fe2'),
0187 --   -- borderline between subnormal and normal
0188 --   (x'007ffff0'), (x'007ffff1'), (x'007ffffe'), (x'007fffff'))
0189 -- select float4send(flt) as ibits,
0190 --        flt
0191 --   from (select bits::integer::xfloat4::float4 as flt
0192 --           from testdata
0193 --      offset 0) s;
0194
0195 -- with testdata(bits) as (values
0196 --   (x'00000000'),
0197 --   -- smallest normal values
0198 --   (x'00800000'), (x'00800001'), (x'00800004'), (x'00800005'),
0199 --   (x'00800006'),
0200 --   -- small normal values chosen for short vs. long output
0201 --   (x'008002f1'), (x'008002f2'), (x'008002f3'),
0202 --   (x'00800e17'), (x'00800e18'), (x'00800e19'),
0203 --   -- assorted values (random mantissae)
0204 --   (x'01000001'), (x'01102843'), (x'01a52c98'),
0205 --   (x'0219c229'), (x'02e4464d'), (x'037343c1'), (x'03a91b36'),
0206 --   (x'047ada65'), (x'0496fe87'), (x'0550844f'), (x'05999da3'),
0207 --   (x'060ea5e2'), (x'06e63c45'), (x'07f1e548'), (x'0fc5282b'),
0208 --   (x'1f850283'), (x'2874a9d6'),
0209 --   -- values around 5e-08
0210 --   (x'3356bf94'), (x'3356bf95'), (x'3356bf96'),
0211 --   -- around 1e-07
0212 --   (x'33d6bf94'), (x'33d6bf95'), (x'33d6bf96'),
0213 --   -- around 3e-07 .. 1e-04
0214 --   (x'34a10faf'), (x'34a10fb0'), (x'34a10fb1'),
0215 --   (x'350637bc'), (x'350637bd'), (x'350637be'),
0216 --   (x'35719786'), (x'35719787'), (x'35719788'),
0217 --   (x'358637bc'), (x'358637bd'), (x'358637be'),
0218 --   (x'36a7c5ab'), (x'36a7c5ac'), (x'36a7c5ad'),
0219 --   (x'3727c5ab'), (x'3727c5ac'), (x'3727c5ad'),
0220 --   -- format crossover at 1e-04
0221 --   (x'38d1b714'), (x'38d1b715'), (x'38d1b716'),
0222 --   (x'38d1b717'), (x'38d1b718'), (x'38d1b719'),
0223 --   (x'38d1b71a'), (x'38d1b71b'), (x'38d1b71c'),
0224 --   (x'38d1b71d'),
0225 --   --
0226 --   (x'38dffffe'), (x'38dfffff'), (x'38e00000'),
0227 --   (x'38efffff'), (x'38f00000'), (x'38f00001'),
0228 --   (x'3a83126e'), (x'3a83126f'), (x'3a831270'),
0229 --   (x'3c23d709'), (x'3c23d70a'), (x'3c23d70b'),
0230 --   (x'3dcccccc'), (x'3dcccccd'), (x'3dccccce'),
0231 --   -- chosen to need 9 digits for 3dcccd70
0232 --   (x'3dcccd6f'), (x'3dcccd70'), (x'3dcccd71'),
0233 --   --
0234 --   (x'3effffff'), (x'3f000000'), (x'3f000001'),
0235 --   (x'3f333332'), (x'3f333333'), (x'3f333334'),
0236 --   -- approach 1.0 with increasing numbers of 9s
0237 --   (x'3f666665'), (x'3f666666'), (x'3f666667'),
0238 --   (x'3f7d70a3'), (x'3f7d70a4'), (x'3f7d70a5'),
0239 --   (x'3f7fbe76'), (x'3f7fbe77'), (x'3f7fbe78'),
0240 --   (x'3f7ff971'), (x'3f7ff972'), (x'3f7ff973'),
0241 --   (x'3f7fff57'), (x'3f7fff58'), (x'3f7fff59'),
0242 --   (x'3f7fffee'), (x'3f7fffef'),
0243 --   -- values very close to 1
0244 --   (x'3f7ffff0'), (x'3f7ffff1'), (x'3f7ffff2'),
0245 --   (x'3f7ffff3'), (x'3f7ffff4'), (x'3f7ffff5'),
0246 --   (x'3f7ffff6'), (x'3f7ffff7'), (x'3f7ffff8'),
0247 --   (x'3f7ffff9'), (x'3f7ffffa'), (x'3f7ffffb'),
0248 --   (x'3f7ffffc'), (x'3f7ffffd'), (x'3f7ffffe'),
0249 --   (x'3f7fffff'),
0250 --   (x'3f800000'),
0251 --   (x'3f800001'), (x'3f800002'), (x'3f800003'),
0252 --   (x'3f800004'), (x'3f800005'), (x'3f800006'),
0253 --   (x'3f800007'), (x'3f800008'), (x'3f800009'),
0254 --   -- values 1 to 1.1
0255 --   (x'3f80000f'), (x'3f800010'), (x'3f800011'),
0256 --   (x'3f800012'), (x'3f800013'), (x'3f800014'),
0257 --   (x'3f800017'), (x'3f800018'), (x'3f800019'),
0258 --   (x'3f80001a'), (x'3f80001b'), (x'3f80001c'),
0259 --   (x'3f800029'), (x'3f80002a'), (x'3f80002b'),
0260 --   (x'3f800053'), (x'3f800054'), (x'3f800055'),
0261 --   (x'3f800346'), (x'3f800347'), (x'3f800348'),
0262 --   (x'3f8020c4'), (x'3f8020c5'), (x'3f8020c6'),
0263 --   (x'3f8147ad'), (x'3f8147ae'), (x'3f8147af'),
0264 --   (x'3f8ccccc'), (x'3f8ccccd'), (x'3f8cccce'),
0265 --   --
0266 --   (x'3fc90fdb'), -- pi/2
0267 --   (x'402df854'), -- e
0268 --   (x'40490fdb'), -- pi
0269 --   --
0270 --   (x'409fffff'), (x'40a00000'), (x'40a00001'),
0271 --   (x'40afffff'), (x'40b00000'), (x'40b00001'),
0272 --   (x'411fffff'), (x'41200000'), (x'41200001'),
0273 --   (x'42c7ffff'), (x'42c80000'), (x'42c80001'),
0274 --   (x'4479ffff'), (x'447a0000'), (x'447a0001'),
0275 --   (x'461c3fff'), (x'461c4000'), (x'461c4001'),
0276 --   (x'47c34fff'), (x'47c35000'), (x'47c35001'),
0277 --   (x'497423ff'), (x'49742400'), (x'49742401'),
0278 --   (x'4b18967f'), (x'4b189680'), (x'4b189681'),
0279 --   (x'4cbebc1f'), (x'4cbebc20'), (x'4cbebc21'),
0280 --   (x'4e6e6b27'), (x'4e6e6b28'), (x'4e6e6b29'),
0281 --   (x'501502f8'), (x'501502f9'), (x'501502fa'),
0282 --   (x'51ba43b6'), (x'51ba43b7'), (x'51ba43b8'),
0283 --   -- stress values
0284 --   (x'1f6c1e4a'),  -- 5e-20
0285 --   (x'59be6cea'),  -- 67e14
0286 --   (x'5d5ab6c4'),  -- 985e15
0287 --   (x'2cc4a9bd'),  -- 55895e-16
0288 --   (x'15ae43fd'),  -- 7038531e-32
0289 --   (x'2cf757ca'),  -- 702990899e-20
0290 --   (x'665ba998'),  -- 25933168707e13
0291 --   (x'743c3324'),  -- 596428896559e20
0292 --   -- exercise fixed-point memmoves
0293 --   (x'47f1205a'),
0294 --   (x'4640e6ae'),
0295 --   (x'449a5225'),
0296 --   (x'42f6e9d5'),
0297 --   (x'414587dd'),
0298 --   (x'3f9e064b'),
0299 --   -- these cases come from the upstream's testsuite
0300 --   -- BoundaryRoundEven
0301 --   (x'4c000004'),
0302 --   (x'50061c46'),
0303 --   (x'510006a8'),
0304 --   -- ExactValueRoundEven
0305 --   (x'48951f84'),
0306 --   (x'45fd1840'),
0307 --   -- LotsOfTrailingZeros
0308 --   (x'39800000'),
0309 --   (x'3b200000'),
0310 --   (x'3b900000'),
0311 --   (x'3bd00000'),
0312 --   -- Regression
0313 --   (x'63800000'),
0314 --   (x'4b000000'),
0315 --   (x'4b800000'),
0316 --   (x'4c000001'),
0317 --   (x'4c800b0d'),
0318 --   (x'00d24584'),
0319 --   (x'00d90b88'),
0320 --   (x'45803f34'),
0321 --   (x'4f9f24f7'),
0322 --   (x'3a8722c3'),
0323 --   (x'5c800041'),
0324 --   (x'15ae43fd'),
0325 --   (x'5d4cccfb'),
0326 --   (x'4c800001'),
0327 --   (x'57800ed8'),
0328 --   (x'5f000000'),
0329 --   (x'700000f0'),
0330 --   (x'5f23e9ac'),
0331 --   (x'5e9502f9'),
0332 --   (x'5e8012b1'),
0333 --   (x'3c000028'),
0334 --   (x'60cde861'),
0335 --   (x'03aa2a50'),
0336 --   (x'43480000'),
0337 --   (x'4c000000'),
0338 --   -- LooksLikePow5
0339 --   (x'5D1502F9'),
0340 --   (x'5D9502F9'),
0341 --   (x'5E1502F9'),
0342 --   -- OutputLength
0343 --   (x'3f99999a'),
0344 --   (x'3f9d70a4'),
0345 --   (x'3f9df3b6'),
0346 --   (x'3f9e0419'),
0347 --   (x'3f9e0610'),
0348 --   (x'3f9e064b'),
0349 --   (x'3f9e0651'),
0350 --   (x'03d20cfe')
0351 -- )
0352 -- select float4send(flt) as ibits,
0353 --        flt,
0354 --        flt::text::float4 as r_flt,
0355 --        float4send(flt::text::float4) as obits,
0356 --        float4send(flt::text::float4) = float4send(flt) as correct
0357 --   from (select bits::integer::xfloat4::float4 as flt
0358 --           from testdata
0359 --      offset 0) s;
0360
0361 -- clean up, lest opr_sanity complain
0362 -- drop type xfloat4 cascade;
0363 DROP TABLE FLOAT4_TBL;