sql: invalid argument to `asin()`, `acosd()`, and `asind()` results in NaN instead of error

[dddddddcoconut]

Describe the problem

The query returns duplicated rows and repeated columns when using SELECT DISTINCT together with GROUP BY on a single column, while also containing an ANY predicate involving ASIND().

Although only one grouping key (t0.c0) is used, the result set contains repeated identical columns and multiple rows that appear to be derived solely from a cross join with t59, even though t59 does not contribute to projection.

This suggests incorrect interaction between:

• DISTINCT
• GROUP BY
• ANY subquery expression
• ASIND() function rewrite or evaluation

To Reproduce

DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t59;

CREATE TABLE t0 (
c0 TIMESTAMP,
c1 FLOAT
);

CREATE TABLE t59 ( x INT);

INSERT INTO t0 VALUES
('1969-12-24 01:54:36+08', 0.18),
('1969-12-13 01:19:22+08', NULL),
('1970-01-01 00:00:00+08', 1.2), -- out of domain
('1970-01-02 00:00:00+08', -1.2); -- out of domain

INSERT INTO t59 VALUES (1);

SELECT DISTINCT t0.c0, t0.c0, t0.c0
FROM t0, t59
WHERE (0.18064210411325599 > ANY (t0.c1, ASIND(t0.c1)))
GROUP BY t0.c0
LIMIT 10;

Expected behavior
Each t0.c0 value should appear at most once due to GROUP BY t0.c0 and DISTINCT.
Output columns should not be duplicated (c0, c0, c0 should be normalized or collapsed).
The presence of t59 (which is not referenced in projection or filter logically affecting grouping) should not affect result multiplicity.
The ANY condition involving ASIND(t0.c1) should not introduce unintended row multiplication or rewrite inconsistencies.

Expected result should be logically equivalent to:

c0	c0	c0
1969-12-24 01:54:36.000000	1969-12-24 01:54:36.000000	1969-12-24 01:54:36.000000
1970-01-02 00:00:00.000000	1970-01-02 00:00:00.000000	1970-01-02 00:00:00.000000

Additional data / screenshots
Actual output:

c0	c0	c0
1969-12-24 01:54:36.000000	1969-12-24 01:54:36.000000	1969-12-24 01:54:36.000000
1970-01-01 00:00:00.000000	1970-01-01 00:00:00.000000	1970-01-01 00:00:00.000000
1970-01-02 00:00:00.000000	1970-01-02 00:00:00.000000	1970-01-02 00:00:00.000000

Environment:
Impact:

• Potential incorrect query rewrite or optimization involving scalar functions (ASIND) inside ANY
• Possible interaction bug between:
  scalar function evaluation
  join planning (FROM t0, t59)
  aggregation (GROUP BY)
  DISTINCT projection normalization

This may lead to incorrect result shaping and unexpected column duplication in query output, which is especially problematic for ORM / JDBC clients relying on stable schema inference.

Jira issue: CRDB-63031

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: DROP TABLE,ORM,ORM)
• @cockroachdb/sql-queries (found keywords: plan)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[DrewKimball]

Thanks for the report! It's strange, we compare finite float values as being larger than NaN:

root@localhost:26257/defaultdb> CREATE TABLE t (x FLOAT);
CREATE TABLE

Time: 30ms total (execution 11ms / network 18ms)

root@localhost:26257/defaultdb> INSERT INTO t VALUES ('NaN');
INSERT 0 1

Time: 5ms total (execution 5ms / network 0ms)

root@localhost:26257/defaultdb> SELECT 0.1 > x FROM t;
  ?column?
------------
     t
(1 row)

Time: 2ms total (execution 2ms / network 0ms)

root@localhost:26257/defaultdb> SELECT 0.1 < x FROM t;
  ?column?
------------
     f
(1 row)

Time: 2ms total (execution 2ms / network 0ms)

root@localhost:26257/defaultdb> SELECT 'NaN' = x FROM t;
  ?column?
------------
     t
(1 row)

Time: 3ms total (execution 3ms / network 0ms)

Postgres does the opposite:

postgres=# CREATE TABLE t (x FLOAT);
CREATE TABLE
postgres=# INSERT INTO t VALUES ('NaN');
INSERT 0 1
postgres=# SELECT 0.1 > x FROM t;
 ?column?
----------
 f
(1 row)

postgres=# SELECT 0.1 < x FROM t;
 ?column?
----------
 t
(1 row)

postgres=# SELECT 'NaN' = x FROM t;
 ?column?
----------
 t
(1 row)

Both seem wrong to me. Postgres docs say:

In most implementations of the “not-a-number” concept, NaN is not considered equal to any other numeric value (including NaN). In order to allow numeric values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.

So it seems like a bit of an arbitrary choice made for the implementation. We already have an issue open for the difference in NaN comparison vs Postgres: #48020.

[DrewKimball]

I think the other difference vs Postgres is that they return an error for invalid input to ASIND:

postgres=# select ASIND(-1.2);
ERROR:  input is out of range

Whereas we get NaN.

[bghal]

A bit more details since it affects asin(), acosd(), and asind().

postgres=# SELECT ACOS(4.05620741E8) FROM t0;
ERROR:  input is out of range

cockroach> SELECT ACOS(4.05620741E8) FROM t0;                                       
  acos
--------
   NaN
   NaN
   NaN
   NaN
(4 rows)