I had to do something similar (in my case it was an IN query)... I ended up writing hack in java to create a custom Expression and injecting into the RowFilter of a dummy secondary index (not advisable and very short term but it keeps my application code clean). I am keeping my eyes open for the evolution of SASI indexes (starting with cassandra 3.4 https://github.com/apache/cassandra/blob/trunk/doc/SASI.md) which should do what you are looking.
On Thu, Apr 7, 2016 at 11:06 AM Mitch Gitman <mgit...@gmail.com> wrote: > 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. >> >> >> > >
