`IN` uses different NaN equality semantics from the `=` operator

[shulei5831sl]

ArcadeDB version

ArcadeDB 26.8.1-SNAPSHOT
Build: aea22a9bf30aef3ef392b535cc44b7480d4ccecc
Docker image: arcadedata/arcadedb:latest
Image ID: 880324d548aa

Environment

• Host OS: Linux x86_64
• Deployment: Docker
• Query interface: HTTP command API
• Query language: Cypher

Description

ArcadeDB treats NaN as matching another NaN when evaluating the IN operator, even though its ordinary equality operator does not consider NaN equal to itself.

This produces inconsistent comparison semantics:

NaN = NaN       -> false
NaN IN [NaN]    -> true

List membership should use equality semantics consistent with the = operator. If no list element compares equal to the left-hand operand, IN should return false.

Steps to reproduce

No graph data is required.

RETURN
  sqrt(-1) = sqrt(-1) AS equalsResult,
  sqrt(-1) IN [sqrt(-1)] AS inResult;

Actual result:

equalsResult = false
inResult = true

Expected behavior

equalsResult = false
inResult = false

Because ArcadeDB's = operator does not consider NaN equal to NaN, a list containing NaN should not contain an element equal to another NaN under the same comparison semantics.

Actual behavior

The IN operator reports a match even though the equivalent equality comparison returns false.

Stored-value control

The same equality behavior occurs when NaN is stored as a property:

MATCH (n) DETACH DELETE n;

CREATE (:NaNTest {value: sqrt(-1)});

MATCH (n:NaNTest)
WHERE n.value = n.value
RETURN n.value;

Actual result:

0 rows

This confirms that ordinary equality does not consider the stored NaN value equal to itself.

Non-NaN list control

RETURN sqrt(-1) IN [1.0, 2.0, 3.0] AS result;

Expected and actual result:

false

The incorrect membership result occurs specifically when the list contains another NaN value.

Cross-engine diagnostic comparison

Expression	ArcadeDB 26.8.1-SNAPSHOT	Neo4j	Memgraph
sqrt(-1) = sqrt(-1)	false	false	false
sqrt(-1) IN [sqrt(-1)]	true	false	false

The expected behavior is primarily based on ArcadeDB's internal inconsistency between = and IN, rather than solely on cross-engine behavior.

Reproducibility

Reproduced consistently in a fresh Docker container.

Impact

Queries using NaN values in membership expressions can incorrectly include rows that do not satisfy the equivalent equality comparison.

Affected patterns include:

• WHERE value IN list
• Duplicate and exclusion checks
• Conditional expressions
• Lists containing calculated floating-point results

[lvca]

Thanks for the report, and especially for framing it as an internal inconsistency - that was exactly the right diagnosis.

Confirmed and fixed in 92ad120 (main, next release).

The cause: IN had its own equality check, separate from the one = uses. It fell back to Java's notion of equality before looking at the types, and Java considers NaN equal to itself. So IN and = disagreed.

The fix removes that duplicate: IN now asks the = operator whether an element matches. Membership is defined in terms of equality, so the two can no longer drift apart. sqrt(-1) IN [sqrt(-1)] now returns false, matching both = and Neo4j/Memgraph.

Two related bugs went away with the same change, since they had the same root cause of borrowing Java's equality rules:

• {a: null} IN [{a: null}] returned true; it now returns null, like {a: null} = {a: null}.
• A datetime passed as a query parameter would not match a stored datetime under IN, though it did under =.

Verified against the full openCypher TCK conformance suite.

One thing you may run into: x NOT IN [...] does not currently parse outside a WHERE clause - in a RETURN, write NOT (x IN [...]). That is a separate gap and I have noted it for a follow-up.

---

❤️ Thanks for helping make ArcadeDB better! ArcadeDB is free and open source, sustained by its community. If it's useful to you or your company, please consider becoming a sponsor to keep development going.