4 ms·

> SQL is fine, most of the quirky way of stating things is because it's declarative No, it's quirky because of its semantics. To quote Erik Meijer "SQL is rif

> SQL is fine, most of the quirky way of stating things is because it's declarative No, it's quirky because of its semantics. To quote Erik Meijer "SQL is rife with noncompositional features. For example, the semantics of NULL is a big mess: why does adding the number 13 to a NULL value, 13+NULL, return NULL, but summing the same two values, SUM(13, NULL), returns 13?" SQL also has no means of abstraction, i.e. defining parameterizable queries. This is one reason that LINQ exists.

noway421 9y ago
Coming from javascript, that doesn't sound all too bad! > 13+null 13 > 13+undefined NaN
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 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.
da_chicken 9y ago
> For example, the semantics of NULL is a big mess: why does adding the number 13 to a NULL value, 13+NULL, return NULL, but summing the same two values, SUM(13, NULL), returns 13? Because aggregate functions explicitly ignore NULLs. All aggregate functions do that including those like `AVG()` and `COUNT()`. Like, I understand the complaint, but aggregate functions don't work like operators. Operators work on data in the same row or result set. Aggregates work on data in the same column, even if it's a column created by the query. If you understand the relational model, then semantically they're not the same operation even though they might both involve addition. This complaint is like saying, "When I take an int and add 2 or multiply by 2 I get a new int. Why when add 2 to a string does it give me a the same string with a 2 at the end, but when I multiply a string by 2 it gives me the same string twice?" The answer is that you have to understand the semantics of operator overloading. Operator overloading isn't bad, but it does mean that you have to understand the underlying data type and operator models. You don't get to cheat just because you know what the operator does in one area. > SQL also has no means of abstraction, i.e. defining parameterizable queries. That's hardly a unique failing of a language. JavaScript, HTML, CSS, Python, shell, PHP, ASP, etc. All of them are vulnerable to code injection because there's essentially no way for a program to validate itself when it's interpretive and allowed to be dynamic. With SQL people decided that validating the dynamically generated code is the job of your data store provider since that's done client site (i.e., web server) and only the client knows enough to correctly build the query. Since you essentially already need a data store provider anyways, even if that provider is ODBC or one that you roll yourself, it's really not a significant problem if you have any idea what you're doing to write parameterized queries. Even if you do logically separate data and code, you still have to have a way to define it as such in whatever language your program is written in. In other words, a fictional SQL language which completely separates data from code would behave identically in practice to current SQL and current parameterized queries. We're not really gaining anything here. The real problem with SQL is that the relational model is particularly poor with serializing hierarchical structures, and objects are inherently hierarchical.

noway421 9y ago

Coming from javascript, that doesn't sound all too bad! > 13+null 13 > 13+undefined NaN

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

da_chicken 9y ago

> For example, the semantics of NULL is a big mess: why does adding the number 13 to a NULL value, 13+NULL, return NULL, but summing the same two values, SUM(13, NULL), returns 13? Because aggregate functions explicitly ignore NULLs. All aggregate functions do that including those like `AVG()` and `COUNT()`. Like, I understand the complaint, but aggregate functions don't work like operators. Operators work on data in the same row or result set. Aggregates work on data in the same column, even if it's a column created by the query. If you understand the relational model, then semantically they're not the same operation even though they might both involve addition. This complaint is like saying, "When I take an int and add 2 or multiply by 2 I get a new int. Why when add 2 to a string does it give me a the same string with a 2 at the end, but when I multiply a string by 2 it gives me the same string twice?" The answer is that you have to understand the semantics of operator overloading. Operator overloading isn't bad, but it does mean that you have to understand the underlying data type and operator models. You don't get to cheat just because you know what the operator does in one area. > SQL also has no means of abstraction, i.e. defining parameterizable queries. That's hardly a unique failing of a language. JavaScript, HTML, CSS, Python, shell, PHP, ASP, etc. All of them are vulnerable to code injection because there's essentially no way for a program to validate itself when it's interpretive and allowed to be dynamic. With SQL people decided that validating the dynamically generated code is the job of your data store provider since that's done client site (i.e., web server) and only the client knows enough to correctly build the query. Since you essentially already need a data store provider anyways, even if that provider is ODBC or one that you roll yourself, it's really not a significant problem if you have any idea what you're doing to write parameterized queries. Even if you do logically separate data and code, you still have to have a way to define it as such in whatever language your program is written in. In other words, a fictional SQL language which completely separates data from code would behave identically in practice to current SQL and current parameterized queries. We're not really gaining anything here. The real problem with SQL is that the relational model is particularly poor with serializing hierarchical structures, and objects are inherently hierarchical.