Thanks for responding. I was able to hack it out eventually. I removed the ‘upper’ column from the PK so it is no longer a clustering column, and I added a secondary index over it. This assumes there is no overlap in the a continuous ranges. I had to add a ‘dummy’ column with a constant value to be able to use an non-eq operator on the ‘upper’ column. Now that it’s not a clustering column and it has an index I can use the following map a continuous range to a discrete value.
CREATE TABLE range_mapping (k int, lower int, upper int, dummy int, mapped_value int, PRIMARY KEY (k, lower)); CREATE INDEX upper_index on range_mapping(upper); CREATE INDEX dummy_index on range_mapping(dummy); INSERT INTO range_mapping (k, dummy, lower, upper, mapped_value) VALUES (0, 0, 0, 99, 0); INSERT INTO range_mapping (k, dummy, lower, upper, mapped_value) VALUES (0, 0, 100, 199, 100); INSERT INTO range_mapping (k, dummy, lower, upper, mapped_value) VALUES (0, 0, 200, 299, 200); Now for the query: select * from range_mapping where k = 0 and dummy = 0 and lower <= 150 and upper >= 150 allow filtering; k | lower | dummy | mapped_value | upper ---+-------+-------+--------------+------- 0 | 100 | 0 | 100 | 199 Oded From: Henry M [mailto:henrymanm...@gmail.com] Sent: Friday, April 08, 2016 6:38 AM To: user@cassandra.apache.org Subject: Re: Mapping a continuous range to a discrete value 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<mailto: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<mailto: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.
