cockroachdb / cockroachdb/cockroach

sql: sqrt function edge cases mismatch with postgres

Open
#100,963 2 comments 0 reactions 0 assignees View on GitHub
C-bug O-community T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

there is a couple of edge cases of sqrt function that is handled by postgres but that is handled differently with cockroach db.
with float infinity, float nan, and negative 0.

```
postgres=# select sqrt('inf')
postgres-# ;
sqrt
----------
Infinity
(1 row)

postgres=# select sqrt('nan');
sqrt
------
NaN
(1 row)

postgres=# select sqrt('-0');
sqrt
------
-0
(1 row)

```

**To Reproduce**
on lastest cockroach demo
```
demo@127.0.0.1:26257/movr> SELECT sqrt('inf');
ERROR: ambiguous call: sqrt(string), candidates are:
sqrt(float) -> float
sqrt(decimal) -> decimal
SQLSTATE: 42725
demo@127.0.0.1:26257/movr> SELECT sqrt('nan');
ERROR: ambiguous call: sqrt(string), candidates are:
sqrt(float) -> float
sqrt(decimal) -> decimal
SQLSTATE: 42725
demo@127.0.0.1:26257/movr> SELECT sqrt(-0);
ERROR: ambiguous call: sqrt(int), candidates are:
sqrt(float) -> float
sqrt(decimal) -> decimal
SQLSTATE: 42725
demo@127.0.0.1:26257/movr> SELECT sqrt(-0.0);
sqrt
--------
0
(1 row)
```

**Environment:**
- CockroachDB version [latest 23.1]
- Server OS: all

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.