Return-Path: X-Original-To: apmail-cassandra-user-archive@www.apache.org Delivered-To: apmail-cassandra-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id AE23C11A20 for ; Fri, 18 Jul 2014 00:28:26 +0000 (UTC) Received: (qmail 28274 invoked by uid 500); 18 Jul 2014 00:28:24 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 28237 invoked by uid 500); 18 Jul 2014 00:28:24 -0000 Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cassandra.apache.org Delivered-To: mailing list user@cassandra.apache.org Received: (qmail 28227 invoked by uid 99); 18 Jul 2014 00:28:24 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 Jul 2014 00:28:24 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of tyler@datastax.com designates 209.85.215.41 as permitted sender) Received: from [209.85.215.41] (HELO mail-la0-f41.google.com) (209.85.215.41) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 Jul 2014 00:28:20 +0000 Received: by mail-la0-f41.google.com with SMTP id s18so2383458lam.28 for ; Thu, 17 Jul 2014 17:27:57 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:content-type; bh=0eZcAfP0raE1a7fG6Fy+3GRRc9gMY0R5ZZs9Nhtc4S8=; b=JCo0wBoS4dfqYo6WwLiD9NfdGOZY5w4robq3RVUP4FL3upwOhRF/L4Z0qS0GvmlE+2 zA6PXL0/Icktesw7CefZD7j162TQcq8WC7H1Wh5UeACpwG314Vm+vHpsZ8TNeuh5Cpca 4ZmMcsbNlDqrrR9OLHB31iZF8BW2hujK2COAbK9Gx7NN8dWZOtUGzhTRN9O47PkBkJuA KfL0q5hPxBz9UDVTK/EVdKlrwyzNPoyb2AfmV78WkT0OIIid3S8zJUzD6qa/C6BbXJjx CY0RoXu4bZBgYD/u7rc+eeawWxDKbOxQUrZb/JC/68nJZLwhxQ3n+tptlZAmV796hixP 1qqg== X-Gm-Message-State: ALoCoQkxB+BVhixjgK+vKD2CaVSpXgQKbHqgZe9QCLUSb6IcGDOn0NuzmkAoGSrp/+QzwcbzeP8a X-Received: by 10.152.87.229 with SMTP id bb5mr558447lab.75.1405643277058; Thu, 17 Jul 2014 17:27:57 -0700 (PDT) MIME-Version: 1.0 Received: by 10.112.168.228 with HTTP; Thu, 17 Jul 2014 17:27:36 -0700 (PDT) In-Reply-To: References: From: Tyler Hobbs Date: Thu, 17 Jul 2014 19:27:36 -0500 Message-ID: Subject: Re: How to column slice with CQL + 1.2 To: user@cassandra.apache.org Content-Type: multipart/alternative; boundary=001a11c34e0635da4b04fe6cd43f X-Virus-Checked: Checked by ClamAV on apache.org --001a11c34e0635da4b04fe6cd43f Content-Type: text/plain; charset=UTF-8 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 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 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 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 >> > Librato, Inc. >> > >> >> >> >> -- >> - michael dykman >> - mdykman@gmail.com >> >> May the Source be with you. >> > > > > -- > > Mike Heffner > Librato, Inc. > > -- Tyler Hobbs DataStax --001a11c34e0635da4b04fe6cd43f Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
For this type of query, you really want the tuple notation= introduced in 2.0.6 (https://issues.apache.org/jira/browse/CASSANDRA-4851):
<= br> SELECT * FROM CF WHERE key=3D'X' AND (column1, column2, column3) &g= t; (1, 3, 4) AND (column1) < (2)

On Thu, Jul 17, 2014 at 6:01 PM, Mike Heffn= er <mike@librato.com> wrote:
Michael,

So if I switch to:

SELECT *= FROM CF WHERE key=3D'X' AND column1=3D1 AND column2=3D3 AND column= 3>4

That doesn't include rows where column1=3D2, which breaks the origin= al slice query.

May= be a better way to put it, I would like:

SELECT * FROM CF WHERE key=3D'X' AND column1>=3D1 AND column2= >=3D3 AND column3>4 AND column1<=3D2;

but that is rejected with:

Bad Request: PRIMAR= Y KEY part column2 cannot be restricted (preceding part column1 is either n= ot restricted or by a non-EQ relation)
<= font color=3D"#888888">


Mike



On Thu, Jul 17, 2014 at 6:37= PM, Michael Dykman <mdykman@gmail.com> wrote:
The last term in this query is redundant. = =C2=A0Any time column1 =3D 1, we
may reasonably expect that it is also <=3D 2 as that's where 1 is fo= und.
If you remove the last term, you elimiate the error and non of the
selection logic.

SELECT * FROM CF WHERE key=3D'X' AND column1=3D1 AND column2=3D3 AN= D
column3>4 AND column1<=3D2;

On Thu, Jul 17, 2014 at 6:23 PM, Mike Heffner <mike@librato.com> wro= te:
> 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&= #39;d like
> to find records at:
>
> key=3DX
> columns > (A=3D1, B=3D3, C=3D4) AND
> =C2=A0 =C2=A0columns <=3D (A=3D2)
>
> The Query:
>
> SELECT * FROM CF WHERE key=3D'X' AND column1=3D1 AND column2= =3D3 AND column3>4 AND
> column1<=3D2;
>
> fails with:
>
> DoGetMeasures: column1 cannot be restricted by both an equal and an in= equal
> relation
>
> This is against Cassandra 1.2.16.
>
> What is the proper way to perform this query?
>
>
> Cheers,
>
> Mike
>
> --
>
> =C2=A0 Mike Heffner <mike@librato.com>
> =C2=A0 Librato, Inc.
>



--
=C2=A0- michael dykman
=C2=A0- mdykman@gmai= l.com

=C2=A0May the Source be with you.



--

=C2=A0=C2=A0Mike Heffner <mike@librato.com>
=C2=A0=C2=A0Librato, = Inc.




--
Tyler Hobbs
DataStax
 --001a11c34e0635da4b04fe6cd43f--