CODEWITHSUNDEEP
cassandracassandra-3.0scylla
Yamiteru XYZ
Yamiteru XYZ

Reputation: 151

Order by in materialized view doesn't sort the results

I have a table with a structure like this:

CREATE TABLE kaefko.se_vi_f55dfeebae00d2b3 (
value text PRIMARY KEY,
id text,
popularity bigint);

With data that looks like this:

value  | id               | popularity
--------+------------------+------------
rally  | 4eff16cb91f96cd6 |          2
reddit | 11aa39686ed66ba5 |          3
red    | 552d7e95af481415 |          1
really | 756bfa499965863c |          1
right  | c5850c6b08f7966b |          1
redis  | 7f1d251f399442d7 |          1

And I've created a materialized view that should sort these values by the popularity from the biggest to the smallest ones:

CREATE MATERIALIZED VIEW kaefko.se_vi_f55dfeebae00d2b3_by_popularity AS
SELECT *
FROM kaefko.se_vi_f55dfeebae00d2b3
WHERE popularity IS NOT null
PRIMARY KEY (value, popularity)
WITH CLUSTERING ORDER BY (popularity DESC);

But the data in the materialized view looks like this:

value  | popularity | id
--------+------------+------------------
rally  |          2 | 4eff16cb91f96cd6
reddit |          3 | 11aa39686ed66ba5
really |          1 | 756bfa499965863c
right  |          1 | c5850c6b08f7966b
redis  |          1 | 7f1d251f399442d7

As you can see there are two main issues:

  1. Data is not sorted as defined in the materialized view
  2. There is just a part of all data in the materialized view

I'm not very experienced in Cassandra and I've already spent hours trying to find the reason why this happens with no avail. Could somebody please help me? Thank you <3

__

I'm using ScyllaDB 4.1.9-0 and cqlsh shows this:

[cqlsh 5.0.1 | Cassandra 3.0.8 | CQL spec 3.3.1 | Native protocol v4]

Upvotes: 3

Views: 1154

Answers (1)

Andrew
Andrew

Reputation: 27294

Alex's comment is 100% correct, the order is within the partition.

PRIMARY KEY (value, popularity)
WITH CLUSTERING ORDER BY (popularity DESC);

This means that the ordering of popularity is descending only for values where the 'value' field is the same - if I was to alter the data you used to show what this would look like as an example, you would get the following:

value  | popularity | id
--------+------------+------------------
rally  |          3 | 4eff16cb91f96cd6
rally  |          2 | 11aa39686ed66ba5
really |          3 | 756bfa499965863c
really |          2 | c5850c6b08f7966b
really |          1 | 7f1d251f399442d7

The order is on a per partition key basis, not globally ordered.

Upvotes: 4

Related Questions