How does implicit casting work in Oracle NoSQL Database?

Question

I am trying to understand the implicit cast behavior. I have a column called ticketNo, this is a string and it is a pk. Using the same datatype in both sides, I am returning one row

SELECT * FROM demo d  WHERE ticketNo = "1762386738153"

When I am doing a explicit cast, this query is returning the same row

SELECT * FROM demo d  WHERE cast (ticketNo as Long)= 1762386738153

Now, when I am doing an implicit cast, this query is returning no rows

SELECT * FROM demo d  WHERE ticketNo = 1762386738153

Any ideas ?

Answer 1

There is no implicit cast behavior in Oracle NoSQL Database. String types are not comparable to Long types so the predicate ticketNo = 1762386738153 returns always false in your case. A string item is comparable to another string item. A string item is also comparable to an enum item.

In your case, this is your primary key, in order to have the best performances, it is not recommended to do a CAST. Validate the types before do this query. A primary key is always typed, no wildcard or complex types are accepted

Otherwise,

the reason for returning false for incomparable items, instead of raising an error, is to handle truly schemaless applications, where different table rows may contain very different data or differently shaped data. As a result, even the writer of the query may not know what kind of items an operand may return and an operand may indeed return different kinds of items from different rows.

you can always execute the explicit CAST operation when needed, as you did.

If you are interested in have more information : https://docs.oracle.com/en/database/other-databases/nosql-database/20.3/sqlreferencefornosql/value-comparison-operators.html