Doesn’t an UPDATE statement creates a row if the partition key does not exist? That’s also confirmed by the official Cassandra documentation here <https://cassandra.apache.org/doc/stable/cassandra/cql/dml.html#update-statement>:
”Unlike in SQL, UPDATE does not check the prior existence of the row by default. The row is created if none existed before, and updated otherwise. Furthermore, there is no means of knowing which action occurred.” That being the case, I think the second option you mention is what keeps consistency with the UPDATEs out of the transaction. Kind regards, Bernardo > On Jun 20, 2024, at 1:54 PM, Caleb Rackliffe <calebrackli...@gmail.com> wrote: > > We had a bug report a while back from Luis E Fernandez and team in > CASSANDRA-18988 <https://issues.apache.org/jira/browse/CASSANDRA-18988> > around the behavior of increments/decrements on numeric fields for > non-existent rows. Consider the following, wich can be run on the > cep-15-accord branch: > > CREATE KEYSPACE accord WITH replication = {'class': 'SimpleStrategy', > 'replication_factor': '1'} AND durable_writes = true > > CREATE TABLE accord.accounts ( > partition text, > account_id int, > balance int, > PRIMARY KEY (partition, account_id) > ) WITH CLUSTERING ORDER BY (account_id ASC) AND transactional_mode='full' > > BEGIN TRANSACTION > INSERT INTO accord.accounts (partition, account_id, balance) VALUES > ('default', 0, 100); > INSERT INTO accord.accounts (partition, account_id, balance) VALUES > ('default', 1, 100); > COMMIT TRANSACTION > > BEGIN TRANSACTION > UPDATE accord.accounts SET balance -= 10 WHERE partition = 'default' AND > account_id = 1; > UPDATE accord.accounts SET balance += 10 WHERE partition = 'default' AND > account_id = 3; > COMMIT TRANSACTION > > Reading the 'default' partition will produce the following result. > > partition | account_id | balance > -----------+------------+--------- > default | 0 | 100 > default | 1 | 90 > > As you will notice, we have not implicitly inserted a row for account_id 3, > which does not exist when we request that its balance be incremented by 10. > This is by design, as null + 10 == null. > > Before I close CASSANDRA-18988 > <https://issues.apache.org/jira/browse/CASSANDRA-18988>, I'd like to confirm > with everyone reading this that the behavior above is reasonable. The only > other option I've seen proposed that would make sense is perhaps producing a > result like: > > partition | account_id | balance > -----------+------------+--------- > default | 0 | 100 > default | 1 | 90 > default | 3 | null > > > Note however that this is exactly what we would produce if we had first > inserted a row w/ no value for balance: > > INSERT INTO accord.accounts (partition, account_id) VALUES ('default', 3);
