Difference between UPDATE and INSERT in Cassandra?

There is a subtle difference. Inserted records via INSERT remain if you set all non-key fields to null. Records inserted via UPDATE go away if you set all non-key fields to null.

Try this:

CREATE TABLE T (
  pk int,
  f1 int,
  PRIMARY KEY (pk)
);

INSERT INTO T (pk, f1) VALUES (1, 1);
UPDATE T SET f1=2 where pk=2;
SELECT * FROM T;

Returns:

 pk | f1
----+----
  1 |  1
  2 |  2

Now, update each row setting f1 to null.

UPDATE T SET f1 = null WHERE pk = 1;
UPDATE T SET f1 = null WHERE pk = 2;
SELECT * FROM T;

Note that row 1 remains, while row 2 is removed.

 pk | f1
----+------
  1 | null

If you look at these using Cassandra-cli, you will see a different in how the rows are added.

I’d sure like to know whether this is by design or a bug and see this behavior documented.