cockroachdb / cockroachdb/cockroach
sql: edge cases in math builtins
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
The following math builtin edge cases have the wrong results:
```
SELECT power('0'::numeric, '-1');
-- crdb: Infinity
-- postgres: ERROR: zero raised to a negative power is undefined
SELECT power('0'::numeric, '-inf');
-- crdb: Infinity
-- postgres: ERROR: zero raised to a negative power is undefined
SELECT power('-2'::numeric, '3.3');
-- crdb: NaN
-- postgres: ERROR: a negative number raised to a non-integer power yields a complex result
SELECT power('-2'::numeric, '-1.5');
-- crdb: NaN
-- postgres: ERROR: a negative number raised to a non-integer power yields a complex result
SELECT power('-2'::numeric, 'inf');
-- crdb: 1
-- postgres: Infinity
SELECT power('-2'::numeric, '-inf');
-- crdb: 1.0000000000000000000
-- postgres: 0
SELECT power('-inf'::numeric, '4.5');
-- crdb: NaN
-- postgres: ERROR: a negative number raised to a non-integer power yields a complex result
SELECT power('-inf'::numeric, 'inf');
--crdb: NaN
-- postgres: Infinity
SELECT power('-inf'::numeric, '-inf');
--crdb: NaN
-- postgres: 0
SELECT width_bucket('NaN', 3.0, 4.0, 888);
--crdb: -9223372036854775808
--postgres: ERROR: operand, lower bound, and upper bound cannot be NaN
SELECT width_bucket('Infinity'::numeric, 1, 10, 10);
-- crdb: ERROR: width_bucket(): operand, lower bound, and upper bound cannot be infinity
-- postgres: 11
width_bucket('-Infinity'::numeric, 1, 10, 10);
-- crdb: ERROR: width_bucket(): operand, lower bound, and upper bound cannot be infinity
-- postgres: 0
select coalesce(nullif(exp(-5000::numeric), 0), 0) as rounds_to_zero;
-- crdb: ERROR: exp(): integer power: underflow, subnormal
-- postgres: 0
select 10.0 ^ -2147483648 as rounds_to_zero;
-- crdb: ERROR: exponent out of range
-- postgres: 0.00000000000
select coalesce(nullif(0.9999999999 ^ 70000000000000, 0), 0) as underflows;
-- crdb: ERROR: underflow, subnormal
-- postgrs: 0
select n, 10.0 ^ n as "10^n", (10.0 ^ n) * (10.0 ^ (-n)) = 1 as ok from (values (1)) x(n);
-- crdb: ERROR: integer out of range
-- postgres: success from values -20 to 20
select 'NaN'::numeric ^ 0;
-- crdb: NaN
-- postgres: 1
select 1 ^ 'NaN'::numeric;
-- crdb: NaN
-- postgres: 1
select coalesce(nullif(exp(-10000::numeric), 0), 0) as underflows;
-- crdb: ERROR: exp(): integer power: underflow, subnormal
-- postgres: 0
```
Found in pg_regress numeric
Jira issue: CRDB-33728
Contributor guide
Assessment
This issue has not been assessed yet.