Getting nothing when selecting more than 1 time interval

CrateDB
1

Creating a partitioned table like this:

CREATE TABLE IF NOT EXISTS "doc"."events_2" ( "timestamp" TIMESTAMP WITH TIME ZONE, "week" TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('week', "timestamp"), "some_uid" BIGINT INDEX OFF, PRIMARY KEY ("week", "*  some_uid *") )

If more than one interval is selected, it returns 0, although each of the intervals contains some value.

select count(*) as count  from events_2 where ((timestamp >= '2020-06-25T22:00:00.000Z' and timestamp < '2020-06-26T07:07:00.000Z') OR (timestamp >= '2020-06-08T22:00:00.000Z' and timestamp < '2020-06-12T07:07:00.000Z')) limit 100;

In the documentation, we have this “limitation:” WHERE clauses cannot contain queries like partitioned_by_column='x' OR normal_column=x

But there is nothing about selecting more than one interval.

2 

+-----------------------------------------------------------------------------------------+
| SHOW CREATE TABLE doc.events_3                                                          |
+-----------------------------------------------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "doc"."events_3" (                                           |
|    "timestamp" TIMESTAMP WITH TIME ZONE,                                                |
|    "week" TIMESTAMP WITH TIME ZONE GENERATED ALWAYS AS date_trunc('week', "timestamp"), |
|    "some_uid" TEXT INDEX OFF,                                                           |
|    PRIMARY KEY ("week", "some_uid")                                                     |
| )                                                                                       |
| CLUSTERED INTO 4 SHARDS                                                                 |
| PARTITIONED BY ("week")                                                                 |
+-----------------------------------------------------------------------------------------+

generated some records

mkjson --num 2  timestamp="randomDateTime('2020-04-01 00:00:00','2020-04-30 23:59:59')" some_uid="uuid4()" | cr8 insert-json --host localhost:4200 --table events_3

cr> select * from events_3;
+---------------+---------------+--------------------------------------+
|     timestamp |          week | some_uid                             |
+---------------+---------------+--------------------------------------+
| 1586758624000 | 1586736000000 | e12b3188-2ef2-4c1b-af89-b326cb3b7d2f |
| 1587341088000 | 1587340800000 | 3b1c19be-5346-4742-8220-a3edaf3218e5 |
+---------------+---------------+--------------------------------------+

…run the query

 select *   from events_3 where
       (timestamp >= '1586736000000' and timestamp < '1586758625000') or
       (timestamp >= '1587340800000' and timestamp < '1587341089000');
+---------------+---------------+--------------------------------------+
|     timestamp |          week | some_uid                             |
+---------------+---------------+--------------------------------------+
| 1586758624000 | 1586736000000 | e12b3188-2ef2-4c1b-af89-b326cb3b7d2f |
| 1587341088000 | 1587340800000 | 3b1c19be-5346-4742-8220-a3edaf3218e5 |
+---------------+---------------+--------------------------------------+

I guess I missed the point. Please let me know.

3

what is the idea of having INDEX OFF on some_uid and using it as a primary key?

cr> select * from events_3 where timestamp = '1586758624000' and some_uid = 'e12b3188-2ef2-4c1b-af89-b326cb3b7d2f';
SQLActionException[UnhandledServerException: java.lang.IllegalArgumentException: Cannot search on field [some_uid] since it is not indexed.]