Search code examples
cratedb

CrateDB statement returning unexpected results during AND comparison

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?

Solution

  • 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.

    https://github.com/crate/crate/issues/12135