3 ms·
Sure SQL can be awkward, but in your example the + operator is not the same as the SUM() function, so it's not surprising that they wouldn't work the same eithe
by manigandham 9y ago
Sure SQL can be awkward, but in your example the + operator is not the same as the SUM() function, so it's not surprising that they wouldn't work the same either. SUM() will probably cast the null into a 0 first in most databases while the + operator has no proper guidance on what to do with a null value.
Nullability is required to represent empty values and every database has ISNULL() and IFNULL/COALESCE() and both of those read fine to me but I can see why they're strange.
I'm not sure what you mean by abstraction though since CTEs, joins, window functions, table values, UDFs, views, and more can all be used to compose logic and the language does support parameters, cursors, if/else/case statements, ... what's missing here?
- willtim 9y ago> 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 different. In mathematics and most programming languages, summation is defined in terms of the (+) operator. > Nullability is required to represent empty values I have no problem with (optional) nullability. > 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.
- 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 agoThe 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.