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 >
