Cassandra - select query with token() function

According to this documentation, I was trying a select query with token() function in it, but it gives wrong results.

I am using below cassandra version

[cqlsh 5.0.1 | Cassandra 2.2.5 | CQL spec 3.3.1 | Native protocol v4]

I was trying token query for below table -

CREATE TABLE price_key_test (
    objectid int,
    createdOn bigint,
    price int,
    foo text,
    PRIMARY KEY ((objectid, createdOn), price));

Inserted data --

insert into nasa.price_key_test (objectid,createdOn,price,foo) values (1,1000,100,'x');
insert into nasa.price_key_test (objectid,createdOn,price,foo) values (1,2000,200,'x');
insert into nasa.price_key_test (objectid,createdOn,price,foo) values (1,3000,300,'x');

Data in table --

 objectid | createdon | price | foo
----------+-----------+-------+-----
        1 |      3000 |   300 |   x
        1 |      2000 |   200 |   x
        1 |      1000 |   100 |   x

Select query is --

select * from nasa.price_key_test
    where token(objectid,createdOn) > token(1,1000)
      and token(objectid,createdOn) < token(1,3000);

This query suppose to return row with createdOn 2000, but it returns zero rows.

 objectid | createdon | price | foo
----------+-----------+-------+-----

(0 rows)

According to my understanding, token(objectid,createdOn) > token(1,1000) and token(objectid,createdOn) < token(1,3000) should select row with partition key with value 1 and 2000.

Is my understanding correct?

Solution

Try flipping your greater/less-than signs around:

aploetz@cqlsh:stackoverflow> SELECT * FROM price_key_test 
    WHERE token(objectid,createdOn) < token(1,1000) 
    AND token(objectid,createdOn) > token(1,3000) ;

 objectid | createdon | price | foo
----------+-----------+-------+-----
        1 |      2000 |   200 |   x

(1 rows)

Adding the token() function to your SELECT should help you to understand why:

aploetz@cqlsh:stackoverflow> SELECT objectid, createdon, token(objectid,createdon), 
    price, foo FROM price_key_test ;

 objectid | createdon | system.token(objectid, createdon) | price | foo
----------+-----------+-----------------------------------+-------+-----
        1 |      3000 |              -8449493444802114536 |   300 |   x
        1 |      2000 |              -2885017981309686341 |   200 |   x
        1 |      1000 |              -1219246892563628877 |   100 |   x

(3 rows)

The hashed token values generated are not necessarily proportional to their original numeric values. In your case, token(1,3000) generated a hash that was the smallest of the three, and not the largest.