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 <[email protected]>
> > 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:
> >> * [email protected] <[email protected]> [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
> >>
>
>
