1

I am writing a parameterized CQL statement that is not behaving as I would expect. I am trying to return a list of records for a range of dates that in this instance are using the same date for the beginning and end points.

The table is defined as follows:

CREATE TABLE sometable (
partition_key varchar,
cluster_start_date timestamp,
other_cluster_key varchar,
data varchar,
PRIMARY KEY((partition_key), cluster_start_date, other_cluster_key)
) WITH CLUSTERING ORDER BY(cluster_start_date ASC, other_cluster_key ASC);

The prepared statement is as follows:

SELECT * FROM sometable WHERE partition_key = 'xxx' AND cluster_start_date  >= ? AND cluster_start_date <= ?;

When the parameterized dates are different, the statement returns the correct data. But when the parameterized dates are the same, then no values are returned.

Could someone tell me why this statement does not work with identical parameters, and if so, what can be done to get the expected behavior?

Thanks for all who can help....

piper1970
  • 518
  • 1
  • 3
  • 14
  • Presumably, no rows have exactly the timestamp provided when the parameters are equal. – Gordon Linoff Aug 22 '15 at 03:34
  • the parameters were being converted to UTC Date from Java8 Localdates. However, it turns out the problem wasn't with Cassandra but another portion of my logic. The query did work, I just wasn't enumerating my map correctly with the response data. – piper1970 Aug 22 '15 at 15:26

1 Answers1

2

You didn't show how you are specifying the timestamp value in your query, but I'm guessing you are specifying it in seconds. Internally the resoultion of timestamp is higher than seconds.

If you specify a timestamp in seconds, then your lower bound would be rounded down to 000 for the milliseconds, and your upper bound would also be rounded down to 000 milliseconds. It's likely the row you expect to see does not have 000 for the milliseconds, and so it is outside the tiny 1 millisecond wide range you specified. For example:

SELECT * from sometable ;

 partition_key | cluster_start_date       | other_cluster_key | data
---------------+--------------------------+-------------------+------
             a | 2015-08-22 06:16:38-0400 |                 a | row1
             a | 2015-08-22 06:17:09-0400 |                 b | row2
             a | 2015-08-22 06:17:31-0400 |                 c | row3

SELECT * FROM sometable WHERE partition_key = 'a' 
    and cluster_start_date  >= '2015-08-22 06:17:09-0400' 
    and cluster_start_date  <= '2015-08-22 06:17:09-0400';

 partition_key | cluster_start_date | other_cluster_key | data
---------------+--------------------+-------------------+------

(0 rows)

But if you specify the timestamp to millisecond precision, and use 999 for the milliseconds on the upper bound, it finds the row:

SELECT * FROM sometable WHERE partition_key = 'a' 
    and cluster_start_date  >= '2015-08-22 06:17:09.000-0400'
    and cluster_start_date  <= '2015-08-22 06:17:09.999-0400';

 partition_key | cluster_start_date       | other_cluster_key | data
---------------+--------------------------+-------------------+------
             a | 2015-08-22 06:17:09-0400 |                 b | row2

Or you could drop the equals on the upper bound and say less than the next second like this:

SELECT * FROM sometable WHERE partition_key = 'a' 
    and cluster_start_date  >= '2015-08-22 06:17:09-0400' 
    and cluster_start_date  < '2015-08-22 06:17:10-0400';

 partition_key | cluster_start_date       | other_cluster_key | data
---------------+--------------------------+-------------------+------
             a | 2015-08-22 06:17:09-0400 |                 b | row2
Jim Meyer
  • 9,275
  • 1
  • 24
  • 49
  • Thank you for your help. I was testing to the second level, but it turned out the error was in another portion of my code. – piper1970 Aug 22 '15 at 15:23