cockroachdb / cockroachdb/cockroach

sql: decimal precision differences vs postgres

Open
#115,679 1 comment 0 reactions 0 assignees View on GitHub
A-sql-pgcompat C-bug P-3 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

This issue tracks decimal precision differences that remain between postgres and CRDB. Using an example query:
```
WITH v(id, x) AS (VALUES (1, '0'::numeric), (2, '1'::numeric), (3, '-1'::numeric),
(4, '4.2'::numeric), (5, 'inf'::numeric), (6, '-inf'::numeric), (7, 'nan'::numeric)
)
SELECT x1, x2,
x1 / x2 AS quot,
x1 % x2 AS mod,
div(x1, x2) AS div
FROM v AS v1(id1, x1), v AS v2(id2, x2) WHERE x2 != 0
ORDER BY id1, id2
```
Here is a diff using the postgres output as base.
```
x1 | x2 | quot | mod | div
------------+-----------+-------------------------+------+-----------
- 0 | 1 | 0.00000000000000000000 | 0 | 0
- 0 | -1 | 0.00000000000000000000 | 0 | 0
- 0 | 4.2 | 0.00000000000000000000 | 0.0 | 0
+------------+-----------+-------------------------+------+------------
+ 0 | 1 | 0 | 0 | 0
+ 0 | -1 | -0 | 0 | -0
+ 0 | 4.2 | 0E+1 | 0.0 | 0
0 | Infinity | 0 | 0 | 0
- 0 | -Infinity | 0 | 0 | 0
+ 0 | -Infinity | 0 | 0 | -0
0 | NaN | NaN | NaN | NaN
- 1 | 1 | 1.00000000000000000000 | 0 | 1
- 1 | -1 | -1.00000000000000000000 | 0 | -1
+ 1 | 1 | 1.0000000000000000000 | 0 | 1
+ 1 | -1 | -1.0000000000000000000 | 0 | -1
1 | 4.2 | 0.23809523809523809524 | 1.0 | 0
1 | Infinity | 0 | 1 | 0
- 1 | -Infinity | 0 | 1 | 0
+ 1 | -Infinity | 0 | 1 | -0
1 | NaN | NaN | NaN | NaN
- -1 | 1 | -1.00000000000000000000 | 0 | -1
- -1 | -1 | 1.00000000000000000000 | 0 | 1
- -1 | 4.2 | -0.23809523809523809524 | -1.0 | 0
- -1 | Infinity | 0 | -1 | 0
+ -1 | 1 | -1.0000000000000000000 | -0 | -1
+ -1 | -1 | 1.0000000000000000000 | -0 | 1
+ -1 | 4.2 | -0.23809523809523809524 | -1.0 | -0
+ -1 | Infinity | 0 | -1 | -0
-1 | -Infinity | 0 | -1 | 0
-1 | NaN | NaN | NaN | NaN
- 4.2 | 1 | 4.2000000000000000 | 0.2 | 4
- 4.2 | -1 | -4.2000000000000000 | 0.2 | -4
- 4.2 | 4.2 | 1.00000000000000000000 | 0.0 | 1
+ 4.2 | 1 | 4.2000000000000000000 | 0.2 | 4
+ 4.2 | -1 | -4.2000000000000000000 | 0.2 | -4
+ 4.2 | 4.2 | 1.0000000000000000000 | 0.0 | 1
4.2 | Infinity | 0 | 4.2 | 0
- 4.2 | -Infinity | 0 | 4.2 | 0
+ 4.2 | -Infinity | 0 | 4.2 | -0
4.2 | NaN | NaN | NaN | NaN
Infinity | 1 | Infinity | NaN | Infinity
Infinity | -1 | -Infinity | NaN | -Infinity
```

Jira issue: CRDB-34150

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.