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.
