What about aborting the transaction / raising an exception if you try and do an incremental operator against a non-existent PK?
The reasonable inferred intent of the user is to change a value that's expected to be there, so if it's not there it's an error case right? Otherwise you'd append "IF EXISTS". On Fri, Jun 21, 2024, at 1:56 AM, Caleb Rackliffe wrote: > It does, but the primary reason it does is that it is setting a value, not > incrementing one. When we’re setting a value, we don’t care what was there > before. Incrementing a value is not possible in a non-transitional update, > hence this thread… > >> On Jun 20, 2024, at 5:17 PM, Bernardo Botella <conta...@bernardobotella.com> >> wrote: >> 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);
