I am +1 on IS NOT NULL/IS NULL instead of EXISTS/NOT EXISTS
Not requiring (but allowing) SELECT on LET Patrick On Mon, Aug 15, 2022 at 11:01 AM Caleb Rackliffe <calebrackli...@gmail.com> wrote: > Monday Morning Caleb has digested, and here's where I am... > > 1.) I have no problem w/ having SELECT on the RHS of a LET assignment, and > to be honest, this may make some implementation things easier for me (i.e. > the encapsulation of SELECT within LET) > 2.) I'm in favor of LET without a select, although I have no strong > feeling that it needs to be in v1. > 3.) I like Benedict's tuple deconstruction idea, as it restores some of > the notational convenience of the previous proposal. Again, though, I don't > have a strong feeling this needs to be in v1. > 3.b.) When we do implement tuple deconstruction, I'd be in favor of > supporting a single level of deconstruction to begin with. > > Having said all that, on Friday I finished a prototype (based on some of > Blake's previous work) of the syntax/grammar we've more or less agreed upon > here, including an implementation of what I described as option #5 above: > https://github.com/maedhroz/cassandra/commits/CASSANDRA-17719-prototype > > To look at specific examples, see these tests: > https://github.com/maedhroz/cassandra/blob/CASSANDRA-17719-prototype/test/distributed/org/apache/cassandra/distributed/test/accord/AccordIntegrationTest.java > > There are only two things that aren't yet congruent w/ our discussion > above, but they should both be trivial to fix: > > 1.) I'm still using EXISTS/NOT EXISTS instead of IS NOT NULL/IS NULL. > 2.) I don't require SELECT on the RHS of LET yet. > > If I were to just fix those two items, would we be in agreement on this > being both the core of the syntax we want and compatible w/ the wish list > for future items? > > > On Sun, Aug 14, 2022 at 12:25 PM Benedict Elliott Smith < > bened...@apache.org> wrote: > >> >> >> >> Verbose version: >> LET (a) = SELECT val FROM table >> IF a > 1 THEN... >> >> Less verbose version: >> LET a = SELECT val FROM table >> IF a.val > 1 THEN... >> >> >> >> My intention is that these are actually two different ways of expressing >> the same thing, both supported and neither intended to be more or less >> verbose than the other. The advantage of permitting both is that you can >> also write >> >> LET a = SELECT val FROM table >> IF a IS NOT NULL AND a.val IS NULL THEN … >> >> Alternatively, for non-queries: >> LET x = SELECT someFunc() AS v1, someOtherFunc() AS v2 >> or less verbose: >> LET x = (someFunc() AS v1, someOtherFunc() as v2) >> LET (v1, v2) = (someFunc(), someOtherFunc()) >> >> >> I personally prefer clarity over any arbitrary verbosity/succinct >> distinction, but we’re in general “taste” territory here. Since this syntax >> includes the SELECT on the RHS, it makes sense to only require this for >> situations where a query is being performed. Though I think if SELECT >> without a FROM is supported then we will likely end up supporting *all >> of the above*. >> >> Weighing in on the "SELECT without a FROM," I think that is fine and, as >> Avi stated >> >> >> Yep, definitely fine. Question is just whether we bother to offer it. >> Also, evidently, whether we support LET *without* a SELECT on the RHS. I >> am strongly in favour of this, as *requiring* a SELECT even when there’s >> no table involved is counter-intuitive to me, as LET is now a distinct >> concept that looks like variable declaration in other languages. >> >> Nested: >> LET (x, y) = SELECT x, y FROM… >> >> >> Deconstruction here refers to the above, i.e. extracting variables x and >> y from the tuple on the RHS >> >> Nesting is just a question of whether we support either nested tuple >> declarations, or nested deconstruction, which might include any of the >> following: >> >> LET (x, (y, z)) = SELECT (x, (y, z)) FROM… >> LET (x, (y, z)) = SELECT x, someTuple FROM… >> LET (x, (y, z)) = (SELECT x FROM.., SELECT y, x FROM…)) >> LET (x, (y, z)) = (someFunc(), SELECT y, z FROM…) >> LET (x, yAndZ) = (someFunc(), SELECT y, z FROM…) >> >> IMO, once you start supporting features they need to be sort of >> intuitively discoverable by users, so that a concept can be used in all >> places you might expect. >> >> But I would be fine with an arbitrary restriction of at most one SELECT >> on the RHS, or even ONLY a SELECT *or* some other tuple, and at most one >> level of deconstruction of the RHS. >> >> >> >> >> >> On 14 Aug 2022, at 18:04, Patrick McFadin <pmcfa...@gmail.com> wrote: >> >> Let me just state my bias right up front. For any kind of QL I lean >> heavily toward verbose and explicit based on their lifecycle. A CQL query >> will probably need to be understood by the next person looking at it, and a >> few seconds saved typing isn't worth the potential misunderstanding later. >> My opinion is formed by having to be the second person many times. :D >> >> I just want to make sure I have the syntax you are proposing. >> >> Verbose version: >> LET (a) = SELECT val FROM table >> IF a > 1 THEN... >> >> Less verbose version: >> LET a = SELECT val FROM table >> IF a.val > 1 THEN... >> >> Alternatively, for non-queries: >> LET x = SELECT someFunc() AS v1, someOtherFunc() AS v2 >> or less verbose: >> LET x = (someFunc() AS v1, someOtherFunc() as v2) >> LET (v1, v2) = (someFunc(), someOtherFunc()) >> >> Weighing in on the "SELECT without a FROM," I think that is fine and, as >> Avi stated, already present in the SQL world. I would prefer that over >> 'SELECT func() FROM dual;' (Looking at you, Oracle) >> >> Finally, on the topic of deconstructing SELECT statements instead of >> nesting. If I understand the argument here, I would favor deconstructing >> over nesting if there is a choice. I think this is what that choice would >> look like. >> >> Deconstructed: >> LET x = SELECT x FROM ... >> LET y = SELECT y FROM ... >> >> Nested: >> LET (x, y) = ((SELECT x FROM…), (SELECT y FROM)) >> >> I'm trying to summate but let me know if I missed something. I apologize >> in advance to Monday morning Caleb, who will have to digest this thread. >> >> Patrick >> >> On Sun, Aug 14, 2022 at 9:00 AM Benedict Elliott Smith < >> bened...@apache.org> wrote: >> >>> >>> >>> I think SQL dialects require subqueries to be parenthesized (not sure). >>> If that's the case I think we should keep the tradition. >>> >>> >>> This isn’t a sub-query though, since LET is not a query. If we permit at >>> most one SELECT, and do not permit mixing SELECT with constant assignments, >>> I don’t see why we would require parentheses. >>> >>> I see no harm in making FROM optional, as it's recognized by other SQL >>> dialects. >>> Absolutely, this just flows naturally from having tuples. There's no >>> difference between "SELECT (a, b)" and "SELECT a_but_a_is_a_tuple”. >>> >>> >>> Neither of these things are supported today, and they’re no longer >>> necessary with this syntax proposal. The downside of splitting SELECT and >>> LET is that there’s no impetus to improve the former. So the question was >>> really whether we bother to improve it anyway, not whether or not they >>> would be good improvements (I think they obviously are). >>> >>> I think this can be safely deferred. Most people would again separate it >>> into separate LETs. >>> >>> That implies we’ll permit deconstructing a tuple variable in a LET. This >>> makes sense to me, but is roughly equivalent to nested deconstruction. It >>> might be that v1 we only support deconstructing SELECT statements, but I >>> guess all of this is probably up to the implementor. >>> >>> I'd add (to the specification) that LETs cannot override a previously >>> defined variable, just to reduce ambiguity. >>> >>> Yep, this was already agreed way back with the earlier proposal. >>> >>> >>> On 14 Aug 2022, at 16:30, Avi Kivity <a...@scylladb.com> wrote: >>> >>> >>> On 14/08/2022 17.50, Benedict Elliott Smith wrote: >>> >>> >>> > SELECT and LET incompatible once comparisons become valid selectors >>> >>> I don’t think this would be ambiguous, as = is required in the LET >>> syntax as we have to bind the result to a variable name. >>> >>> But, I like the deconstructed tuple syntax improvement over “Option 6”. >>> This would also seem to easily support assigning from non-query statements, >>> such as LET (a, b) = (someFunc(), someOtherFunc(?)) >>> >>> I don’t think it is ideal to depend on relative position in the tuple >>> for assigning results to a variable name, as it leaves more scope for >>> errors. It would be nice to have a simple way to deconstruct safely. But, I >>> think this proposal is good, and I’d be fine with it as an alternative if >>> others concur. I agree that seeing the SELECT independently may be more >>> easily recognisable to users. >>> >>> With this approach there remains the question of how we handle single >>> column results. I’d be inclined to treat in the following way: >>> >>> LET (a) = SELECT val FROM table >>> IF a > 1 THEN... >>> >>> LET a = SELECT val FROM table >>> IF a.val > 1 THEN... >>> >>> >>> I think SQL dialects require subqueries to be parenthesized (not sure). >>> If that's the case I think we should keep the tradition. >>> >>> >>> ---- >>> There is also the question of whether we support SELECT without a FROM >>> clause, e.g. >>> LET x = SELECT someFunc() AS v1, someOtherFunc() AS v2 >>> >>> Or just LET (since they are no longer equivalent) >>> e.g. >>> LET x = (someFunc() AS v1, someOtherFunc() as v2) >>> LET (v1, v2) = (someFunc(), someOtherFunc()) >>> >>> >>> I see no harm in making FROM optional, as it's recognized by other SQL >>> dialects. >>> >>> >>> ---- >>> Also since LET is only binding variables, is there any reason we >>> shouldn’t support multiple SELECT assignments in a single LET?, e.g. >>> LET (x, y) = ((SELECT x FROM…), (SELECT y FROM)) >>> >>> >>> What if an inner select returns a tuple? Would y be a tuple? >>> >>> >>> I think this is redundant and atypical enough to not be worth >>> supporting. Most people would use separate LETs. >>> >>> >>> ---- >>> Also whether we support tuples in SELECT statements anyway, e.g. >>> LET (tuple1, tuple2) = SELECT (a, b), (c, d) FROM.. >>> IF tuple1.a > 1 AND tuple2.d > 1… >>> >>> >>> Absolutely, this just flows naturally from having tuples. There's no >>> difference between "SELECT (a, b)" and "SELECT a_but_a_is_a_tuple". >>> >>> >>> >>> ---- >>> and whether we support nested deconstruction, e.g. >>> LET (a, b, (c, d)) = SELECT a, b, someTuple FROM.. >>> IF a > 1 AND d > 1… >>> >>> >>> I think this can be safely deferred. Most people would again separate it >>> into separate LETs. >>> >>> >>> I'd add (to the specification) that LETs cannot override a previously >>> defined variable, just to reduce ambiguity. >>> >>> >>> >>> >>> >>> >>> >>> >>> On 14 Aug 2022, at 13:55, Avi Kivity via dev <dev@cassandra.apache.org> >>> <dev@cassandra.apache.org> wrote: >>> >>> >>> On 14/08/2022 01.29, Benedict Elliott Smith wrote: >>> >>> >>> I’ll do my best to express with my thinking, as well as how I would >>> explain the feature to a user. >>> >>> My mental model for LET statements is that they are simply SELECT >>> statements where the columns that are selected become variables accessible >>> anywhere in the scope of the transaction. That is to say, you should be >>> able to run something like s/LET/SELECT and s/([^=]+)=([^,]+)(,|$)/\2 AS >>> \1\3/g on the columns of a LET statement and produce a valid SELECT >>> statement, and vice versa. Both should perform identically. >>> >>> e.g. >>> SELECT pk AS key, v AS value FROM table >>> >>> => >>> LET key = pk, value = v FROM table >>> >>> >>> "=" is a CQL/SQL operator. Cassandra doesn't support it yet, but SQL >>> supports selecting comparisons: >>> >>> >>> $ psql >>> psql (14.3) >>> Type "help" for help. >>> >>> avi=# SELECT 1 = 2, 3 = 3, NULL = NULL; >>> ?column? | ?column? | ?column? >>> ----------+----------+---------- >>> f | t | >>> (1 row) >>> >>> >>> Using "=" as a syntactic element in LET would make SELECT and LET >>> incompatible once comparisons become valid selectors. Unless they become >>> mandatory (and then you'd write "LET q = a = b" if you wanted to select a >>> comparison). >>> >>> >>> I personally prefer the nested query syntax: >>> >>> >>> LET (a, b, c) = (SELECT foo, bar, x+y FROM ...); >>> >>> >>> So there aren't two similar-but-not-quite-the-same syntaxes. SELECT is >>> immediately recognizable by everyone as a query, LET is not. >>> >>> >>> >>> Identical form, identical behaviour. Every statement should be directly >>> translatable with some simple text manipulation. >>> >>> We can then make this more powerful for users by simply expanding SELECT >>> statements, e.g. by permitting them to declare constants and tuples in the >>> column results. In this scheme LET x = * is simply syntactic sugar for LET >>> x = (pk, ck, field1, …) This scheme then supports options 2, 4 and 5 all at >>> once, consistently alongside each other. >>> >>> Option 6 is in fact very similar, but is strictly less flexible for the >>> user as they have no way to declare multiple scalar variables without >>> scoping them inside a tuple. >>> >>> e.g. >>> LET key = pk, value = v FROM table >>> IF key > 1 AND value > 1 THEN... >>> >>> => >>> LET row = SELECT pk AS key, v AS value FROM table >>> IF row.key > 1 AND row.value > 1 THEN… >>> >>> However, both are expressible in the existing proposal, as if you prefer >>> this naming scheme you can simply write >>> >>> LET row = (pk AS key, v AS value) FROM table >>> IF row.key > 1 AND row.value > 1 THEN… >>> >>> With respect to auto converting single column results to a scalar, we do >>> need a way for the user to say they care whether the row was null or the >>> column. I think an implicit conversion here could be surprising. However we >>> could implement tuple expressions anyway and let the user explicitly >>> declare v as a tuple as Caleb has suggested for the existing proposal as >>> well. >>> >>> Assigning constants or other values not selected from a table would also >>> be a little clunky: >>> >>> LET v1 = someFunc(), v2 = someOtherFunc(?) >>> IF v1 > 1 AND v2 > 1 THEN… >>> >>> => >>> LET row = SELECT someFunc() AS v1, someOtherFunc(?) AS v2 >>> IF row.v1 > 1 AND row.v2 > 1 THEN... >>> >>> That said, the proposals are *close* to identical, it is just slightly >>> more verbose and slightly less flexible. >>> >>> Which one would be most intuitive to users is hard to predict. It might >>> be that Option 6 would be slightly easier, but I’m unsure if there would be >>> a huge difference. >>> >>> >>> On 13 Aug 2022, at 16:59, Patrick McFadin <pmcfa...@gmail.com> wrote: >>> >>> I'm really happy to see CEP-15 getting closer to a final implementation. >>> I'm going to walk through my reasoning for your proposals wrt trying to >>> explain this to somebody new. >>> >>> Looking at all the options, the first thing that comes up for me is the >>> Cassandra project's complicated relationship with NULL. We have prior art >>> with EXISTS/NOT EXISTS when creating new tables. IS NULL/IS NOT NULL is >>> used in materialized views similarly to proposals 2,4 and 5. >>> >>> CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] [keyspace_name.]view_name >>> AS SELECT [ (column_list) ] >>> FROM [keyspace_name.]table_name >>> [ WHERE column_name IS NOT NULL >>> [ AND column_name IS NOT NULL ... ] ] >>> [ AND relation [ AND ... ] ] >>> PRIMARY KEY ( column_list ) >>> [ WITH [ table_properties ] >>> [ [ AND ] CLUSTERING ORDER BY (cluster_column_name order_option) ] ] ; >>> >>> Based on that, I believe 1 and 3 would just confuse users, so -1 on >>> those. >>> >>> Trying to explain the difference between row and column operations with >>> LET, I can't see the difference between a row and column in #2. >>> >>> #4 introduces a boolean instead of column names and just adds more >>> syntax. >>> >>> #5 is verbose and, in my opinion, easier to reason when writing a query. >>> Thinking top down, I need to know if these exact rows and/or column values >>> exist before changing them, so I'll define them first. Then I'll iterate >>> over the state I created in my actual changes so I know I'm changing >>> precisely what I want. >>> >>> #5 could use a bit more to be clearer to somebody who doesn't write CQL >>> queries daily and wouldn't require memorizing subtle differences. It should >>> be similar to all the other syntax, so learning a little about CQL will let >>> you move into more without completely re-learning the new syntax. >>> >>> So I propose #6) >>> BEGIN TRANSACTION >>> LET row1 = SELECT * FROM ks.tbl WHERE k=0 AND c=0; <-- * selects all >>> columns >>> LET row2 = SELECT v FROM ks.tbl WHERE k=1 AND c=0; >>> SELECT row1, row2 >>> IF row1 IS NULL AND row2.v = 3 THEN >>> INSERT INTO ks.tbl (k, c, v) VALUES (0, 0, 1); >>> END IF >>> COMMIT TRANSACTION >>> >>> I added the SELECT in the LET just so it's straightforward, you are >>> reading, and it's just like doing a regular select, but you are assigning >>> it to a variable. >>> >>> I removed the confusing 'row1.v' and replaced it with 'row1' I can't >>> see why you would need the '.v' vs having the complete variable I >>> created in the statement above. >>> >>> EOL >>> >>> Patrick >>> >>> On Thu, Aug 11, 2022 at 1:37 PM Caleb Rackliffe < >>> calebrackli...@gmail.com> wrote: >>> >>>> ...and one more option... >>>> >>>> 5.) Introduce tuple assignments, removing all ambiguity around row vs. >>>> column operations. >>>> >>>> BEGIN TRANSACTION >>>> LET row1 = * FROM ks.tbl WHERE k=0 AND c=0; <-- * selects all columns >>>> LET row2 = (v) FROM ks.tbl WHERE k=1 AND c=0; >>>> SELECT row1.v, row2.v >>>> IF row1 IS NULL AND row2.v = 3 THEN >>>> INSERT INTO ks.tbl (k, c, v) VALUES (0, 0, 1); >>>> END IF >>>> COMMIT TRANSACTION >>>> >>>> >>>> >>>> On Thu, Aug 11, 2022 at 12:55 PM Caleb Rackliffe < >>>> calebrackli...@gmail.com> wrote: >>>> >>>>> via Benedict, here is a 4th option: >>>>> >>>>> 4.) Similar to #2, but don't rely on the key element being NULL. >>>>> >>>>> If the read returns no result, x effectively becomes NULL. Otherwise, >>>>> it remains true/NOT NULL. >>>>> >>>>> BEGIN TRANSACTION >>>>> LET x = true FROM ks.tbl WHERE k=0 AND c=0; >>>>> LET row2_v = v FROM ks.tbl WHERE k=1 AND c=0; >>>>> SELECT x, row2_v >>>>> IF x IS NULL AND row2_v = 3 THEN >>>>> INSERT INTO ks.tbl (k, c, v) VALUES (0, 0, 1); >>>>> END IF >>>>> COMMIT TRANSACTION >>>>> >>>>> On Thu, Aug 11, 2022 at 12:12 PM Caleb Rackliffe < >>>>> calebrackli...@gmail.com> wrote: >>>>> >>>>>> Hello again everyone! >>>>>> >>>>>> I've been working on a prototype >>>>>> <https://issues.apache.org/jira/browse/CASSANDRA-17719> in >>>>>> CASSANDRA-17719 for a grammar that roughly corresponds to what we've >>>>>> agreed >>>>>> on in this thread. One thing that isn't immediately obvious to me is how >>>>>> the LET syntax handles cases where we want to check for the plain >>>>>> existence >>>>>> of a row in IF. For example, in this hybrid of the originally proposed >>>>>> syntax and something more like what we've agreed on (and the RETURNING >>>>>> just >>>>>> to distinguish between that and SELECT), this could be pretty >>>>>> straightforward: >>>>>> >>>>>> BEGIN TRANSACTION >>>>>> SELECT v FROM ks.tbl WHERE k=0 AND c=0 AS row1; >>>>>> SELECT v FROM ks.tbl WHERE k=1 AND c=0 AS row2; >>>>>> RETURNING row1.v, row2.v >>>>>> IF row1 NOT EXISTS AND row2.v = 3 THEN >>>>>> INSERT INTO ks.tbl (k, c, v) VALUES (0, 0, 1); >>>>>> END IF >>>>>> COMMIT TRANSACTION >>>>>> >>>>>> The NOT EXISTS operator has row1 to work with. One the other hand, w/ >>>>>> the LET syntax and no naming of reads, it's not clear what the best >>>>>> solution would be. Here are a few possibilities: >>>>>> >>>>>> 1.) Provide a few built-in functions that operate on a whole result >>>>>> row. If we assume a SQL style IS NULL and IS NOT NULL (see my last post >>>>>> here) for operations on particular columns, this probably eliminates the >>>>>> need for EXISTS/NOT EXISTS as well. >>>>>> >>>>>> BEGIN TRANSACTION >>>>>> LET row1_missing = notExists() FROM ks.tbl WHERE k=0 AND c=0; >>>>>> LET row2_v = v FROM ks.tbl WHERE k=1 AND c=0; >>>>>> SELECT row1_missing, row2_v >>>>>> IF row1_missing AND row2_v = 3 THEN >>>>>> INSERT INTO ks.tbl (k, c, v) VALUES (0, 0, 1); >>>>>> END IF >>>>>> COMMIT TRANSACTION >>>>>> >>>>>> 2.) Assign and check the first primary key element to determine >>>>>> whether the row exists. >>>>>> >>>>>> BEGIN TRANSACTION >>>>>> LET row1_k = k FROM ks.tbl WHERE k=0 AND c=0; >>>>>> LET row2_v = v FROM ks.tbl WHERE k=1 AND c=0; >>>>>> SELECT row1_k, row2_v >>>>>> IF row1_k IS NULL AND row2_v = 3 THEN >>>>>> INSERT INTO ks.tbl (k, c, v) VALUES (0, 0, 1); >>>>>> END IF >>>>>> COMMIT TRANSACTION >>>>>> >>>>>> 3.) Reconsider the LET concept toward something that allows us to >>>>>> explicitly name our reads again. >>>>>> >>>>>> BEGIN TRANSACTION >>>>>> WITH (SELECT v FROM ks.tbl WHERE k=0 AND c=0) AS row1; >>>>>> WITH (SELECT v FROM ks.tbl WHERE k=1 AND c=0) AS row2; >>>>>> SELECT row1.v, row2.v >>>>>> IF row1 NOT EXISTS AND row2.v = 3 THEN >>>>>> INSERT INTO ks.tbl (k, c, v) VALUES (0, 0, 1); >>>>>> END IF >>>>>> COMMIT TRANSACTION >>>>>> >>>>>> I don't have a strong affinity for any of these, although #1 seems >>>>>> the most awkward. >>>>>> >>>>>> Does anyone have any other alternatives? Preference for one of the >>>>>> above options? >>>>>> >>>>>> Thanks! >>>>>> >>>>>> On Fri, Jul 22, 2022 at 11:21 AM Caleb Rackliffe < >>>>>> calebrackli...@gmail.com> wrote: >>>>>> >>>>>>> Avi brought up an interesting point around NULLness checking in >>>>>>> CASSANDRA-17762 >>>>>>> <https://issues.apache.org/jira/browse/CASSANDRA-17762>... >>>>>>> >>>>>>> In SQL, any comparison with NULL is NULL, which is interpreted as >>>>>>>> FALSE in a condition. To test for NULLness, you use IS NULL or IS NOT >>>>>>>> NULL. >>>>>>>> But LWT uses IF col = NULL as a NULLness test. This is likely to >>>>>>>> confuse >>>>>>>> people coming from SQL and hamper attempts to extend the dialect. >>>>>>> >>>>>>> >>>>>>> We can leave that Jira open to address what to do in the legacy LWT >>>>>>> case, but I'd support a SQL-congruent syntax here (IS NULL or IS >>>>>>> NOT NULL), where we have something closer to a blank slate. >>>>>>> >>>>>>> Thoughts? >>>>>>> >>>>>>> On Thu, Jun 30, 2022 at 6:25 PM Abe Ratnofsky <a...@aber.io> wrote: >>>>>>> >>>>>>>> The new syntax looks great, and I’m really excited to see this >>>>>>>> coming together. >>>>>>>> >>>>>>>> One piece of feedback on the proposed syntax is around the use of >>>>>>>> “=“ as a declaration in addition to its current use as an equality >>>>>>>> operator >>>>>>>> in a WHERE clause and an assignment operator in an UPDATE: >>>>>>>> >>>>>>>> BEGIN TRANSACTION >>>>>>>> LET car_miles = miles_driven, car_is_running = is_running FROM >>>>>>>> cars WHERE model=’pinto’ >>>>>>>> LET user_miles = miles_driven FROM users WHERE name=’blake’ >>>>>>>> SELECT something else from some other table >>>>>>>> IF NOT car_is_running THEN ABORT >>>>>>>> UPDATE users SET miles_driven = user_miles + 30 WHERE >>>>>>>> name='blake'; >>>>>>>> UPDATE cars SET miles_driven = car_miles + 30 WHERE model='pinto'; >>>>>>>> COMMIT TRANSACTION >>>>>>>> >>>>>>>> This is supported in languages like PL/pgSQL, but in a normal SQL >>>>>>>> query kind of local declaration is often expressed as an alias (SELECT >>>>>>>> col >>>>>>>> AS new_col), subquery alias (SELECT col) t, or common table expression >>>>>>>> (WITH t AS (SELECT col)). >>>>>>>> >>>>>>>> Here’s an example of an alternative to the proposed syntax that I’d >>>>>>>> find more readable: >>>>>>>> >>>>>>>> BEGIN TRANSACTION >>>>>>>> WITH car_miles, car_is_running AS (SELECT miles_driven, >>>>>>>> is_running FROM cars WHERE model=’pinto’), >>>>>>>> user_miles AS (SELECT miles_driven FROM users WHERE name=’blake’) >>>>>>>> IF NOT car_is_running THEN ABORT >>>>>>>> UPDATE users SET miles_driven = user_miles + 30 WHERE >>>>>>>> name='blake'; >>>>>>>> UPDATE cars SET miles_driven = car_miles + 30 WHERE model='pinto'; >>>>>>>> COMMIT TRANSACTION >>>>>>>> >>>>>>>> There’s also the option of naming the transaction like a subquery, >>>>>>>> and supporting LET via AS (this one I’m less sure about but wanted to >>>>>>>> propose anyway): >>>>>>>> >>>>>>>> BEGIN TRANSACTION t1 >>>>>>>> SELECT miles_driven AS t1.car_miles, is_running AS >>>>>>>> t1.car_is_running FROM cars WHERE model=’pinto’; >>>>>>>> SELECT miles_driven AS t1.user_miles FROM users WHERE >>>>>>>> name=’blake’; >>>>>>>> IF NOT car_is_running THEN ABORT >>>>>>>> UPDATE users SET miles_driven = user_miles + 30 WHERE >>>>>>>> name='blake'; >>>>>>>> UPDATE cars SET miles_driven = car_miles + 30 WHERE model='pinto'; >>>>>>>> COMMIT TRANSACTION >>>>>>>> >>>>>>>> This also has the benefit of resolving ambiguity in case of naming >>>>>>>> conflicts with existing (or future) column names. >>>>>>>> >>>>>>>> -- >>>>>>>> Abe >>>>>>>> >>>>>>> >>> >>> >>> >>
