You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Mike Heffner <mi...@librato.com> on 2014/07/18 00:23:17 UTC
How to column slice with CQL + 1.2
What is the proper way to perform a column slice using CQL with 1.2?
I have a CF with a primary key X and 3 composite columns (A, B, C). I'd
like to find records at:
key=X
columns > (A=1, B=3, C=4) AND
columns <= (A=2)
The Query:
SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND column3>4
AND column1<=2;
fails with:
DoGetMeasures: column1 cannot be restricted by both an equal and an inequal
relation
This is against Cassandra 1.2.16.
What is the proper way to perform this query?
Cheers,
Mike
--
Mike Heffner <mi...@librato.com>
Librato, Inc.
Re: How to column slice with CQL + 1.2
Posted by DuyHai Doan <do...@gmail.com>.
Even if native protocole from 2.0 offers nice paging feature, the tuple
notation is mandatory when paging is handled from client-side.
On Fri, Jul 18, 2014 at 3:15 PM, Mike Heffner <mi...@librato.com> wrote:
> Tyler,
>
> Cool, yes I was actually trying to solve that exact problem of paginating
> with LIMIT when it ends up slicing in the middle of a set of composite
> columns. (though sounds like automatic ResultSet paging in 2.0.x alleviates
> that need).
>
> So to do composite column slicing in 1.2.x the answer is to stick with
> Thrift?
>
> Mike
>
>
> On Thu, Jul 17, 2014 at 8:27 PM, Tyler Hobbs <ty...@datastax.com> wrote:
>
>> For this type of query, you really want the tuple notation introduced in
>> 2.0.6 (https://issues.apache.org/jira/browse/CASSANDRA-4851):
>>
>> SELECT * FROM CF WHERE key='X' AND (column1, column2, column3) > (1, 3,
>> 4) AND (column1) < (2)
>>
>>
>> On Thu, Jul 17, 2014 at 6:01 PM, Mike Heffner <mi...@librato.com> wrote:
>>
>>> Michael,
>>>
>>> So if I switch to:
>>>
>>> SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND column3>4
>>>
>>> That doesn't include rows where column1=2, which breaks the original
>>> slice query.
>>>
>>> Maybe a better way to put it, I would like:
>>>
>>> SELECT * FROM CF WHERE key='X' AND column1>=1 AND column2>=3 AND
>>> column3>4 AND column1<=2;
>>>
>>> but that is rejected with:
>>>
>>> Bad Request: PRIMARY KEY part column2 cannot be restricted (preceding
>>> part column1 is either not restricted or by a non-EQ relation)
>>>
>>>
>>> Mike
>>>
>>>
>>>
>>> On Thu, Jul 17, 2014 at 6:37 PM, Michael Dykman <md...@gmail.com>
>>> wrote:
>>>
>>>> The last term in this query is redundant. Any time column1 = 1, we
>>>> may reasonably expect that it is also <= 2 as that's where 1 is found.
>>>> If you remove the last term, you elimiate the error and non of the
>>>> selection logic.
>>>>
>>>> SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND
>>>> column3>4 AND column1<=2;
>>>>
>>>> On Thu, Jul 17, 2014 at 6:23 PM, Mike Heffner <mi...@librato.com> wrote:
>>>> > What is the proper way to perform a column slice using CQL with 1.2?
>>>> >
>>>> > I have a CF with a primary key X and 3 composite columns (A, B, C).
>>>> I'd like
>>>> > to find records at:
>>>> >
>>>> > key=X
>>>> > columns > (A=1, B=3, C=4) AND
>>>> > columns <= (A=2)
>>>> >
>>>> > The Query:
>>>> >
>>>> > SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND
>>>> column3>4 AND
>>>> > column1<=2;
>>>> >
>>>> > fails with:
>>>> >
>>>> > DoGetMeasures: column1 cannot be restricted by both an equal and an
>>>> inequal
>>>> > relation
>>>> >
>>>> > This is against Cassandra 1.2.16.
>>>> >
>>>> > What is the proper way to perform this query?
>>>> >
>>>> >
>>>> > Cheers,
>>>> >
>>>> > Mike
>>>> >
>>>> > --
>>>> >
>>>> > Mike Heffner <mi...@librato.com>
>>>> > Librato, Inc.
>>>> >
>>>>
>>>>
>>>>
>>>> --
>>>> - michael dykman
>>>> - mdykman@gmail.com
>>>>
>>>> May the Source be with you.
>>>>
>>>
>>>
>>>
>>> --
>>>
>>> Mike Heffner <mi...@librato.com>
>>> Librato, Inc.
>>>
>>>
>>
>>
>> --
>> Tyler Hobbs
>> DataStax <http://datastax.com/>
>>
>
>
>
> --
>
> Mike Heffner <mi...@librato.com>
> Librato, Inc.
>
>
Re: How to column slice with CQL + 1.2
Posted by Mike Heffner <mi...@librato.com>.
Tyler,
Cool, yes I was actually trying to solve that exact problem of paginating
with LIMIT when it ends up slicing in the middle of a set of composite
columns. (though sounds like automatic ResultSet paging in 2.0.x alleviates
that need).
So to do composite column slicing in 1.2.x the answer is to stick with
Thrift?
Mike
On Thu, Jul 17, 2014 at 8:27 PM, Tyler Hobbs <ty...@datastax.com> wrote:
> For this type of query, you really want the tuple notation introduced in
> 2.0.6 (https://issues.apache.org/jira/browse/CASSANDRA-4851):
>
> SELECT * FROM CF WHERE key='X' AND (column1, column2, column3) > (1, 3, 4)
> AND (column1) < (2)
>
>
> On Thu, Jul 17, 2014 at 6:01 PM, Mike Heffner <mi...@librato.com> wrote:
>
>> Michael,
>>
>> So if I switch to:
>>
>> SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND column3>4
>>
>> That doesn't include rows where column1=2, which breaks the original
>> slice query.
>>
>> Maybe a better way to put it, I would like:
>>
>> SELECT * FROM CF WHERE key='X' AND column1>=1 AND column2>=3 AND
>> column3>4 AND column1<=2;
>>
>> but that is rejected with:
>>
>> Bad Request: PRIMARY KEY part column2 cannot be restricted (preceding
>> part column1 is either not restricted or by a non-EQ relation)
>>
>>
>> Mike
>>
>>
>>
>> On Thu, Jul 17, 2014 at 6:37 PM, Michael Dykman <md...@gmail.com>
>> wrote:
>>
>>> The last term in this query is redundant. Any time column1 = 1, we
>>> may reasonably expect that it is also <= 2 as that's where 1 is found.
>>> If you remove the last term, you elimiate the error and non of the
>>> selection logic.
>>>
>>> SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND
>>> column3>4 AND column1<=2;
>>>
>>> On Thu, Jul 17, 2014 at 6:23 PM, Mike Heffner <mi...@librato.com> wrote:
>>> > What is the proper way to perform a column slice using CQL with 1.2?
>>> >
>>> > I have a CF with a primary key X and 3 composite columns (A, B, C).
>>> I'd like
>>> > to find records at:
>>> >
>>> > key=X
>>> > columns > (A=1, B=3, C=4) AND
>>> > columns <= (A=2)
>>> >
>>> > The Query:
>>> >
>>> > SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND
>>> column3>4 AND
>>> > column1<=2;
>>> >
>>> > fails with:
>>> >
>>> > DoGetMeasures: column1 cannot be restricted by both an equal and an
>>> inequal
>>> > relation
>>> >
>>> > This is against Cassandra 1.2.16.
>>> >
>>> > What is the proper way to perform this query?
>>> >
>>> >
>>> > Cheers,
>>> >
>>> > Mike
>>> >
>>> > --
>>> >
>>> > Mike Heffner <mi...@librato.com>
>>> > Librato, Inc.
>>> >
>>>
>>>
>>>
>>> --
>>> - michael dykman
>>> - mdykman@gmail.com
>>>
>>> May the Source be with you.
>>>
>>
>>
>>
>> --
>>
>> Mike Heffner <mi...@librato.com>
>> Librato, Inc.
>>
>>
>
>
> --
> Tyler Hobbs
> DataStax <http://datastax.com/>
>
--
Mike Heffner <mi...@librato.com>
Librato, Inc.
Re: How to column slice with CQL + 1.2
Posted by Tyler Hobbs <ty...@datastax.com>.
For this type of query, you really want the tuple notation introduced in
2.0.6 (https://issues.apache.org/jira/browse/CASSANDRA-4851):
SELECT * FROM CF WHERE key='X' AND (column1, column2, column3) > (1, 3, 4)
AND (column1) < (2)
On Thu, Jul 17, 2014 at 6:01 PM, Mike Heffner <mi...@librato.com> wrote:
> Michael,
>
> So if I switch to:
>
> SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND column3>4
>
> That doesn't include rows where column1=2, which breaks the original slice
> query.
>
> Maybe a better way to put it, I would like:
>
> SELECT * FROM CF WHERE key='X' AND column1>=1 AND column2>=3 AND column3>4
> AND column1<=2;
>
> but that is rejected with:
>
> Bad Request: PRIMARY KEY part column2 cannot be restricted (preceding part
> column1 is either not restricted or by a non-EQ relation)
>
>
> Mike
>
>
>
> On Thu, Jul 17, 2014 at 6:37 PM, Michael Dykman <md...@gmail.com> wrote:
>
>> The last term in this query is redundant. Any time column1 = 1, we
>> may reasonably expect that it is also <= 2 as that's where 1 is found.
>> If you remove the last term, you elimiate the error and non of the
>> selection logic.
>>
>> SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND
>> column3>4 AND column1<=2;
>>
>> On Thu, Jul 17, 2014 at 6:23 PM, Mike Heffner <mi...@librato.com> wrote:
>> > What is the proper way to perform a column slice using CQL with 1.2?
>> >
>> > I have a CF with a primary key X and 3 composite columns (A, B, C). I'd
>> like
>> > to find records at:
>> >
>> > key=X
>> > columns > (A=1, B=3, C=4) AND
>> > columns <= (A=2)
>> >
>> > The Query:
>> >
>> > SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND
>> column3>4 AND
>> > column1<=2;
>> >
>> > fails with:
>> >
>> > DoGetMeasures: column1 cannot be restricted by both an equal and an
>> inequal
>> > relation
>> >
>> > This is against Cassandra 1.2.16.
>> >
>> > What is the proper way to perform this query?
>> >
>> >
>> > Cheers,
>> >
>> > Mike
>> >
>> > --
>> >
>> > Mike Heffner <mi...@librato.com>
>> > Librato, Inc.
>> >
>>
>>
>>
>> --
>> - michael dykman
>> - mdykman@gmail.com
>>
>> May the Source be with you.
>>
>
>
>
> --
>
> Mike Heffner <mi...@librato.com>
> Librato, Inc.
>
>
--
Tyler Hobbs
DataStax <http://datastax.com/>
Re: How to column slice with CQL + 1.2
Posted by Mike Heffner <mi...@librato.com>.
Michael,
So if I switch to:
SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND column3>4
That doesn't include rows where column1=2, which breaks the original slice
query.
Maybe a better way to put it, I would like:
SELECT * FROM CF WHERE key='X' AND column1>=1 AND column2>=3 AND column3>4
AND column1<=2;
but that is rejected with:
Bad Request: PRIMARY KEY part column2 cannot be restricted (preceding part
column1 is either not restricted or by a non-EQ relation)
Mike
On Thu, Jul 17, 2014 at 6:37 PM, Michael Dykman <md...@gmail.com> wrote:
> The last term in this query is redundant. Any time column1 = 1, we
> may reasonably expect that it is also <= 2 as that's where 1 is found.
> If you remove the last term, you elimiate the error and non of the
> selection logic.
>
> SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND
> column3>4 AND column1<=2;
>
> On Thu, Jul 17, 2014 at 6:23 PM, Mike Heffner <mi...@librato.com> wrote:
> > What is the proper way to perform a column slice using CQL with 1.2?
> >
> > I have a CF with a primary key X and 3 composite columns (A, B, C). I'd
> like
> > to find records at:
> >
> > key=X
> > columns > (A=1, B=3, C=4) AND
> > columns <= (A=2)
> >
> > The Query:
> >
> > SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND column3>4
> AND
> > column1<=2;
> >
> > fails with:
> >
> > DoGetMeasures: column1 cannot be restricted by both an equal and an
> inequal
> > relation
> >
> > This is against Cassandra 1.2.16.
> >
> > What is the proper way to perform this query?
> >
> >
> > Cheers,
> >
> > Mike
> >
> > --
> >
> > Mike Heffner <mi...@librato.com>
> > Librato, Inc.
> >
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> May the Source be with you.
>
--
Mike Heffner <mi...@librato.com>
Librato, Inc.
Re: How to column slice with CQL + 1.2
Posted by Michael Dykman <md...@gmail.com>.
The last term in this query is redundant. Any time column1 = 1, we
may reasonably expect that it is also <= 2 as that's where 1 is found.
If you remove the last term, you elimiate the error and non of the
selection logic.
SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND
column3>4 AND column1<=2;
On Thu, Jul 17, 2014 at 6:23 PM, Mike Heffner <mi...@librato.com> wrote:
> What is the proper way to perform a column slice using CQL with 1.2?
>
> I have a CF with a primary key X and 3 composite columns (A, B, C). I'd like
> to find records at:
>
> key=X
> columns > (A=1, B=3, C=4) AND
> columns <= (A=2)
>
> The Query:
>
> SELECT * FROM CF WHERE key='X' AND column1=1 AND column2=3 AND column3>4 AND
> column1<=2;
>
> fails with:
>
> DoGetMeasures: column1 cannot be restricted by both an equal and an inequal
> relation
>
> This is against Cassandra 1.2.16.
>
> What is the proper way to perform this query?
>
>
> Cheers,
>
> Mike
>
> --
>
> Mike Heffner <mi...@librato.com>
> Librato, Inc.
>
--
- michael dykman
- mdykman@gmail.com
May the Source be with you.