Search code examples
cratecratedb

CRATE DB: WHERE IN on timestamp data results in NullPointerException

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.

Solution

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