diff options
Diffstat (limited to 'src/test/regress/expected/numeric.out')
| -rw-r--r-- | src/test/regress/expected/numeric.out | 680 |
1 files changed, 661 insertions, 19 deletions
diff --git a/src/test/regress/expected/numeric.out b/src/test/regress/expected/numeric.out index 81a0c5d40f..8546ce901f 100644 --- a/src/test/regress/expected/numeric.out +++ b/src/test/regress/expected/numeric.out @@ -661,6 +661,432 @@ SELECT t1.id1, t1.result, t2.expected (0 rows) -- ****************************** +-- * Check behavior with Inf and NaN inputs. It's easiest to handle these +-- * separately from the num_data framework used above, because some input +-- * combinations will throw errors. +-- ****************************** +WITH v(x) AS + (VALUES('0'::numeric),('1'),('-1'),('4.2'),('inf'),('-inf'),('nan')) +SELECT x1, x2, + x1 + x2 AS sum, + x1 - x2 AS diff, + x1 * x2 AS prod +FROM v AS v1(x1), v AS v2(x2); + x1 | x2 | sum | diff | prod +-----------+-----------+-----------+-----------+----------- + 0 | 0 | 0 | 0 | 0 + 0 | 1 | 1 | -1 | 0 + 0 | -1 | -1 | 1 | 0 + 0 | 4.2 | 4.2 | -4.2 | 0.0 + 0 | Infinity | Infinity | -Infinity | NaN + 0 | -Infinity | -Infinity | Infinity | NaN + 0 | NaN | NaN | NaN | NaN + 1 | 0 | 1 | 1 | 0 + 1 | 1 | 2 | 0 | 1 + 1 | -1 | 0 | 2 | -1 + 1 | 4.2 | 5.2 | -3.2 | 4.2 + 1 | Infinity | Infinity | -Infinity | Infinity + 1 | -Infinity | -Infinity | Infinity | -Infinity + 1 | NaN | NaN | NaN | NaN + -1 | 0 | -1 | -1 | 0 + -1 | 1 | 0 | -2 | -1 + -1 | -1 | -2 | 0 | 1 + -1 | 4.2 | 3.2 | -5.2 | -4.2 + -1 | Infinity | Infinity | -Infinity | -Infinity + -1 | -Infinity | -Infinity | Infinity | Infinity + -1 | NaN | NaN | NaN | NaN + 4.2 | 0 | 4.2 | 4.2 | 0.0 + 4.2 | 1 | 5.2 | 3.2 | 4.2 + 4.2 | -1 | 3.2 | 5.2 | -4.2 + 4.2 | 4.2 | 8.4 | 0.0 | 17.64 + 4.2 | Infinity | Infinity | -Infinity | Infinity + 4.2 | -Infinity | -Infinity | Infinity | -Infinity + 4.2 | NaN | NaN | NaN | NaN + Infinity | 0 | Infinity | Infinity | NaN + Infinity | 1 | Infinity | Infinity | Infinity + Infinity | -1 | Infinity | Infinity | -Infinity + Infinity | 4.2 | Infinity | Infinity | Infinity + Infinity | Infinity | Infinity | NaN | Infinity + Infinity | -Infinity | NaN | Infinity | -Infinity + Infinity | NaN | NaN | NaN | NaN + -Infinity | 0 | -Infinity | -Infinity | NaN + -Infinity | 1 | -Infinity | -Infinity | -Infinity + -Infinity | -1 | -Infinity | -Infinity | Infinity + -Infinity | 4.2 | -Infinity | -Infinity | -Infinity + -Infinity | Infinity | NaN | -Infinity | -Infinity + -Infinity | -Infinity | -Infinity | NaN | Infinity + -Infinity | NaN | NaN | NaN | NaN + NaN | 0 | NaN | NaN | NaN + NaN | 1 | NaN | NaN | NaN + NaN | -1 | NaN | NaN | NaN + NaN | 4.2 | NaN | NaN | NaN + NaN | Infinity | NaN | NaN | NaN + NaN | -Infinity | NaN | NaN | NaN + NaN | NaN | NaN | NaN | NaN +(49 rows) + +WITH v(x) AS + (VALUES('0'::numeric),('1'),('-1'),('4.2'),('inf'),('-inf'),('nan')) +SELECT x1, x2, + x1 / x2 AS quot, + x1 % x2 AS mod, + div(x1, x2) AS div +FROM v AS v1(x1), v AS v2(x2) WHERE x2 != 0; + x1 | x2 | quot | mod | div +-----------+-----------+-------------------------+------+----------- + 0 | 1 | 0.00000000000000000000 | 0 | 0 + 1 | 1 | 1.00000000000000000000 | 0 | 1 + -1 | 1 | -1.00000000000000000000 | 0 | -1 + 4.2 | 1 | 4.2000000000000000 | 0.2 | 4 + Infinity | 1 | Infinity | NaN | Infinity + -Infinity | 1 | -Infinity | NaN | -Infinity + NaN | 1 | NaN | NaN | NaN + 0 | -1 | 0.00000000000000000000 | 0 | 0 + 1 | -1 | -1.00000000000000000000 | 0 | -1 + -1 | -1 | 1.00000000000000000000 | 0 | 1 + 4.2 | -1 | -4.2000000000000000 | 0.2 | -4 + Infinity | -1 | -Infinity | NaN | -Infinity + -Infinity | -1 | Infinity | NaN | Infinity + NaN | -1 | NaN | NaN | NaN + 0 | 4.2 | 0.00000000000000000000 | 0.0 | 0 + 1 | 4.2 | 0.23809523809523809524 | 1.0 | 0 + -1 | 4.2 | -0.23809523809523809524 | -1.0 | 0 + 4.2 | 4.2 | 1.00000000000000000000 | 0.0 | 1 + Infinity | 4.2 | Infinity | NaN | Infinity + -Infinity | 4.2 | -Infinity | NaN | -Infinity + NaN | 4.2 | NaN | NaN | NaN + 0 | Infinity | 0 | 0 | 0 + 1 | Infinity | 0 | 1 | 0 + -1 | Infinity | 0 | -1 | 0 + 4.2 | Infinity | 0 | 4.2 | 0 + Infinity | Infinity | NaN | NaN | NaN + -Infinity | Infinity | NaN | NaN | NaN + NaN | Infinity | NaN | NaN | NaN + 0 | -Infinity | 0 | 0 | 0 + 1 | -Infinity | 0 | 1 | 0 + -1 | -Infinity | 0 | -1 | 0 + 4.2 | -Infinity | 0 | 4.2 | 0 + Infinity | -Infinity | NaN | NaN | NaN + -Infinity | -Infinity | NaN | NaN | NaN + NaN | -Infinity | NaN | NaN | NaN + 0 | NaN | NaN | NaN | NaN + 1 | NaN | NaN | NaN | NaN + -1 | NaN | NaN | NaN | NaN + 4.2 | NaN | NaN | NaN | NaN + Infinity | NaN | NaN | NaN | NaN + -Infinity | NaN | NaN | NaN | NaN + NaN | NaN | NaN | NaN | NaN +(42 rows) + +SELECT 'inf'::numeric / '0'; +ERROR: division by zero +SELECT '-inf'::numeric / '0'; +ERROR: division by zero +SELECT 'nan'::numeric / '0'; + ?column? +---------- + NaN +(1 row) + +SELECT '0'::numeric / '0'; +ERROR: division by zero +SELECT 'inf'::numeric % '0'; +ERROR: division by zero +SELECT '-inf'::numeric % '0'; +ERROR: division by zero +SELECT 'nan'::numeric % '0'; + ?column? +---------- + NaN +(1 row) + +SELECT '0'::numeric % '0'; +ERROR: division by zero +SELECT div('inf'::numeric, '0'); +ERROR: division by zero +SELECT div('-inf'::numeric, '0'); +ERROR: division by zero +SELECT div('nan'::numeric, '0'); + div +----- + NaN +(1 row) + +SELECT div('0'::numeric, '0'); +ERROR: division by zero +WITH v(x) AS + (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('inf'),('-inf'),('nan')) +SELECT x, -x as minusx, abs(x), floor(x), ceil(x), sign(x), numeric_inc(x) as inc +FROM v; + x | minusx | abs | floor | ceil | sign | inc +-----------+-----------+----------+-----------+-----------+------+----------- + 0 | 0 | 0 | 0 | 0 | 0 | 1 + 1 | -1 | 1 | 1 | 1 | 1 | 2 + -1 | 1 | 1 | -1 | -1 | -1 | 0 + 4.2 | -4.2 | 4.2 | 4 | 5 | 1 | 5.2 + -7.777 | 7.777 | 7.777 | -8 | -7 | -1 | -6.777 + Infinity | -Infinity | Infinity | Infinity | Infinity | 1 | Infinity + -Infinity | Infinity | Infinity | -Infinity | -Infinity | -1 | -Infinity + NaN | NaN | NaN | NaN | NaN | NaN | NaN +(8 rows) + +WITH v(x) AS + (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('inf'),('-inf'),('nan')) +SELECT x, round(x), round(x,1) as round1, trunc(x), trunc(x,1) as trunc1 +FROM v; + x | round | round1 | trunc | trunc1 +-----------+-----------+-----------+-----------+----------- + 0 | 0 | 0.0 | 0 | 0.0 + 1 | 1 | 1.0 | 1 | 1.0 + -1 | -1 | -1.0 | -1 | -1.0 + 4.2 | 4 | 4.2 | 4 | 4.2 + -7.777 | -8 | -7.8 | -7 | -7.7 + Infinity | Infinity | Infinity | Infinity | Infinity + -Infinity | -Infinity | -Infinity | -Infinity | -Infinity + NaN | NaN | NaN | NaN | NaN +(8 rows) + +-- the large values fall into the numeric abbreviation code's maximal classes +WITH v(x) AS + (VALUES('0'::numeric),('1'),('-1'),('4.2'),('-7.777'),('1e340'),('-1e340'), + ('inf'),('-inf'),('nan'), + ('inf'),('-inf'),('nan')) +SELECT substring(x::text, 1, 32) +FROM v ORDER BY x; + substring +---------------------------------- + -Infinity + -Infinity + -1000000000000000000000000000000 + -7.777 + -1 + 0 + 1 + 4.2 + 10000000000000000000000000000000 + Infinity + Infinity + NaN + NaN +(13 rows) + +WITH v(x) AS + (VALUES('0'::numeric),('1'),('4.2'),('inf'),('nan')) +SELECT x, sqrt(x) +FROM v; + x | sqrt +----------+------------------- + 0 | 0.000000000000000 + 1 | 1.000000000000000 + 4.2 | 2.049390153191920 + Infinity | Infinity + NaN | NaN +(5 rows) + +SELECT sqrt('-1'::numeric); +ERROR: cannot take square root of a negative number +SELECT sqrt('-inf'::numeric); +ERROR: cannot take square root of a negative number +WITH v(x) AS + (VALUES('1'::numeric),('4.2'),('inf'),('nan')) +SELECT x, + log(x), + log10(x), + ln(x) +FROM v; + x | log | log10 | ln +----------+--------------------+--------------------+-------------------- + 1 | 0.0000000000000000 | 0.0000000000000000 | 0.0000000000000000 + 4.2 | 0.6232492903979005 | 0.6232492903979005 | 1.4350845252893226 + Infinity | Infinity | Infinity | Infinity + NaN | NaN | NaN | NaN +(4 rows) + +SELECT ln('0'::numeric); +ERROR: cannot take logarithm of zero +SELECT ln('-1'::numeric); +ERROR: cannot take logarithm of a negative number +SELECT ln('-inf'::numeric); +ERROR: cannot take logarithm of a negative number +WITH v(x) AS + (VALUES('2'::numeric),('4.2'),('inf'),('nan')) +SELECT x1, x2, + log(x1, x2) +FROM v AS v1(x1), v AS v2(x2); + x1 | x2 | log +----------+----------+-------------------- + 2 | 2 | 1.0000000000000000 + 2 | 4.2 | 2.0703893278913979 + 2 | Infinity | Infinity + 2 | NaN | NaN + 4.2 | 2 | 0.4830009440873890 + 4.2 | 4.2 | 1.0000000000000000 + 4.2 | Infinity | Infinity + 4.2 | NaN | NaN + Infinity | 2 | 0 + Infinity | 4.2 | 0 + Infinity | Infinity | NaN + Infinity | NaN | NaN + NaN | 2 | NaN + NaN | 4.2 | NaN + NaN | Infinity | NaN + NaN | NaN | NaN +(16 rows) + +SELECT log('0'::numeric, '10'); +ERROR: cannot take logarithm of zero +SELECT log('10'::numeric, '0'); +ERROR: cannot take logarithm of zero +SELECT log('-inf'::numeric, '10'); +ERROR: cannot take logarithm of a negative number +SELECT log('10'::numeric, '-inf'); +ERROR: cannot take logarithm of a negative number +SELECT log('inf'::numeric, '0'); +ERROR: cannot take logarithm of zero +SELECT log('inf'::numeric, '-inf'); +ERROR: cannot take logarithm of a negative number +SELECT log('-inf'::numeric, 'inf'); +ERROR: cannot take logarithm of a negative number +WITH v(x) AS + (VALUES('0'::numeric),('1'),('2'),('4.2'),('inf'),('nan')) +SELECT x1, x2, + power(x1, x2) +FROM v AS v1(x1), v AS v2(x2) WHERE x1 != 0 OR x2 >= 0; + x1 | x2 | power +----------+----------+--------------------- + 0 | 0 | 1.0000000000000000 + 0 | 1 | 0.0000000000000000 + 0 | 2 | 0.0000000000000000 + 0 | 4.2 | 0.0000000000000000 + 0 | Infinity | 0 + 0 | NaN | NaN + 1 | 0 | 1.0000000000000000 + 1 | 1 | 1.0000000000000000 + 1 | 2 | 1.0000000000000000 + 1 | 4.2 | 1.0000000000000000 + 1 | Infinity | 1 + 1 | NaN | 1 + 2 | 0 | 1.0000000000000000 + 2 | 1 | 2.0000000000000000 + 2 | 2 | 4.0000000000000000 + 2 | 4.2 | 18.379173679952560 + 2 | Infinity | Infinity + 2 | NaN | NaN + 4.2 | 0 | 1.0000000000000000 + 4.2 | 1 | 4.2000000000000000 + 4.2 | 2 | 17.6400000000000000 + 4.2 | 4.2 | 414.61691860129675 + 4.2 | Infinity | Infinity + 4.2 | NaN | NaN + Infinity | 0 | 1 + Infinity | 1 | Infinity + Infinity | 2 | Infinity + Infinity | 4.2 | Infinity + Infinity | Infinity | Infinity + Infinity | NaN | NaN + NaN | 0 | 1 + NaN | 1 | NaN + NaN | 2 | NaN + NaN | 4.2 | NaN + NaN | Infinity | NaN + NaN | NaN | NaN +(36 rows) + +SELECT power('0'::numeric, '-1'); +ERROR: zero raised to a negative power is undefined +SELECT power('0'::numeric, '-inf'); +ERROR: zero raised to a negative power is undefined +SELECT power('-1'::numeric, 'inf'); + power +------- + 1 +(1 row) + +SELECT power('-2'::numeric, '3'); + power +--------------------- + -8.0000000000000000 +(1 row) + +SELECT power('-2'::numeric, '3.3'); +ERROR: a negative number raised to a non-integer power yields a complex result +SELECT power('-2'::numeric, '-1'); + power +--------------------- + -0.5000000000000000 +(1 row) + +SELECT power('-2'::numeric, '-1.5'); +ERROR: a negative number raised to a non-integer power yields a complex result +SELECT power('-2'::numeric, 'inf'); + power +---------- + Infinity +(1 row) + +SELECT power('-2'::numeric, '-inf'); + power +------- + 0 +(1 row) + +SELECT power('inf'::numeric, '-2'); + power +------- + 0 +(1 row) + +SELECT power('inf'::numeric, '-inf'); + power +------- + 0 +(1 row) + +SELECT power('-inf'::numeric, '2'); + power +---------- + Infinity +(1 row) + +SELECT power('-inf'::numeric, '3'); + power +----------- + -Infinity +(1 row) + +SELECT power('-inf'::numeric, '4.5'); +ERROR: a negative number raised to a non-integer power yields a complex result +SELECT power('-inf'::numeric, '-2'); + power +------- + 0 +(1 row) + +SELECT power('-inf'::numeric, '-3'); + power +------- + 0 +(1 row) + +SELECT power('-inf'::numeric, '0'); + power +------- + 1 +(1 row) + +SELECT power('-inf'::numeric, 'inf'); + power +---------- + Infinity +(1 row) + +SELECT power('-inf'::numeric, '-inf'); + power +------- + 0 +(1 row) + +-- ****************************** -- * miscellaneous checks for things that have been broken in the past... -- ****************************** -- numeric AVG used to fail on some platforms @@ -696,6 +1122,13 @@ ERROR: numeric field overflow DETAIL: A field with precision 4, scale 4 must round to an absolute value less than 1. INSERT INTO fract_only VALUES (7, '0.00001'); INSERT INTO fract_only VALUES (8, '0.00017'); +INSERT INTO fract_only VALUES (9, 'NaN'); +INSERT INTO fract_only VALUES (10, 'Inf'); -- should fail +ERROR: numeric field overflow +DETAIL: A field with precision 4, scale 4 cannot hold an infinite value. +INSERT INTO fract_only VALUES (11, '-Inf'); -- should fail +ERROR: numeric field overflow +DETAIL: A field with precision 4, scale 4 cannot hold an infinite value. SELECT * FROM fract_only; id | val ----+--------- @@ -705,7 +1138,8 @@ SELECT * FROM fract_only; 5 | 0.9999 7 | 0.0000 8 | 0.0002 -(6 rows) + 9 | NaN +(7 rows) DROP TABLE fract_only; -- Check inf/nan conversion behavior @@ -716,9 +1150,35 @@ SELECT 'NaN'::float8::numeric; (1 row) SELECT 'Infinity'::float8::numeric; -ERROR: cannot convert infinity to numeric + numeric +---------- + Infinity +(1 row) + SELECT '-Infinity'::float8::numeric; -ERROR: cannot convert infinity to numeric + numeric +----------- + -Infinity +(1 row) + +SELECT 'NaN'::numeric::float8; + float8 +-------- + NaN +(1 row) + +SELECT 'Infinity'::numeric::float8; + float8 +---------- + Infinity +(1 row) + +SELECT '-Infinity'::numeric::float8; + float8 +----------- + -Infinity +(1 row) + SELECT 'NaN'::float4::numeric; numeric --------- @@ -726,9 +1186,59 @@ SELECT 'NaN'::float4::numeric; (1 row) SELECT 'Infinity'::float4::numeric; -ERROR: cannot convert infinity to numeric + numeric +---------- + Infinity +(1 row) + SELECT '-Infinity'::float4::numeric; -ERROR: cannot convert infinity to numeric + numeric +----------- + -Infinity +(1 row) + +SELECT 'NaN'::numeric::float4; + float4 +-------- + NaN +(1 row) + +SELECT 'Infinity'::numeric::float4; + float4 +---------- + Infinity +(1 row) + +SELECT '-Infinity'::numeric::float4; + float4 +----------- + -Infinity +(1 row) + +SELECT '42'::int2::numeric; + numeric +--------- + 42 +(1 row) + +SELECT 'NaN'::numeric::int2; +ERROR: cannot convert NaN to smallint +SELECT 'Infinity'::numeric::int2; +ERROR: cannot convert infinity to smallint +SELECT '-Infinity'::numeric::int2; +ERROR: cannot convert infinity to smallint +SELECT 'NaN'::numeric::int4; +ERROR: cannot convert NaN to integer +SELECT 'Infinity'::numeric::int4; +ERROR: cannot convert infinity to integer +SELECT '-Infinity'::numeric::int4; +ERROR: cannot convert infinity to integer +SELECT 'NaN'::numeric::int8; +ERROR: cannot convert NaN to bigint +SELECT 'Infinity'::numeric::int8; +ERROR: cannot convert infinity to bigint +SELECT '-Infinity'::numeric::int8; +ERROR: cannot convert infinity to bigint -- Simple check that ceil(), floor(), and round() work correctly CREATE TABLE ceil_floor_round (a numeric); INSERT INTO ceil_floor_round VALUES ('-5.5'); @@ -794,6 +1304,12 @@ SELECT width_bucket('NaN', 3.0, 4.0, 888); ERROR: operand, lower bound, and upper bound cannot be NaN SELECT width_bucket(0::float8, 'NaN', 4.0::float8, 888); ERROR: operand, lower bound, and upper bound cannot be NaN +SELECT width_bucket('inf', 3.0, 4.0, 888); +ERROR: operand, lower bound, and upper bound cannot be infinity +SELECT width_bucket(2.0, 3.0, '-inf', 888); +ERROR: operand, lower bound, and upper bound cannot be infinity +SELECT width_bucket(0::float8, '-inf', 4.0::float8, 888); +ERROR: lower and upper bounds must be finite -- normal operation CREATE TABLE width_bucket_test (operand_num numeric, operand_f8 float8); COPY width_bucket_test (operand_num) FROM stdin; @@ -1199,6 +1715,60 @@ SELECT '' AS to_char_23, to_char(val, '9.999EEEE') FROM num_data; | -2.493e+07 (10 rows) +WITH v(val) AS + (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan')) +SELECT val, + to_char(val, '9.999EEEE') as numeric, + to_char(val::float8, '9.999EEEE') as float8, + to_char(val::float4, '9.999EEEE') as float4 +FROM v; + val | numeric | float8 | float4 +------------+------------+------------+------------ + 0 | 0.000e+00 | 0.000e+00 | 0.000e+00 + -4.2 | -4.200e+00 | -4.200e+00 | -4.200e+00 + 4200000000 | 4.200e+09 | 4.200e+09 | 4.200e+09 + 0.000012 | 1.200e-05 | 1.200e-05 | 1.200e-05 + Infinity | #.####### | #.####### | #.####### + -Infinity | #.####### | #.####### | #.####### + NaN | #.####### | #.####### | #.####### +(7 rows) + +WITH v(val) AS + (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan')) +SELECT val, + to_char(val, 'MI9999999999.99') as numeric, + to_char(val::float8, 'MI9999999999.99') as float8, + to_char(val::float4, 'MI9999999999.99') as float4 +FROM v; + val | numeric | float8 | float4 +------------+----------------+----------------+---------------- + 0 | .00 | .00 | .00 + -4.2 | - 4.20 | - 4.20 | - 4.20 + 4200000000 | 4200000000.00 | 4200000000.00 | 4200000000 + 0.000012 | .00 | .00 | .00 + Infinity | Infinity | Infinity | Infinity + -Infinity | - Infinity | - Infinity | - Infinity + NaN | NaN | NaN | NaN +(7 rows) + +WITH v(val) AS + (VALUES('0'::numeric),('-4.2'),('4.2e9'),('1.2e-5'),('inf'),('-inf'),('nan')) +SELECT val, + to_char(val, 'MI99.99') as numeric, + to_char(val::float8, 'MI99.99') as float8, + to_char(val::float4, 'MI99.99') as float4 +FROM v; + val | numeric | float8 | float4 +------------+---------+--------+-------- + 0 | .00 | .00 | .00 + -4.2 | - 4.20 | - 4.20 | - 4.20 + 4200000000 | ##.## | ##.## | ##. + 0.000012 | .00 | .00 | .00 + Infinity | ##.## | ##.## | ##. + -Infinity | -##.## | -##.## | -##. + NaN | ##.## | ##.## | ##.## +(7 rows) + SELECT '' AS to_char_24, to_char('100'::numeric, 'FM999.9'); to_char_24 | to_char ------------+--------- @@ -1426,6 +1996,12 @@ INSERT INTO num_input_test(n1) VALUES ('555.50'); INSERT INTO num_input_test(n1) VALUES ('-555.50'); INSERT INTO num_input_test(n1) VALUES ('NaN '); INSERT INTO num_input_test(n1) VALUES (' nan'); +INSERT INTO num_input_test(n1) VALUES (' inf '); +INSERT INTO num_input_test(n1) VALUES (' +inf '); +INSERT INTO num_input_test(n1) VALUES (' -inf '); +INSERT INTO num_input_test(n1) VALUES (' Infinity '); +INSERT INTO num_input_test(n1) VALUES (' +inFinity '); +INSERT INTO num_input_test(n1) VALUES (' -INFINITY '); -- bad inputs INSERT INTO num_input_test(n1) VALUES (' '); ERROR: invalid input syntax for type numeric: " " @@ -1459,17 +2035,27 @@ INSERT INTO num_input_test(n1) VALUES (' N aN '); ERROR: invalid input syntax for type numeric: " N aN " LINE 1: INSERT INTO num_input_test(n1) VALUES (' N aN '); ^ +INSERT INTO num_input_test(n1) VALUES ('+ infinity'); +ERROR: invalid input syntax for type numeric: "+ infinity" +LINE 1: INSERT INTO num_input_test(n1) VALUES ('+ infinity'); + ^ SELECT * FROM num_input_test; - n1 ---------- - 123 - 3245874 - -93853 - 555.50 - -555.50 - NaN - NaN -(7 rows) + n1 +----------- + 123 + 3245874 + -93853 + 555.50 + -555.50 + NaN + NaN + Infinity + Infinity + -Infinity + Infinity + Infinity + -Infinity +(13 rows) -- -- Test some corner cases for multiplication @@ -1805,6 +2391,24 @@ select exp(1.0::numeric(71,70)); 2.7182818284590452353602874713526624977572470936999595749669676277240766 (1 row) +select exp('nan'::numeric); + exp +----- + NaN +(1 row) + +select exp('inf'::numeric); + exp +---------- + Infinity +(1 row) + +select exp('-inf'::numeric); + exp +----- + 0 +(1 row) + -- cases that used to generate inaccurate results select exp(32.999); exp @@ -1876,6 +2480,12 @@ select * from generate_series('nan'::numeric, 100::numeric, 10::numeric); ERROR: start value cannot be NaN select * from generate_series(0::numeric, 'nan'::numeric, 10::numeric); ERROR: stop value cannot be NaN +select * from generate_series('inf'::numeric, 'inf'::numeric, 10::numeric); +ERROR: start value cannot be infinity +select * from generate_series(0::numeric, 'inf'::numeric, 10::numeric); +ERROR: stop value cannot be infinity +select * from generate_series(0::numeric, '42'::numeric, '-inf'::numeric); +ERROR: step size cannot be infinity -- Checks maximum, output is truncated select (i / (10::numeric ^ 131071))::numeric(1,0) from generate_series(6 * (10::numeric ^ 131071), @@ -2081,6 +2691,12 @@ select scale(numeric 'NaN'); (1 row) +select scale(numeric 'inf'); + scale +------- + +(1 row) + select scale(NULL::numeric); scale ------- @@ -2138,6 +2754,12 @@ select min_scale(numeric 'NaN') is NULL; -- should be true t (1 row) +select min_scale(numeric 'inf') is NULL; -- should be true + ?column? +---------- + t +(1 row) + select min_scale(0); -- no digits min_scale ----------- @@ -2207,6 +2829,12 @@ select trim_scale(numeric 'NaN'); NaN (1 row) +select trim_scale(numeric 'inf'); + trim_scale +------------ + Infinity +(1 row) + select trim_scale(1.120); trim_scale ------------ @@ -2280,7 +2908,11 @@ FROM (VALUES (0::numeric, 0::numeric), (0::numeric, 46375::numeric), (433125::numeric, 46375::numeric), (43312.5::numeric, 4637.5::numeric), - (4331.250::numeric, 463.75000::numeric)) AS v(a, b); + (4331.250::numeric, 463.75000::numeric), + ('inf', '0'), + ('inf', '42'), + ('inf', 'inf') + ) AS v(a, b); a | b | gcd | gcd | gcd | gcd ----------+-----------+---------+---------+---------+--------- 0 | 0 | 0 | 0 | 0 | 0 @@ -2289,7 +2921,10 @@ FROM (VALUES (0::numeric, 0::numeric), 433125 | 46375 | 875 | 875 | 875 | 875 43312.5 | 4637.5 | 87.5 | 87.5 | 87.5 | 87.5 4331.250 | 463.75000 | 8.75000 | 8.75000 | 8.75000 | 8.75000 -(6 rows) + Infinity | 0 | NaN | NaN | NaN | NaN + Infinity | 42 | NaN | NaN | NaN | NaN + Infinity | Infinity | NaN | NaN | NaN | NaN +(9 rows) -- -- Tests for LCM() @@ -2301,7 +2936,11 @@ FROM (VALUES (0::numeric, 0::numeric), (13272::numeric, 13272::numeric), (423282::numeric, 13272::numeric), (42328.2::numeric, 1327.2::numeric), - (4232.820::numeric, 132.72000::numeric)) AS v(a, b); + (4232.820::numeric, 132.72000::numeric), + ('inf', '0'), + ('inf', '42'), + ('inf', 'inf') + ) AS v(a, b); a | b | lcm | lcm | lcm | lcm ----------+-----------+--------------+--------------+--------------+-------------- 0 | 0 | 0 | 0 | 0 | 0 @@ -2311,7 +2950,10 @@ FROM (VALUES (0::numeric, 0::numeric), 423282 | 13272 | 11851896 | 11851896 | 11851896 | 11851896 42328.2 | 1327.2 | 1185189.6 | 1185189.6 | 1185189.6 | 1185189.6 4232.820 | 132.72000 | 118518.96000 | 118518.96000 | 118518.96000 | 118518.96000 -(7 rows) + Infinity | 0 | NaN | NaN | NaN | NaN + Infinity | 42 | NaN | NaN | NaN | NaN + Infinity | Infinity | NaN | NaN | NaN | NaN +(10 rows) SELECT lcm(9999 * (10::numeric)^131068 + (10::numeric^131068 - 1), 2); -- overflow ERROR: value overflows numeric format |