2 ms·
> In mathematics and most programming languages, summation is defined in terms of the (+) operator. (+) is not SUM. All languages treat these basic operators
by manigandham 9y ago
> In mathematics and most programming languages, summation is defined in terms of the (+) operator.
(+) is not SUM. All languages treat these basic operators as fluid logic that depends on surrounding context and data types. You can't add a null or string to a number in most languages either, and dynamic languages like Javascript will usually create equally strange results. Likewise (+) means concat if you're dealing with 2 strings. SUM() is an explicit function that clearly says that you're adding numbers, and within that function context making NULL = 0 is a reasonable coercion as they are mathematically equivalent.
> but I cannot parameterize a query
What is the parameterization issue exactly? Instead of a view, you use a stored procedure or function which accept parameters. Even normal queries can use parameters which you can then provide them separately, either as variables or even the output of other functions. You can even use functions to write dynamic SQL and then execute that if you want.
Views are usually meant as static projections (which are an abstraction) but you can still join them on some other table which can act as a control, or use if/else/case statements within the view.
- 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 ago