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 C7BAC18F0F for ; Mon, 24 Aug 2015 20:40:15 +0000 (UTC) Received: (qmail 35395 invoked by uid 500); 24 Aug 2015 20:40:13 -0000 Delivered-To: apmail-cassandra-user-archive@cassandra.apache.org Received: (qmail 35360 invoked by uid 500); 24 Aug 2015 20:40:13 -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 35350 invoked by uid 99); 24 Aug 2015 20:40:13 -0000 Received: from Unknown (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 24 Aug 2015 20:40:13 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 922A5ED77E for ; Mon, 24 Aug 2015 20:40:12 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 3.201 X-Spam-Level: *** X-Spam-Status: No, score=3.201 tagged_above=-999 required=6.31 tests=[HTML_MESSAGE=3, KAM_HUGEIMGSRC=0.2, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, T_KAM_HTML_FONT_INVALID=0.01, T_REMOTE_IMAGE=0.01, URIBL_BLOCKED=0.001] autolearn=disabled Received: from mx1-us-west.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id JD0K8IdxMJKs for ; Mon, 24 Aug 2015 20:39:58 +0000 (UTC) Received: from mail-pa0-f54.google.com (mail-pa0-f54.google.com [209.85.220.54]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with ESMTPS id 2546A20752 for ; Mon, 24 Aug 2015 20:39:58 +0000 (UTC) Received: by padfo6 with SMTP id fo6so1380299pad.3 for ; Mon, 24 Aug 2015 13:39: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:from:content-type:message-id:mime-version :subject:date:references:to:in-reply-to; bh=VwMYBHd4MogfUwWKtUhUD1KM4ZAvwXOO92FjVIvuLes=; b=dZiROooYMERftSQHBPEqXutTDBppWIAw5t4Qa4BxcJD+qPZnJMeM+Hd5opL6vT5NWx dIlxM8GO7UKyrBSvY0KXzMCplQOOH53nGixLIf6YTfPxZVLEE/OdrmVllbJkjkhMHrjT 2IiOP3G6/i3fYBMPaCZgIY9d/s9IS5ZJYeC8kEokMIm3Ab1VnOfE7kVjnMOENTLxyzXQ osTMw9MVg0m57jl6EIo4Eus3S3/dnIFTJff2QQSI4Jw5REDlNq17wW77h2S5enFlhN4Y 0RWHud7Tjed232JjsjnffI64csphxILdZ3ax1VI4rOJcC9PCxvjX3kyrrTy7NfZSNN/L 2ZkQ== X-Gm-Message-State: ALoCoQkHgTKv4PSVwLbL0YhU4E+/9SdA6pE3k3Tb1NE/MYvnNXQCtny4gbb0LqxUnF8nMA2ZHmMJ X-Received: by 10.68.197.65 with SMTP id is1mr48360879pbc.109.1440445217525; Mon, 24 Aug 2015 12:40:17 -0700 (PDT) Received: from [192.168.0.102] ([209.181.137.150]) by smtp.gmail.com with ESMTPSA id sk5sm18288586pbc.75.2015.08.24.12.40.16 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Mon, 24 Aug 2015 12:40:16 -0700 (PDT) From: Cameron Little Content-Type: multipart/alternative; boundary="Apple-Mail=_941EDADD-0BAC-43FF-BB69-829928FB1741" Message-Id: <639E7013-ED74-444A-A047-63E4C2FEFE4F@cloudcitylabs.co> Mime-Version: 1.0 (Mac OS X Mail 8.2 \(2104\)) Subject: Re: Delete semantics Date: Mon, 24 Aug 2015 12:40:15 -0700 References: <2FBBBD93-F080-4E28-8E3F-958D11B5DD2C@cloudcitylabs.co> To: user@cassandra.apache.org In-Reply-To: X-Mailer: Apple Mail (2.2104) --Apple-Mail=_941EDADD-0BAC-43FF-BB69-829928FB1741 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Great, that perfectly satiated my curiosity.=20 Cameron Little c 360-460-1205 > On Aug 24, 2015, at 8:01 AM, Sebastian Estevez = wrote: >=20 > Hi Cameron, >=20 > INSERTs did not always have the ability to do where in's and the = functionality has not not been ported to DELETEs. This Jira should give = you what you're looking for (ETA 3.0 beta 2): >=20 > CASSANDRA-6237 >=20 > Check out CASSANDRA-6446 = for details on = how range tombstones work, note their effects on performance even with = this patch. >=20 >=20 > All the best, >=20 > > Sebasti=C3=A1n Est=C3=A9vez > Solutions Architect | 954 905 8615 | sebastian.estevez@datastax.com = > = = = >=20 > = >=20 > DataStax is the fastest, most scalable distributed database = technology, delivering Apache Cassandra to the world=E2=80=99s most = innovative enterprises. Datastax is built to be agile, always-on, and = predictably scalable to any size. With more than 500 customers in 45 = countries, DataStax is the database technology and transactional = backbone of choice for the worlds most innovative companies such as = Netflix, Adobe, Intuit, and eBay.=20 >=20 > On Fri, Aug 21, 2015 at 7:41 PM, Cameron Little = > wrote: > Can anyone help me understand the semantics of the DELETE cql = statement, specifically the WHERE=E2=80=A6 part? >=20 > Taken literally, the datastax documentation at = http://docs.datastax.com/en/cql/3.1/cql/cql_reference/delete_r.html = = seems to indicate a single row specification can be used.=20 >=20 > The documentation at = https://cassandra.apache.org/doc/cql3/CQL.html#deleteStmt = seems to = indicate that the row specifications can be in any order. >=20 >=20 > Here=E2=80=99s what I=E2=80=99ve found so far from testing. >=20 > - Identifiers must be primary key columns. > - A single IN clause ( IN '(' ')') is allowed = for the first primary key column > - Mutliple =3D clauses ( '=3D' ) are allowed, = starting with the first primary key column (not already used), not = skipping any, and not appearing before an IN clause >=20 > For example, the following work for the table: >=20 > CREATE TABLE mpk_store ( > pk_one text, > pk_two text, > pk_three text, > four text, > PRIMARY KEY (pk_one, pk_two, pk_three) > ) >=20 > DELETE FROM mpk_store WHERE pk_one IN ('a', 'b') AND pk_two =3D 'a'; > DELETE FROM mpk_store WHERE pk_one IN ('a', 'b') AND pk_two =3D 'a' = AND pk_three =3D 'b'; > DELETE FROM mpk_store WHERE pk_one IN ('a', 'b'); > DELETE FROM mpk_store WHERE pk_one =3D 'a'; >=20 > The following return Bad Request errors: >=20 > DELETE FROM mpk_store WHERE pk_one IN ('a', 'b') AND pk_two IN ('a', = 'b'); > DELETE FROM mpk_store WHERE pk_one =3D 'test_fetch_partial_limit' AND = pk_two IN ('a', 'b'); > DELETE FROM mpk_store WHERE pk_one IN ('a', 'b') AND pk_two IN ('a', = 'b') AND pk_three =3D 'b'; >=20 > This is a bit weird, since select allows IN clauses anywhere in the = statement. >=20 >=20 > Can anyone help explain these semantics or why Cassandra does this? >=20 > Thanks, > Cameron Little >=20 >=20 --Apple-Mail=_941EDADD-0BAC-43FF-BB69-829928FB1741 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Great, that perfectly satiated my curiosity. 

Cameron Little
c 360-460-1205

On Aug 24, 2015, at 8:01 AM, Sebastian Estevez <sebastian.estevez@datastax.com> wrote:

Hi Cameron,

INSERTs did not always have the ability to do where in's and = the functionality has not not been ported to DELETEs. This Jira should = give you what you're looking for (ETA 3.0 beta 2):

CASSANDRA-6237

Check out CASSANDRA-6446 for details on how range tombstones work, = note their effects on performance even with this patch.


All the best,

3D"datastax_logo.png"
Sebasti=C3=A1n = Est=C3=A9vez
Solutions Architect = | 954 905 8615 | sebastian.estevez@datastax.com
3D"linkedin.png" = 3D"facebook.png" 3D"twitter.png" 3D"g+.png"


DataStax is the fastest, most scalable distributed database = technology, delivering Apache Cassandra to the world=E2=80=99s most = innovative enterprises. Datastax is built to be agile, always-on, and = predictably scalable to any size. With more than 500 customers in 45 = countries, DataStax is the database technology and = transactional backbone of choice for the worlds most innovative = companies such as Netflix, Adobe, Intuit, and eBay. =

On Fri, Aug 21, 2015 at 7:41 = PM, Cameron Little <cameron@cloudcitylabs.co> wrote:
Can anyone help me understand = the semantics of the DELETE cql statement, specifically the WHERE=E2=80=A6= part?

Taken = literally, the datastax documentation at http://docs.datastax.com/en/cql/3.1/cql/cql_reference/delete_r.= html seems to indicate a single row specification can be = used. 

The = documentation at https://cassandra.apache.org/doc/cql3/CQL.html#deleteStmt&n= bsp;seems to indicate that the row specifications can be in any = order.


Here=E2=80=99s what I=E2=80=99ve found = so far from testing.

- Identifiers must be primary key columns.
- A single IN clause (<identifier> IN '(' = <term_list> ')') is allowed for the  first primary key = column
- Mutliple =3D clauses (<identifier> = '=3D' <term>) are allowed, starting with the first primary key = column (not already used), not skipping any, and not appearing before an = IN clause

For = example, the following work for the table:

CREATE TABLE mpk_store (
  pk_one text,
  pk_two = text,
  pk_three text,
  four text,
  PRIMARY KEY = (pk_one, pk_two, pk_three)
)

DELETE FROM mpk_store WHERE pk_one IN = ('a', 'b') AND pk_two =3D 'a';
DELETE FROM = mpk_store WHERE pk_one IN ('a', 'b') AND pk_two =3D 'a' AND pk_three =3D = 'b';
DELETE FROM mpk_store WHERE pk_one IN ('a', 'b');
DELETE FROM mpk_store WHERE pk_one =3D 'a';

The following return Bad = Request errors:

DELETE FROM mpk_store WHERE pk_one IN ('a', 'b') AND pk_two = IN ('a', 'b');
DELETE FROM mpk_store WHERE pk_one =3D= 'test_fetch_partial_limit' AND pk_two IN ('a', 'b');
DELETE FROM mpk_store WHERE pk_one IN ('a', 'b') AND pk_two = IN ('a', 'b') AND pk_three =3D 'b';

This is a bit weird, since select = allows IN clauses anywhere in the statement.


Can = anyone help explain these semantics or why Cassandra does = this?

Thanks,
Cameron = Little



= --Apple-Mail=_941EDADD-0BAC-43FF-BB69-829928FB1741--