4 ms·

You make some good points about SQL's shortcomings but some of the claims you make are infactual or seem intentionally misleading. Since you mentioned SQL Serve

You make some good points about SQL's shortcomings but some of the claims you make are infactual or seem intentionally misleading. Since you mentioned SQL Server, I have to chime in with some corrections to your statements While you can't declare new variables within a SELECT statement, you can modify them with functions: DECLARE @foo INT; SELECT @foo = dbo.ComputeSomething(...), @foo + @bar AS Val1 FROM ... Additionally, SQL Server does have a boolean type by another name: bit. It is 1/0/NULL and you can convert the string values TRUE and FALSE into their respective numerical values. To modify your example to use proper SQL: DECLARE @x BIT; SELECT @x = 'TRUE' WHERE 1 <> 2 Or, to use a more real-world example, you'd probably use a CASE statement: SELECT @x = CASE WHEN 1 <> 2 THEN 1 ELSE 0 END Yes, it's verbose. But it's possible. (SQL's slogan, probably, and understandably the root of a lot of your remarks.) Finally, your GROUP BY / HAVING / WHERE example lacks an important distinction. HAVING <> WHERE. HAVING is specifically meant to deal with aggregate functions, while WHERE is a simple filter. You wouldn't use "HAVING PostId = @foo" any more than you might use "WHERE COUNT(*) > @bar". This is why it comes after the GROUP BY clause -- which you should think of as a "key" for the data that's being aggregated. Look, SQL is not perfect or near perfect. As with any language, it could use improvements. But to criticize a language based on ignorance, misleading statements, or use cases that are not meant for the language is fairly dishonest, in my opinion.

rspeele 8y ago
If I use your variable example I get: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. I'm assuming there is a way to make it work, but then it seems like it'd be kind of a messy imperative-style approach. Would the value of @foo persist between each row's evaluation, so now my query would be capable of (intentionally or not) stateful execution? Would that interfere with the optimizer's ability to re-order evaluation or would it be undefined behavior? Other than the special cased string value conversion, SQL server treats bit as just a very short integer type. You cannot supply a bit value alone to WHERE or CASE, you must use a comparison operator. And likewise, you cannot assign the result of a comparison expression to a bit, you must wrap it in CASE as you have demonstrated. In fact my own project, in an attempt to squeeze various dialects of SQL into a common type system, fakes booleans in T-SQL by inserting CASE expressions and (<> 0) comparisons as needed See example: http://rzsql.net/#80273F1AF9F7DB7776102BFA0814D4A7F01F2397 http://rzsql.net/#80273F1AF9F7DB7776102BFA0814D4A7F01F2397 If a normal programming language had special places where boolean expressions could appear (like in if and while loops), and boolean expressions did not produce values in and of themselves, it would stand out as being extremely hacky. Of course that is a complaint only for T-SQL, other dialects do have true bools. But it is emblematic of design undertaken without effort to unify fundamentals (The question not asked by T-SQL's designers: "What if predicate expressions were just like any other expression, and had a value?") and that is what I find distasteful about SQL in general. HAVING is another example of that lazy, slap-more-syntax-on design. As you correctly point out, HAVING evaluates after the GROUP BY, WHERE before, and that's why HAVING can refer to aggregate results. But if "WHERE" was just an operator it could be used in the order it was needed without hassle. In LINQ, you write: users .Where(u => u.AccountIsActivated) .GroupBy(u => u.Name) .Select(g => new { Name = g.Key, NumberOfUsersWithName = g.Count() }) .Where(g => g.NumberOfUsersWithName > 1) .Select(g => $"The name {g.Name} is shared by {g.NumberOfUsersWithName} users") The .Where before the grouping is the same function as the .Where after it. No need for a special case. Of course you could do the same thing in SQL without HAVING using a subquery: select 'The name ' + sq.Name + ' is shared by ' + sq.NumberOfUsersWithName + ' users' from (select u.Name, count(*) as NumberOfUsersWithName from Users u where u.AccountIsActivated = 1 group by u.Name) sq where sq.NumberOfUsersWithName > 1 But it's ugly, so they threw in HAVING and that lets you do it all in one query. select 'The name ' + u.Name + ' is shared by ' + count(*) + ' users' from Users u where u.AccountIsActivated = 1 group by u.Name having count(*) > 1 Understandable choice, because subqueries start to get unreadable fast. I believe this is due to the middle-out (select outer-stuff from inner-queries where more-outer-stuff) syntax, which gets hard to follow when nested more than a level or two. I find the front-to-back pipeline approach of LINQ or F#'s Seq module far easier to track as data gets filtered and transformed in the same sequence that the code reads. Let's go back to my needlessly cruel and exaggerated example of "SQL math", in which all expressions are written as a fill-in-the-clauses COMPUTE statement with each operation being its own optional clause, in a fixed order. Suppose that it was decided that it's helpful to be able to MULTIPLY after adding, which normally comes _before_ multiplying in the fixed order, but people were sick of writing: COMPUTE MULTIPLY 3 FROM (COMPUTE MULTIPLY 2 ADD 1 FROM 3) So they came up with a new keyword for multiplying that goes _after_ the addition step. They'd use a different word so you could tell the operators apart, but it'd basically be a synonym. COMPUTE MULTIPLY 2 ADD 1 PRODUCT WITH 3 That's how HAVING feels to me. It's a band-aid that wouldn't be necessary or even desired if WHERE was a standalone operator on table types, just as our hypothetical PRODUCT WITH clause wouldn't be desired if we had a standalone * operator. I get that SQL works and is extremely useful. Remember, I spent quite a bit of time implementing a library for working with it. But there are languages that make you think things like, "Oh, that's simpler than I thought. I can use this kind of construct to iterate over anything that implements the right interface. That was smart of the language designers". Then there are languages that make you think, "Huh. Another special case. Couldn't they have thought this through a bit more carefully before speccing out the language?". SQL feels like the latter and yet I see people call it a beautiful language or their favorite language and I get a strong "stop liking what I don't like" impulse.

rspeele 8y ago

If I use your variable example I get: A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations. I'm assuming there is a way to make it work, but then it seems like it'd be kind of a messy imperative-style approach. Would the value of @foo persist between each row's evaluation, so now my query would be capable of (intentionally or not) stateful execution? Would that interfere with the optimizer's ability to re-order evaluation or would it be undefined behavior? Other than the special cased string value conversion, SQL server treats bit as just a very short integer type. You cannot supply a bit value alone to WHERE or CASE, you must use a comparison operator. And likewise, you cannot assign the result of a comparison expression to a bit, you must wrap it in CASE as you have demonstrated. In fact my own project, in an attempt to squeeze various dialects of SQL into a common type system, fakes booleans in T-SQL by inserting CASE expressions and (<> 0) comparisons as needed See example: http://rzsql.net/#80273F1AF9F7DB7776102BFA0814D4A7F01F2397 http://rzsql.net/#80273F1AF9F7DB7776102BFA0814D4A7F01F2397 If a normal programming language had special places where boolean expressions could appear (like in if and while loops), and boolean expressions did not produce values in and of themselves, it would stand out as being extremely hacky. Of course that is a complaint only for T-SQL, other dialects do have true bools. But it is emblematic of design undertaken without effort to unify fundamentals (The question not asked by T-SQL's designers: "What if predicate expressions were just like any other expression, and had a value?") and that is what I find distasteful about SQL in general. HAVING is another example of that lazy, slap-more-syntax-on design. As you correctly point out, HAVING evaluates after the GROUP BY, WHERE before, and that's why HAVING can refer to aggregate results. But if "WHERE" was just an operator it could be used in the order it was needed without hassle. In LINQ, you write: users .Where(u => u.AccountIsActivated) .GroupBy(u => u.Name) .Select(g => new { Name = g.Key, NumberOfUsersWithName = g.Count() }) .Where(g => g.NumberOfUsersWithName > 1) .Select(g => $"The name {g.Name} is shared by {g.NumberOfUsersWithName} users") The .Where before the grouping is the same function as the .Where after it. No need for a special case. Of course you could do the same thing in SQL without HAVING using a subquery: select 'The name ' + sq.Name + ' is shared by ' + sq.NumberOfUsersWithName + ' users' from (select u.Name, count(*) as NumberOfUsersWithName from Users u where u.AccountIsActivated = 1 group by u.Name) sq where sq.NumberOfUsersWithName > 1 But it's ugly, so they threw in HAVING and that lets you do it all in one query. select 'The name ' + u.Name + ' is shared by ' + count(*) + ' users' from Users u where u.AccountIsActivated = 1 group by u.Name having count(*) > 1 Understandable choice, because subqueries start to get unreadable fast. I believe this is due to the middle-out (select outer-stuff from inner-queries where more-outer-stuff) syntax, which gets hard to follow when nested more than a level or two. I find the front-to-back pipeline approach of LINQ or F#'s Seq module far easier to track as data gets filtered and transformed in the same sequence that the code reads. Let's go back to my needlessly cruel and exaggerated example of "SQL math", in which all expressions are written as a fill-in-the-clauses COMPUTE statement with each operation being its own optional clause, in a fixed order. Suppose that it was decided that it's helpful to be able to MULTIPLY after adding, which normally comes _before_ multiplying in the fixed order, but people were sick of writing: COMPUTE MULTIPLY 3 FROM (COMPUTE MULTIPLY 2 ADD 1 FROM 3) So they came up with a new keyword for multiplying that goes _after_ the addition step. They'd use a different word so you could tell the operators apart, but it'd basically be a synonym. COMPUTE MULTIPLY 2 ADD 1 PRODUCT WITH 3 That's how HAVING feels to me. It's a band-aid that wouldn't be necessary or even desired if WHERE was a standalone operator on table types, just as our hypothetical PRODUCT WITH clause wouldn't be desired if we had a standalone * operator. I get that SQL works and is extremely useful. Remember, I spent quite a bit of time implementing a library for working with it. But there are languages that make you think things like, "Oh, that's simpler than I thought. I can use this kind of construct to iterate over anything that implements the right interface. That was smart of the language designers". Then there are languages that make you think, "Huh. Another special case. Couldn't they have thought this through a bit more carefully before speccing out the language?". SQL feels like the latter and yet I see people call it a beautiful language or their favorite language and I get a strong "stop liking what I don't like" impulse.