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> 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
> > >>
> >
> >
