I think we’ve converged on a starting syntax. Are there any additional comments before I open a JIRA?
> On Jun 16, 2022, at 10:33 AM, Blake Eggleston <beggles...@apple.com> wrote: > > I think in any scenario where the same cell is updated multiple times, the > last one would win. The final result for s3 in your example would be 2 > >> On Jun 16, 2022, at 10:31 AM, Jon Meredith <jmeredit...@gmail.com >> <mailto:jmeredit...@gmail.com>> wrote: >> >> The reason I brought up static columns was for cases where multiple >> statements update them and there could be ambiguity. >> >> CREATE TABLE tbl >> { >> pk1 int, >> ck2 int, >> s3 static int, >> r4 static int, >> PRIMARY KEY (pk1, ck2) >> } >> >> BEGIN TRANSACTION >> UPDATE tbl SET s3=1, r4=1 WHERE pk1=1 AND ck2=1; >> UPDATE tbl SET s3=2, r4=2 WHERE pk1=1 AND ck2=2; >> COMMIT TRANSACTION >> >> What should the final value be for s3? >> >> This makes me realize I don't understand how upsert statements that touch >> the same row would be applied in general within a transaction. >> If the plan is for only-once-per-row within a transaction, then I think >> regular columns and static columns should be split into their own UPSERT >> statements. >> >> On Thu, Jun 16, 2022 at 10:40 AM Benedict Elliott Smith <bened...@apache.org >> <mailto:bened...@apache.org>> wrote: >> I like Postgres' approach of letting you declare an exceptional condition >> and failing if there is not precisely one result (though I would prefer to >> differentiate between 0 row->Null and 2 rows->first row), but once you >> permit coercing to NULL I think you have to then treat it like NULL and >> permit arithmetic (that itself yields NULL) >> >> This is explicitly stipulated in ANSI SQL 92, in 6.12 <numeric value >> expression>: >> >> General Rules >> >> 1) If the value of any <numeric primary> simply contained in a >> <numeric value expression> is the null value, then the result of >> the <numeric value expression> is the null value. >> >> >> On 2022/06/16 16:02:33 Blake Eggleston wrote: >> > Yeah I'd say NULL is fine for condition evaluation. Reference assignment >> > is a little trickier. Assigning null to a column seems ok, but we should >> > raise an exception if they're doing math or something that expects a >> > non-null value >> > >> > > On Jun 16, 2022, at 8:46 AM, Benedict Elliott Smith <bened...@apache.org >> > > <mailto:bened...@apache.org>> wrote: >> > > >> > > AFAICT that standard addresses server-side cursors, not the assignment >> > > of a query result to a variable. Could you point to where it addresses >> > > variable assignment? >> > > >> > > Postgres has a similar concept, SELECT INTO[1], and it explicitly >> > > returns NULL if there are no result rows, unless STRICT is specified in >> > > which case an error is returned. My recollection is that T-SQL is also >> > > fine with coercing no results to NULL when assigning to a variable or >> > > using it in a sub-expression. >> > > >> > > I'm in favour of expanding our functionality here, but I do not see >> > > anything fundamentally problematic about the proposal as it stands. >> > > >> > > [1] >> > > https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW >> > > >> > > <https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW> >> > > >> > > >> > > >> > > On 2022/06/13 14:52:41 Konstantin Osipov wrote: >> > >> * bened...@apache.org <mailto:bened...@apache.org> <bened...@apache.org >> > >> <mailto:bened...@apache.org>> [22/06/13 17:37]: >> > >>> I believe that is a MySQL specific concept. This is one problem with >> > >>> mimicking SQL – it’s not one thing! >> > >>> >> > >>> In T-SQL, a Boolean expression is TRUE, FALSE or UNKNOWN[1], and a >> > >>> NULL value submitted to a Boolean operator yields UNKNOWN. >> > >>> >> > >>> IF (X) THEN Y does not run Y if X is UNKNOWN; >> > >>> IF (X) THEN Y ELSE Z does run Z if X is UNKNOWN. >> > >>> >> > >>> So, I think we have evidence that it is fine to interpret NULL >> > >>> as “false” for the evaluation of IF conditions. >> > >> >> > >> NOT FOUND handler is in ISO/IEC 9075-4:2003 13.2 <handler declaration> >> > >> >> > >> In Cassandra results, there is no way to distinguish null values >> > >> from absence of a row. Branching, thus, without being able to >> > >> branch based on the absence of a row, whatever specific syntax >> > >> is used for such branching, is incomplete. >> > >> >> > >> More broadly, SQL/PSM has exception and condition statements, not >> > >> just IF statements. >> > >> >> > >> -- >> > >> Konstantin Osipov, Moscow, Russia >> > >> >> > >> > >
