Weird Cassandra upsert behaviour

This post is about explaining one huge difference between cassandra upsert and insert and how things get hairy when you use ttl with a column.

Table schema

Here is the cql statement to create the table.

Normal insert

so far so good.

Normal update

This is an update which behaves like normal insert.

insert and update with a null

In the insert statement as we have mentioned a primary key but set a non-primary-key column to null a row should be inserted into the table. We did the similar thing with update statement. We should be able to verify this with a select statement.

What happended here. There is no row corresponding to primary key ’d’ which we used in update statement but there is one row corresponding to primary key ‘c’ which we used in insert statement. That is weird, right? Yes, it is. Apparently cassandra deletes a row if all of its non primary key fields are set to null if that row was inserted with a update statement.

TTL

When you insert ttl into picture things really get hairy and you would run into situations where you would think it’s a cassandra bug. But in reality it’s not. Many a times it’s been reported as a bug in cassandra but had to be explained to naive souls :). I was such a naive soul sometime back. See 8430 and 6668. TTLs are on per column basis in cassandra and you need to be careful when using ttls with update statements. The lifetime of a row would also depend on whether the row was inserted with an insert or update statement.