Hi, I think the current behavior maps to SQL more than CQL. In SQL an update doesn't generate an error if the row to be updating doesn't exist it just return 0 rows updated.
If someone wanted an upsert or increment behavior in their transaction could they accomplish it with the current transaction CQL at all? We could support a more optimal syntax later, but I suspect that with our one shot behavior it would get mixed up by multiple attempts to insert if not exists and then update the same row to achieve upsert. Ariel On Thu, Jun 20, 2024, at 4:54 PM, Caleb Rackliffe 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);
