-1

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?

danronmoon
  • 3,814
  • 5
  • 34
  • 56
The Coder
  • 293
  • 2
  • 15
  • Edited to make it more clear this is a question about CrateDB. You tagged the question [tag:mysql], but MySQL does not have a `TRY_CAST()` function. – Bill Karwin Mar 11 '22 at 20:38
  • 1
    Generally you should adapt your schema to use `BIGINT` for `deviceID`, as this casting would always lead to a table scan, which one would want to avoid. If only some values are of numeric value a generated column, that try_casts numeric values could also help. – proddata Mar 12 '22 at 20:21

1 Answers1

1

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

proddata
  • 216
  • 1
  • 7
  • Thanks for the reply. Is there any way to modify my query? – The Coder Mar 12 '22 at 14:19
  • I posted some workarounds here: https://community.crate.io/t/incorrect-result-with-try-cast-function/1014/6?u=proddata ```sql CREATE OR REPLACE VIEW v_transactions_v2 AS SELECT try_cast(deviceId as long)+0 AS deviceId FROM v_transactions_v2; ``` – proddata Mar 12 '22 at 20:18