You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@cassandra.apache.org by Kai Wang <de...@gmail.com> on 2015/10/23 17:55:52 UTC

timestamp as clustering key doesn't work as expected

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+0000 | 27.11
 MSFT |     1 | 2004-12-16 21:00:00+0000 | 27.16
 MSFT |     1 | 2004-12-17 21:00:00+0000 | 26.96
 MSFT |     1 | 2004-12-20 21:00:00+0000 | 26.95
 MSFT |     1 | 2004-12-21 21:00:00+0000 | 27.07
 MSFT |     1 | 2004-12-22 21:00:00+0000 | 26.98
 MSFT |     1 | 2004-12-23 21:00:00+0000 | 27.01
 MSFT |     1 | 2004-12-27 21:00:00+0000 | 26.85
 MSFT |     1 | 2004-12-28 21:00:00+0000 | 26.95
 MSFT |     1 | 2004-12-29 21:00:00+0000 |  26.9
 MSFT |     1 | 2004-12-30 21:00:00+0000 | 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

Posted by Kai Wang <de...@gmail.com>.

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

On Fri, Oct 23, 2015 at 1:26 PM, Kai Wang <de...@gmail.com> 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 <jo...@jonhaddad.com> 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 <de...@gmail.com> 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+0000 | 27.11
>> >  MSFT |     1 | 2004-12-16 21:00:00+0000 | 27.16
>> >  MSFT |     1 | 2004-12-17 21:00:00+0000 | 26.96
>> >  MSFT |     1 | 2004-12-20 21:00:00+0000 | 26.95
>> >  MSFT |     1 | 2004-12-21 21:00:00+0000 | 27.07
>> >  MSFT |     1 | 2004-12-22 21:00:00+0000 | 26.98
>> >  MSFT |     1 | 2004-12-23 21:00:00+0000 | 27.01
>> >  MSFT |     1 | 2004-12-27 21:00:00+0000 | 26.85
>> >  MSFT |     1 | 2004-12-28 21:00:00+0000 | 26.95
>> >  MSFT |     1 | 2004-12-29 21:00:00+0000 |  26.9
>> >  MSFT |     1 | 2004-12-30 21:00:00+0000 | 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

Posted by Kai Wang <de...@gmail.com>.

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 <jo...@jonhaddad.com> 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 <de...@gmail.com> 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+0000 | 27.11
> >  MSFT |     1 | 2004-12-16 21:00:00+0000 | 27.16
> >  MSFT |     1 | 2004-12-17 21:00:00+0000 | 26.96
> >  MSFT |     1 | 2004-12-20 21:00:00+0000 | 26.95
> >  MSFT |     1 | 2004-12-21 21:00:00+0000 | 27.07
> >  MSFT |     1 | 2004-12-22 21:00:00+0000 | 26.98
> >  MSFT |     1 | 2004-12-23 21:00:00+0000 | 27.01
> >  MSFT |     1 | 2004-12-27 21:00:00+0000 | 26.85
> >  MSFT |     1 | 2004-12-28 21:00:00+0000 | 26.95
> >  MSFT |     1 | 2004-12-29 21:00:00+0000 |  26.9
> >  MSFT |     1 | 2004-12-30 21:00:00+0000 | 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

Posted by Jon Haddad <jo...@jonhaddad.com>.

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 <de...@gmail.com> 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+0000 | 27.11
>  MSFT |     1 | 2004-12-16 21:00:00+0000 | 27.16
>  MSFT |     1 | 2004-12-17 21:00:00+0000 | 26.96
>  MSFT |     1 | 2004-12-20 21:00:00+0000 | 26.95
>  MSFT |     1 | 2004-12-21 21:00:00+0000 | 27.07
>  MSFT |     1 | 2004-12-22 21:00:00+0000 | 26.98
>  MSFT |     1 | 2004-12-23 21:00:00+0000 | 27.01
>  MSFT |     1 | 2004-12-27 21:00:00+0000 | 26.85
>  MSFT |     1 | 2004-12-28 21:00:00+0000 | 26.95
>  MSFT |     1 | 2004-12-29 21:00:00+0000 |  26.9
>  MSFT |     1 | 2004-12-30 21:00:00+0000 | 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