> > Doesn’t an UPDATE statement creates a row if the partition key does not > exist?
Things are more tricky that what the documentation is letting people believe. Internally, UPDATE does not really create a row. It creates a set of updates for a row. At the CQL level it looks like a row exists but there is a difference. The best way to understand it is to run: INSERT INTO my_table (pk, c, v) VALUES (1, 1, 1); DELETE v FROM my_table WHERE pk = 1 AND c = 1; SELECT * FROM my_table WHERE pk = 1 AND c =1; The result will be: pk | c | v ----+---+----- 1 | 1 | null If instead you run UPDATE my_table SET v = 1 WHERE pk = 1 AND c = 1; DELETE v FROM my_table WHERE pk = 1 AND c = 1; SELECT * FROM my_table WHERE pk = 1 AND c =1; The result will be: pk | c | v ----+---+----- No row will be returned An INSERT creates the primary key columns (by giving them a write timestamp) and UPDATE does not. Based on this behavior the second option appears to be counter intuitive with how CQL normally operates. The first option looks to me as the correct one as long as the user will know that the transaction did not succeed. Le ven. 21 juin 2024 à 17:05, Jon Haddad <j...@jonhaddad.com> a écrit : > Seems to me that this should use the same behavior as a counter unless IF > EXISTS is supplied. > > I can see a solid argument for failing though, if the argument is that > only counters behave that way, vs increment / decrement. > > > > On Fri, Jun 21, 2024 at 4:32 PM Josh McKenzie <jmcken...@apache.org> > wrote: > >> 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); >> >> >>
