Oops. I missed this part: "full primary key or a limit of 1" Still curious what the end-user would see if there is more than one row returned.
On Sat, Jun 4, 2022 at 5:46 PM Patrick McFadin <pmcfa...@gmail.com> wrote: > I've been waiting for this email! I'll echo what Jeff said about how > exciting this is for the project. > > On the SELECT inside the transaction: > > In the first example, I'm making an assumption that you are doing a select > on a partition key and only expect one result but is any valid CQL SELECT > allowed here? If 'model' were a non-partition key column name and was > indexed, then you could potentially have multiple rows returned and that > isn't an allowed operation. Are only partition key lookups allowed or is > there some logic looking for only one row? > > I'm asking because I can see in reverse time series models where you can > select the latest temperature > SELECT temperature FROM weather_station WHERE id=1234 AND > DATE='2022-06-04' LIMIT 1; > > (also, horrible example. Everyone knows that the return value for a > Pinto.is_running will always evaluate to FALSE) > > On COMMIT TRANSACTION: > > So much to unpack here. In the case that the condition is met, is the > mutation applied at that point, or has it already happened and there is > something like a rollback segment? What is the case when the condition is > not met and what is presented to the end-user? More importantly, what > happens with respect to the A & I in ACID when the transaction is applied? > > If UPDATE is used, returning the number of rows changed would be helpful. > > Is this something that can be done interactively in cqlsh or does it all > have to be submitted in one statement block? > > I'll stop here for now. > > Patrick > > On Sat, Jun 4, 2022 at 3:34 PM bened...@apache.org <bened...@apache.org> > wrote: > >> > The returned result set is after the updates are applied? >> >> Returning the prior values is probably more powerful, as you can perform >> unconditional updates and respond to the prior state, that you otherwise >> would not know. It’s also simpler to implement. >> >> >> >> My inclination is to require that SELECT statements are declared first, >> so that we leave open the option of (in future) supporting SELECT >> statements in any place in the transaction, returning the values as of >> their position in a sequential execution of the statements. >> >> >> >> > And would you allow a transaction that had > 1 named select and no >> modification statements, but commit if 1=1 ? >> >> >> >> My preference is that the IF condition is anyway optional, as it is much >> more obvious to a user than concocting some always-true condition. But yes, >> read-only transactions involving multiple tables will definitely be >> supported. >> >> >> >> >> >> *From: *Jeff Jirsa <jji...@gmail.com> >> *Date: *Saturday, 4 June 2022 at 22:49 >> *To: *dev@cassandra.apache.org <dev@cassandra.apache.org> >> *Subject: *Re: CEP-15 multi key transaction syntax >> >> >> And would you allow a transaction that had > 1 named select and no >> modification statements, but commit if 1=1 ? >> >> > On Jun 4, 2022, at 2:45 PM, Jeff Jirsa <jji...@gmail.com> wrote: >> > >> > >> > >> >> On Jun 3, 2022, at 8:39 AM, Blake Eggleston <beggles...@apple.com> >> wrote: >> >> >> >> Hi dev@, >> > >> > First, I’m ridiculously excited to see this. >> > >> >> >> >> I’ve been working on a draft syntax for Accord transactions and wanted >> to bring what I have to the dev list to solicit feedback and build >> consensus before moving forward with it. The proposed transaction syntax is >> intended to be an extended batch syntax. Basically batches with selects, >> and an optional condition at the end. To facilitate conditions against an >> arbitrary number of select statements, you can also name the statements, >> and reference columns in the results. To cut down on the number of >> operations needed, select values can also be used in updates, including >> some math operations. Parameterization of literals is supported the same as >> other statements. >> >> >> >> Here's an example selecting a row from 2 tables, and issuing updates >> for each row if a condition is met: >> >> >> >> BEGIN TRANSACTION; >> >> SELECT * FROM users WHERE name='blake' AS user; >> >> SELECT * from cars WHERE model='pinto' AS car; >> >> UPDATE users SET miles_driven = user.miles_driven + 30 WHERE >> name='blake'; >> >> UPDATE cars SET miles_driven = car.miles_driven + 30 WHERE >> model='pinto'; >> >> COMMIT TRANSACTION IF car.is_running; >> >> >> >> This can be simplified by naming the updates with an AS <name> syntax. >> If updates are named, a corresponding read is generated behind the scenes >> and its values inform the update. >> >> >> >> Here's an example, the query is functionally identical to the previous >> query. In the case of the user update, a read is still performed behind the >> scenes to enable the calculation of miles_driven + 30, but doesn't need to >> be named since it's not referenced anywhere else. >> >> >> >> BEGIN TRANSACTION; >> >> UPDATE users SET miles_driven += 30 WHERE name='blake'; >> >> UPDATE cars SET miles_driven += 30 WHERE model='pinto' AS car; >> >> COMMIT TRANSACTION IF car.is_running; >> >> >> >> Here’s another example, performing the canonical bank transfer: >> >> >> >> BEGIN TRANSACTION; >> >> UPDATE accounts SET balance += 100 WHERE name='blake' AS blake; >> >> UPDATE accounts SET balance -= 100 WHERE name='benedict' AS benedict; >> >> COMMIT TRANSACTION IF blake EXISTS AND benedict.balance >= 100; >> >> >> >> As you can see from the examples, column values can be referenced via >> a dot syntax, ie: <select_name>.<column> -> select1.value. Since the read >> portion of the transaction is performed before evaluating conditions or >> applying updates, values read can be freely applied to non-primary key >> values in updates. Select statements used either in checking a condition or >> creating an update must be restricted to a single row, either by specifying >> the full primary key or a limit of 1. Multi-row selects are allowed, but >> only for returning data to the client (see below). >> >> >> >> For evaluating conditions, = & != are available for all types, <, <=, >> >, >= are available for numerical types, and EXISTS, NOT EXISTS can be used >> for partitions, rows, and values. If any column references cannot be >> satisfied by the result of the reads, the condition implicitly fails. This >> prevents having to include a bunch of exists statements. >> > >> > Is there a new keyword for “partition (not) exists” or is it inferred >> by the select? >> > >> >> >> >> On completion, an operation would return a boolean value indicating >> the operation had been applied, and a result set for each named select (but >> not named update). We could also support an optional RETURN keyword, which >> would allow the user to only return specific named selects (ie: RETURN >> select1, select2). >> >> >> > >> > The returned result set is after the updates are applied? >> > >> > >> >> Let me know what you think! >> >> >> >> Blake >> >
