Re: timestamp as clustering key doesn't work as expected

[Kai Wang]

Jon,

It's 2.1.10. I will see if I can reproduce it with a simple script.

Thanks.

On Fri, Oct 23, 2015 at 1:05 PM, Jon Haddad  wrote:

> What version of Cassandra?  I can’t think of a reason why you’d see this
> output.  If you can reliably reproduce, this should be filed as a JIRA.
> https://issues.apache.org/jira
>
>
>
> > On Oct 23, 2015, at 8:55 AM, Kai Wang  wrote:
> >
> > Hi,
> >
> > I use a timestamp column as the last clustering key so that I can run
> query like "timestamp > ... AND timestamp < ...". But it doesn't work as
> expected. Here is a simplified example.
> >
> > My table:
> > CREATE TABLE test (
> > tag text,
> > group int,
> > timestamp timestamp,
> > value double,
> > PRIMARY KEY (tag, group, timestamp)
> > ) WITH CLUSTERING ORDER BY (group ASC, timestamp DESC)
> >
> > After inserting some data, here is my query:
> >
> > cqlsh> select * from test where tag = 'MSFT' and group = 1 and timestamp
> ='2004-12-15 16:00:00-0500';
> >
> >  tag  | group | timestamp| value
> > --+---+--+---
> >  MSFT | 1 | 2004-12-15 21:00:00+ | 27.11
> >  MSFT | 1 | 2004-12-16 21:00:00+ | 27.16
> >  MSFT | 1 | 2004-12-17 21:00:00+ | 26.96
> >  MSFT | 1 | 2004-12-20 21:00:00+ | 26.95
> >  MSFT | 1 | 2004-12-21 21:00:00+ | 27.07
> >  MSFT | 1 | 2004-12-22 21:00:00+ | 26.98
> >  MSFT | 1 | 2004-12-23 21:00:00+ | 27.01
> >  MSFT | 1 | 2004-12-27 21:00:00+ | 26.85
> >  MSFT | 1 | 2004-12-28 21:00:00+ | 26.95
> >  MSFT | 1 | 2004-12-29 21:00:00+ |  26.9
> >  MSFT | 1 | 2004-12-30 21:00:00+ | 26.76
> > (11 rows)
> >
> > This doesn't make sense. I expect this query to return only the first
> row. Why does it give me back rows with different timestamps? Did I
> misunderstand how timestamp and clustering key work?
> >
> > Thanks.
> >
> > -Kai
>
>

Re: timestamp as clustering key doesn't work as expected

[Kai Wang]

https://issues.apache.org/jira/browse/CASSANDRA-10583

On Fri, Oct 23, 2015 at 1:26 PM, Kai Wang  wrote:

> Jon,
>
> It's 2.1.10. I will see if I can reproduce it with a simple script.
>
> Thanks.
>
> On Fri, Oct 23, 2015 at 1:05 PM, Jon Haddad  wrote:
>
>> What version of Cassandra?  I can’t think of a reason why you’d see this
>> output.  If you can reliably reproduce, this should be filed as a JIRA.
>> https://issues.apache.org/jira
>>
>>
>>
>> > On Oct 23, 2015, at 8:55 AM, Kai Wang  wrote:
>> >
>> > Hi,
>> >
>> > I use a timestamp column as the last clustering key so that I can run
>> query like "timestamp > ... AND timestamp < ...". But it doesn't work as
>> expected. Here is a simplified example.
>> >
>> > My table:
>> > CREATE TABLE test (
>> > tag text,
>> > group int,
>> > timestamp timestamp,
>> > value double,
>> > PRIMARY KEY (tag, group, timestamp)
>> > ) WITH CLUSTERING ORDER BY (group ASC, timestamp DESC)
>> >
>> > After inserting some data, here is my query:
>> >
>> > cqlsh> select * from test where tag = 'MSFT' and group = 1 and
>> timestamp ='2004-12-15 16:00:00-0500';
>> >
>> >  tag  | group | timestamp| value
>> > --+---+--+---
>> >  MSFT | 1 | 2004-12-15 21:00:00+ | 27.11
>> >  MSFT | 1 | 2004-12-16 21:00:00+ | 27.16
>> >  MSFT | 1 | 2004-12-17 21:00:00+ | 26.96
>> >  MSFT | 1 | 2004-12-20 21:00:00+ | 26.95
>> >  MSFT | 1 | 2004-12-21 21:00:00+ | 27.07
>> >  MSFT | 1 | 2004-12-22 21:00:00+ | 26.98
>> >  MSFT | 1 | 2004-12-23 21:00:00+ | 27.01
>> >  MSFT | 1 | 2004-12-27 21:00:00+ | 26.85
>> >  MSFT | 1 | 2004-12-28 21:00:00+ | 26.95
>> >  MSFT | 1 | 2004-12-29 21:00:00+ |  26.9
>> >  MSFT | 1 | 2004-12-30 21:00:00+ | 26.76
>> > (11 rows)
>> >
>> > This doesn't make sense. I expect this query to return only the first
>> row. Why does it give me back rows with different timestamps? Did I
>> misunderstand how timestamp and clustering key work?
>> >
>> > Thanks.
>> >
>> > -Kai
>>
>>
>

Re: timestamp as clustering key doesn't work as expected

[Jon Haddad]

What version of Cassandra?  I can’t think of a reason why you’d see this 
output.  If you can reliably reproduce, this should be filed as a JIRA. 
https://issues.apache.org/jira



> On Oct 23, 2015, at 8:55 AM, Kai Wang  wrote:
> 
> Hi,
> 
> I use a timestamp column as the last clustering key so that I can run query 
> like "timestamp > ... AND timestamp < ...". But it doesn't work as expected. 
> Here is a simplified example.
> 
> My table:
> CREATE TABLE test (
> tag text,
> group int,
> timestamp timestamp,
> value double,
> PRIMARY KEY (tag, group, timestamp)
> ) WITH CLUSTERING ORDER BY (group ASC, timestamp DESC)
> 
> After inserting some data, here is my query:
> 
> cqlsh> select * from test where tag = 'MSFT' and group = 1 and timestamp 
> ='2004-12-15 16:00:00-0500';
> 
>  tag  | group | timestamp| value
> --+---+--+---
>  MSFT | 1 | 2004-12-15 21:00:00+ | 27.11
>  MSFT | 1 | 2004-12-16 21:00:00+ | 27.16
>  MSFT | 1 | 2004-12-17 21:00:00+ | 26.96
>  MSFT | 1 | 2004-12-20 21:00:00+ | 26.95
>  MSFT | 1 | 2004-12-21 21:00:00+ | 27.07
>  MSFT | 1 | 2004-12-22 21:00:00+ | 26.98
>  MSFT | 1 | 2004-12-23 21:00:00+ | 27.01
>  MSFT | 1 | 2004-12-27 21:00:00+ | 26.85
>  MSFT | 1 | 2004-12-28 21:00:00+ | 26.95
>  MSFT | 1 | 2004-12-29 21:00:00+ |  26.9
>  MSFT | 1 | 2004-12-30 21:00:00+ | 26.76
> (11 rows)
> 
> This doesn't make sense. I expect this query to return only the first row. 
> Why does it give me back rows with different timestamps? Did I misunderstand 
> how timestamp and clustering key work?
> 
> Thanks.
> 
> -Kai