cockroachdb / cockroachdb/cockroach
sql: decimal precision differences vs postgres
- 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
Assessment
This issue has not been assessed yet.