I just happened to run into a similar situation myself and I can see it's through a bad schema design (and query design) on my part. What I wanted to do was narrow down by the range on one clustering column and then by another range on the next clustering column. Failing to adequately think through how Cassandra stores its sorted rows on disk, I just figured, hey, why not?
The result? The same error message you got. But then, going back over some old notes from a DataStax CQL webinar, I came across this (my words): "You can do selects with combinations of the different primary keys including ranges on individual columns. The range will only work if you've narrowed things down already by equality on all the prior columns. Cassandra creates a composite type to store the column name." My new solution in response. Create two tables: one that's sorted by (in my situation) a high timestamp, the other that's sorted by (in my situation) a low timestamp. What had been two clustering columns gets broken up into one clustering column each in two different tables. Then I do two queries, one with the one range, the other with the other, and I programmatically merge the results. The funny thing is, that was my original design which my most recent, and failed, design is replacing. My new solution goes back to my old solution. On Thu, Apr 7, 2016 at 1:37 AM, Peer, Oded <oded.p...@rsa.com> wrote: > I have a table mapping continuous ranges to discrete values. > > > > CREATE TABLE range_mapping (k int, lower int, upper int, mapped_value int, > PRIMARY KEY (k, lower, upper)); > > INSERT INTO range_mapping (k, lower, upper, mapped_value) VALUES (0, 0, > 99, 0); > > INSERT INTO range_mapping (k, lower, upper, mapped_value) VALUES (0, 100, > 199, 100); > > INSERT INTO range_mapping (k, lower, upper, mapped_value) VALUES (0, 200, > 299, 200); > > > > I then want to query this table to find mapping of a specific value. > > In SQL I would use: *select mapped_value from range_mapping where k=0 and > ? between lower and upper* > > > > If the variable is bound to the value 150 then the mapped_value returned > is 100. > > > > I can’t use the same type of query in CQL. > > Using the query “*select * from range_mapping where k = 0 and lower <= > 150 and upper >= 150;*” returns an error "Clustering column "upper" > cannot be restricted (preceding column "lower" is restricted by a non-EQ > relation)" > > > > I thought of using multi-column restrictions but they don’t work as I > expected as the following query returns two rows instead of the one I > expected: > > > > *select * from range_mapping where k = 0 and (lower,upper) <= (150,999) > and (lower,upper) >= (-999,150);* > > > > k | lower | upper | mapped_value > > ---+-------+-------+-------------- > > 0 | 0 | 99 | 0 > > 0 | 100 | 199 | 100 > > > > I’d appreciate any thoughts on the subject. > > >
