The replies got trashed pretty badly in the responses. When you say: "Agree it's better to reuse existing syntax than invent new syntax."
Which syntax are you referring to? Patrick On Mon, Aug 22, 2022 at 1:36 AM Avi Kivity via dev <dev@cassandra.apache.org> wrote: > Agree it's better to reuse existing syntax than invent new syntax. > > On 8/21/22 16:52, Konstantin Osipov wrote: > > * Avi Kivity via dev <dev@cassandra.apache.org> [22/08/14 15:59]: > > > > MySQL supports SELECT <expr_list> INTO <var_list> FROM ... WHERE > > ... > > > > PostgreSQL supports pretty much the same syntax. > > > > Maybe instead of LET use the ANSI/MySQL/PostgreSQL DECLARE var TYPE and > > MySQL/PostgreSQL SELECT ... INTO? > > > >> 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 inCASSANDRA-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 > >>>> > >
