Cassandra: delete last record with static column

Question

We are unable to delete the last row in a table with a static column.

We have tried with Cassandra 2.2, 3.0 and 3.11.2. With 1 or more as replication factor.

You can reproduce this by creating the following table:

CREATE TABLE playlists (
    username text,
    playlist_id bigint,

    playlist_order bigint,

    last_modified bigint static,

    PRIMARY KEY ((username, playlist_id), playlist_order)
)
WITH CLUSTERING ORDER BY (playlist_order DESC);

Then insert some test data:

INSERT INTO 
    playlists (
        username, 
        playlist_id, 
        playlist_order, 
        last_modified) 
    values (
        'test', 
        123, 
        123, 
        123);

Then delete said row:

DELETE FROM playlists WHERE username = 'test' AND playlist_id = 123 AND playlist_order = 123;

Now do a select:

SELECT * FROM playlists WHERE username = 'test' AND playlist_id = 123;

Your result should look like this:

 username | playlist_id | playlist_order | last_modified
----------+-------------+----------------+---------------
     test |         123 |           null |           123

As you can see the record is not deleted, only the clustering column is deleted. We suspect this has to do with the static column but am unable to explain it beyond that.

However if you omit the clustering key in the delete query, like so:

DELETE FROM playlists WHERE username = 'test' AND playlist_id = 123;

Then the record is deleted, but this requires unnecessary application logic to complete.

The behaviour only applies to the last record with the shared static column, you can populate the table with multiple records and delete those successfully, but the last one will always be dangling.

Answer 1

Static columns exist per partition so in your case the last_modified value 123 exists for all rows in the partition test:123.

Your DELETE statement will not delete the static column because you are specifying a specific row for deletion. The static column will remain even though there are no rows left in the partition.

To delete the static column you need to issue:

DELETE last_modified FROM playlists WHERE username = 'test' AND 'playlist_id' = 123;

This will remove the static column from the partition.