3 ms·

> The + operator is not the same as the SUM() function, so it's not surprising that they wouldn't work the same No, it's very surprising that they are differen

• yazan94 9y ago
  >> I'm not sure what you mean by abstraction > There is no way to abstract queries over tables, joins, etc. Views allow me to re-use a particular concrete query, but I cannot parameterize a query. Not the person you replied to, but to me, you can "parameterize a query" by abstracting it away into a function or stored proc (SQL Server). Can you explain what you mean by it?
  • willtim 9y ago
    The context was the standard declarative SQL query language. I can't define stored procedures in my SQL query to your database, so I have to use something like LINQ to compose larger queries from smaller ones.
• 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 ago
      A: "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 ago
        postgres=# SELECT AVG(x) FROM (SELECT 1 AS x UNION SELECT NULL) t; avg ------------------------ 1.00000000000000000000 (1 row)
      • 9y ago