You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Cameron Little <ca...@cloudcitylabs.co> on 2015/08/22 01:41:47 UTC
Delete semantics
Can anyone help me understand the semantics of the DELETE cql statement, specifically the WHERE… part?
Taken literally, the datastax documentation at http://docs.datastax.com/en/cql/3.1/cql/cql_reference/delete_r.html <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 <https://cassandra.apache.org/doc/cql3/CQL.html#deleteStmt> seems to indicate that the row specifications can be in any order.
Here’s what I’ve 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 = clauses (<identifier> '=' <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 = 'a';
DELETE FROM mpk_store WHERE pk_one IN ('a', 'b') AND pk_two = 'a' AND pk_three = 'b';
DELETE FROM mpk_store WHERE pk_one IN ('a', 'b');
DELETE FROM mpk_store WHERE pk_one = '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 = '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 = '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
Re: Delete semantics
Posted by Cameron Little <ca...@cloudcitylabs.co>.
Great, that perfectly satiated my curiosity.
Cameron Little
c 360-460-1205
> On Aug 24, 2015, at 8:01 AM, Sebastian Estevez <se...@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 <https://issues.apache.org/jira/browse/CASSANDRA-6237>
>
> Check out CASSANDRA-6446 <https://issues.apache.org/jira/browse/CASSANDRA-6446> for details on how range tombstones work, note their effects on performance even with this patch.
>
>
> All the best,
>
> <http://www.datastax.com/>
> Sebastián Estévez
> Solutions Architect | 954 905 8615 | sebastian.estevez@datastax.com <ma...@datastax.com>
> <https://www.linkedin.com/company/datastax> <https://www.facebook.com/datastax> <https://twitter.com/datastax> <https://plus.google.com/+Datastax/about> <http://feeds.feedburner.com/datastax>
>
> <http://cassandrasummit-datastax.com/?utm_campaign=summit15&utm_medium=summiticon&utm_source=emailsignature>
>
> DataStax is the fastest, most scalable distributed database technology, delivering Apache Cassandra to the world’s 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 <ma...@cloudcitylabs.co>> wrote:
> Can anyone help me understand the semantics of the DELETE cql statement, specifically the WHERE… part?
>
> Taken literally, the datastax documentation at http://docs.datastax.com/en/cql/3.1/cql/cql_reference/delete_r.html <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 <https://cassandra.apache.org/doc/cql3/CQL.html#deleteStmt> seems to indicate that the row specifications can be in any order.
>
>
> Here’s what I’ve 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 = clauses (<identifier> '=' <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 = 'a';
> DELETE FROM mpk_store WHERE pk_one IN ('a', 'b') AND pk_two = 'a' AND pk_three = 'b';
> DELETE FROM mpk_store WHERE pk_one IN ('a', 'b');
> DELETE FROM mpk_store WHERE pk_one = '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 = '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 = '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
>
>
Re: Delete semantics
Posted by Sebastian Estevez <se...@datastax.com>.
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 <https://issues.apache.org/jira/browse/CASSANDRA-6237>
Check out CASSANDRA-6446
<https://issues.apache.org/jira/browse/CASSANDRA-6446> for details on how
range tombstones work, note their effects on performance even with this
patch.
All the best,
[image: datastax_logo.png] <http://www.datastax.com/>
Sebastián Estévez
Solutions Architect | 954 905 8615 | sebastian.estevez@datastax.com
[image: linkedin.png] <https://www.linkedin.com/company/datastax> [image:
facebook.png] <https://www.facebook.com/datastax> [image: twitter.png]
<https://twitter.com/datastax> [image: g+.png]
<https://plus.google.com/+Datastax/about>
<http://feeds.feedburner.com/datastax>
<http://cassandrasummit-datastax.com/?utm_campaign=summit15&utm_medium=summiticon&utm_source=emailsignature>
DataStax is the fastest, most scalable distributed database technology,
delivering Apache Cassandra to the world’s 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 <ca...@cloudcitylabs.co>
wrote:
> Can anyone help me understand the semantics of the DELETE cql statement,
> specifically the WHERE… 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 seems to
> indicate that the row specifications can be in any order.
>
>
> Here’s what I’ve 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 = clauses (<identifier> '=' <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 = 'a';
> DELETE FROM mpk_store WHERE pk_one IN ('a', 'b') AND pk_two = 'a' AND
> pk_three = 'b';
> DELETE FROM mpk_store WHERE pk_one IN ('a', 'b');
> DELETE FROM mpk_store WHERE pk_one = '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 = '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 = '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
>
>