CRATE DB: WHERE IN on timestamp data results in NullPointerException

Question

The following select doesn't work:

 SELECT * FROM "myschema"."timetable" WHERE "start_time" in (1519705800000, 1519710840000, 1519705800000, 1519718400000)

Start_time is a timestamp column. Crate responds with:

Error!

SQLActionException[UnhandledServerException: java.lang.NullPointerException]

Is WHERE IN not working on timestamps? Is there an alternative? The crate version is 2.1.6.

As mentioned, CRATE version 2.1.6 – Alex J Gr Jan 29 '18 at 15:55 — Alex J Gr, Jan 29 '18 at 15:55

score 0 · Answer 1 · answered Jan 29 '18 at 21:14

0

i can't confirm this behaviour on a CrateDB instance with the latest version (2.3.2).

Can you try with the latest version? Otherwise post your schema and some sample data to reproduce?

answered Jan 29 '18 at 21:14

Jodok Batlogg

I'm not allowed to upgrade because our system is in an advanced development state and it's quite stable for now with 2.1.6. Last try to upgrade CRATE further resulted in some other selects not working anymore, probably due to deprecated stuff that we don't have the time to look into, so I'll use the "or" approach for now. We'll look back again on these issues when we get some time for refactoring, but thank you for your answer :) – Alex J Gr Jan 30 '18 at 09:56

score 0 · Accepted Answer · answered Jan 30 '18 at 17:02

This bug has been fixed in CrateDB 2.1.9, see https://crate.io/docs/crate/reference/en/2.1/release_notes/2.1.9.html#fixes. Please consider upgrading to 2.1.9, as it is only a patch version and shouldn't affect your development state.

That said, there is a workaround. You have to cast the timestamp column to long and it will work fine:

SELECT * FROM "myschema"."timetable" WHERE "start_time"::long in (1519705800000, 1519710840000, 1519705800000, 1519718400000);

This works because timestamps are internally represented as longs, so you won't loose any precision.

2 Answers2