Cassandra materialized view won't let me filter

Question

I have created a materialized view:

CREATE MATERIALIZED VIEW test_view AS SELECT state, city, zip, loc, pop FROM citylist WHERE state IS NOT NULL AND city IS NOT NULL AND zip IS NOT NULL AND pop IS NOT NULL
PRIMARY KEY (state,city,pop,zip) WITH CLUSTERING ORDER BY (state ASC).

and I get the results from the query "SELECT * FROM test_view Where state='NY'LIMIT 15;" as:

state | city         | pop   | zip   | loc
-------+--------------+-------+-------+-------------------------
    NY |       ACCORD |  2695 | 12404 | [-74.235336, 41.808308]
    NY |         ACRA |   525 | 12405 | [-74.085723, 42.330367]
    NY |  ADAMS BASIN |   989 | 14410 | [-77.853905, 43.190644]
    NY | ADAMS CENTER |  2649 | 13606 |  [-76.00415, 43.863106]
    NY |      ADDISON |  4967 | 14801 |  [-77.266027, 42.09825]
    NY |   ADIRONDACK |   105 | 12808 | [-73.782486, 43.716479]
    NY |        AFTON |  2801 | 13730 | [-75.536604, 42.241737]
    NY |        AKRON |  7924 | 14001 | [-78.508365, 43.024944]
    NY |      ALABAMA |    68 | 14003 | [-78.385231, 43.071888]
    NY |       ALBANY |  2709 | 12207 | [-73.752327, 42.658133]
    NY |       ALBANY |  6927 | 12204 | [-73.735364, 42.684667]
    NY |       ALBANY |  9374 | 12210 |   [-73.76052, 42.65677]
    NY |       ALBANY | 10008 | 12209 | [-73.785385, 42.641665]
    NY |       ALBANY | 11097 | 12202 | [-73.764071, 42.641314]
    NY |       ALBANY | 17230 | 12206 | [-73.774406, 42.668326]

Now, when I add another condition filtering statement to make the query:

SELECT * FROM test_view Where state='NY' AND pop > 2000 ORDER BY city ASC LIMIT 15;

I get the error:

InvalidRequest: Error from server: code=2200 [Invalid query] message="PRIMARY KEY column "pop" cannot be restricted as preceding column "city" is not restricted"

I want to be able to keep the ordering of the entries in the table/results above, but also allow filtering using "pop". How will I edit my materialized view statement to do so?

Answer 1

CREATE MATERIALIZED VIEW test_view AS SELECT state, city, zip, loc, pop FROM citylist WHERE state IS NOT NULL AND city IS NOT NULL AND zip IS NOT NULL AND pop IS NOT NULL
PRIMARY KEY (state,pop,city,zip) WITH CLUSTERING ORDER BY (state ASC).

The order in which the keys are created does matter - you cannot restrict with a column without restricting the previous column in the compound key list (without allow filtering and which anyway is not a good idea)

Answer 2

If you are not going to query using state and city both then you can change your view to one shown below.

CREATE MATERIALIZED VIEW test_view AS SELECT state, city, zip, loc, pop FROM citylist WHERE state IS NOT NULL AND city IS NOT NULL AND zip IS NOT NULL AND pop IS NOT NULL PRIMARY KEY (state,pop,city,zip) WITH CLUSTERING ORDER BY (pop ASC, city ASC)

Then you can query like this SELECT * FROM test_view Where state='NY' AND pop > 2000 ORDER BY pop, city ASC LIMIT 15;

With above view it will not retain the order of cities for a state. It will be the order of population for cities instead of by name of cities for a given state. If you want to retain the ordering by city names within the state then you will have to specify city along with state when filtering by population. Else filter by population but sacrifice ordering by city names.

The reason you are getting error is because you can only query in the order of clustering columns after specifying partition key. You can not omit a preceding column in the clustering key order when filtering by clustering column.