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