Hi, SGTM. It's not just what we return though it's also supporting UPSERT for RMR updates? Because our transactions are one shot I don't think you could do that because the statement that does INSERT IF NOT EXIST would not generate a row that is visible to a later UPDATE statement in the same transaction that increments the row.
We might also have a restriction somewhere that limits us to one update per clustering. Ariel On Mon, Jun 24, 2024, at 1:30 PM, Caleb Rackliffe wrote: > It sounds like the best course of action for now would be to keep the current > behavior. > > However, we might want to fold this into CASSANDRA-18107 as a specific > concern around what we return when an explicit SELECT isn't present in the > transaction. > > i.e. For any update, we'll have something like (courtesy of David) UPDATED, > SKIPPED (condition was met but couldn't update a non-existent row), or > CONDITION_NOT_MET > > > On Mon, Jun 24, 2024 at 11:42 AM Ariel Weisberg <ar...@weisberg.ws> wrote: >> __ >> 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); >>
