3 ms·
> (+) is not SUM. As I said, people expect a mathematical summation to be defined in terms of addition. Otherwise one has non-compositional semantics and broke
by willtim 9y ago
> (+) is not SUM.
As I said, people expect a mathematical summation to be defined in terms of addition. Otherwise one has non-compositional semantics and broken equational reasoning. Perhaps you care not for these properties, but many people do.
> All languages treat these basic operators as fluid logic that depends on surrounding context and data types.... Likewise (+) means concat if you're dealing with 2 strings
Syntax overloading is not relevant here. String concat is just sharing syntax, it is a different function. The issue is that the treatment of null is not consistent. It's possible to add null and still have all algebraic laws hold, so null itself is not the problem.
> within that function context making NULL = 0 is a reasonable coercion
IMHO no coercion is reasonable, especially one that is unexpected. They should have called your "SUM" something else and provided one defined in terms of (+).
> What is the parameterization issue exactly? Instead of a view, you use a stored procedure
Stored procedures are not SQL, they are a feature of various imperative languages with SQL as a subset. Typically I am not allowed to define a stored procedure when I query your database.The context was deficiencies in the standard SQL query language.
- manigandham 9y agoA: "I want to add 2 numbers" B: "Ok, what's the first number?" A: "1" B: "Ok, what's the second number?" A: "I dont know..." B: "Then I dont know the answer..." NULL is the absence of a value. It has no meaning in algebra. You cannot do anything mathematically with a NULL value so it always results in a NULL output regardless of the operator. If you do use the numeric functions, then it will attempt to convert to a number (0) first. Where is the inconsistency? It seems you want the database to automatically assume 0 for NULL when they are distinctly separate meanings and it would be far more confusing if that were so.
- dllthomas 9y agopostgres=# SELECT AVG(x) FROM (SELECT 1 AS x UNION SELECT NULL) t; avg ------------------------ 1.00000000000000000000 (1 row)
- willtim 9y ago> NULL is the absence of a value. It has no meaning in algebra. Null is a value in its own right. If null is added to e.g. the type of numbers, then it had better be given meaning (i.e. semantics). > You cannot do anything mathematically with a NULL value so it always results in a NULL output Returning null if any input is null seems reasonable, but again this is not true with SQL SUM: SUM [1, null] = 1. > Where is the inconsistency You seriously cannot see an inconsistency? Are you trolling? > It seems you want the database to automatically assume 0 for NULL Where on earth did you get that idea? You are defending the behaviour of SQL SUM, not me! I've already told you, I don't want coercions.
- dllthomas 9y agoIMO, thinking of it as coercion is wrong. Rather, many aggregate functions on many databases filter nulls first. For sum there's no difference, but for others (eg. average) there is. I make no particular claim as to whether that's the right choice.
- willtim 9y agoIt's the wrong choice, since they defined x+null=null and ignoring missing/undefined/error values (which null often represents) is dangerous in many domains. Users could have always filtered nulls manually, which would have made this dangerous behaviour explicit.
- dllthomas 9y agoI see your argument and it's a strong one, I merely haven't considered the issue deeply enough to have much confidence that there aren't other strong arguments the other way. I don't object to your making the claim, I merely wouldn't make it myself :) That said, I think the fact that there's a hard question here stems from SQL's conflation of "I don't know this" with "this is known to be absent". If NULL always represented the latter, implicitly omitting them from aggregates would be clearly correct.