cockroachdb / cockroachdb/cockroach

sql: edge cases in math builtins

Open
#114,872 1 comment 0 reactions 0 assignees View on GitHub
A-sql-builtins A-sql-pgcompat C-bug E-quick-win O-pg-regress P-3 T-sql-queries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.