I have a query as such
SELECT "deviceId", "transactionStatus"
FROM scp_service_transaction.transactions_v2
WHERE "tenantId" = 'aptos-denim'
AND TRY_CAST("deviceId" AS BIGINT) >= 100
AND TRY_CAST("deviceId" AS BIGINT) <= 150
ORDER BY "endDateTime" desc LIMIT 20 OFFSET 0;
My data set looks like this
[
{deviceId: 123, transactionStatus: object },
{deviceId: 1015, transactionStatus: object }
]
My resulting query returns back deviceId with values of 123
and 1015
while I am expecting only 123
to be returned. Why is my query giving me both 123 and 1015 back when I specified the range that I want my deviceId to be in?
I assume that deviceId
is a TEXT
field.
There is a bug that has been fixed with CrateDB 4.7.1 / 4.6.8, which applied swap cast optimisation when explicit cast ares used. This leads to ignoring the cast and text being compared as text.
Both versions are available on the testing channels and will most likely be promoted stable within the next week.