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>
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> 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
> > >
> > >
> > >
> > > On 2022/06/13 14:52:41 Konstantin Osipov wrote:
> > >> * bened...@apache.org <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
> > >>
> >
> >
>
