4 ms·

I am the author of a small parser/typechecker for a subset[0] of SQL, roughly matching that supported by SQLite, and I agree with you COMPLETELY. What follows i

I am the author of a small parser/typechecker for a subset[0] of SQL, roughly matching that supported by SQLite, and I agree with you COMPLETELY. What follows is my rant directed at those who don't. It is an ugly, verbose language. You can be very familiar with thinking in sets, and still not like SQL. It's what we've got, and the SQL databases available are very, very good products. But I do wish a cleaner language could have won. As you know, anyone who's used LINQ in C#, particularly by directly calling the extension methods .Select(...).Where(...).OrderBy(...), sees how much better it is from a composability standpoint. SQL is the anti-Lisp. Lisp's design is about 4 pieces of syntax and a few fundamental operations from which all else is built. Conversely, nearly every operation in SQL is a tacked-on special case to the ridiculously complex SELECT syntax. Filtering results? That's a clause of SELECT. Ordering? Clause of SELECT. Filtering after aggregating? Oh, that's a different clause of SELECT. Once this philosophy has infected the brain of a SQL implementer, it spreads like wildfire. That's why you even see custom syntax pop up even in good'ol function calls sometimes, like in Postgres: overlay('abcdef' placing 'wt' from 3 for 2). SQL fans often talk about the beauty of relational algebra. Once you achieve relational enlightenment, SQL is supposed to be beautiful. But if we wrote math like SQL, you wouldn't say 2 * 3 + 4. There would be a grand COMPUTE statement with clauses for each operation you could wish to perform. So you'd write COMPUTE MULTIPLY 2 ADD 4 FROM 3. Of course, the COMPUTE statement is a pipeline, and multiplication comes after addition in the pipeline, so if you wanted to represent 2 * (3 + 4) you'll push that into a sub-compute, like COMPUTE MULTIPLY 2 FROM (COMPUTE MULTIPLY 1 ADD 4 FROM 3). SQL clauses could have been "functions" with well-defined input and output types, if the language designers had come up with a type system to match the relational algebra. WHERE - table<row type> -> predicate<row type> -> table<row type> JOIN - table<row x> -> table<row y> -> predicate<row x, row y> -> table<row x, row y> ORDER - table<row type> -> list<expression<row type>> -> table<row type> These could be pipelined, rather than nested, either with an OO-style method call syntax or a functional style pipe operator. You understand the idea. Again, as you've mentioned, the LINQ methods[1] are a great resource for people not familiar with this style. But, counterargument. Languages with minimal syntax and great power are often claimed to be unreadable. Sometimes it's nice to have special syntax, to help give a recognizable shape to what you're reading, instead of it being operator / function call soup. So what did SQL accomplish by making everything a special case of SELECT? Well, it's got this sensible flow to every statement. You see, the execution of SELECT logically flows as I've numbered the lines below. SELECT 8 DISTINCT 7 TOP n 5 column expressions 1 FROM tables 2 WHERE predicate 3 GROUP BY expressions 4 HAVING expressions 6 ORDER BY ... I sure am glad they cleared that up. If it had been a chain of individual operations I'd have been utterly baffled. Ignoring completely the syntactical design, the lack of basic operations is a pain too. Why can't I declare variables within queries? I often would like to do something like: select let x = compute_something(...) in x + q as column1 x + r as column2 from ... Instead, when I really need that, I end up wrapping the whole thing in an outer query and computing X in the inner query. Hooray for SELECT, the answer to all problems! Oh and yes, views and stored procedures and UDFs are no answer to the need for one-off local composability within queries. Then you have the sloppy design of the type system in virtually all SQL dialects. SQL Server doesn't even have a boolean type. There is no type you can declare for `@x` that will let you `set @x = (1 <> 2)`. And don't even get me started on the GROUP BY clause, the design of which contorts the whole rest of the language. If you GROUP BY some columns, you must not refer to any non-grouped columns in the later parts of your query (refer to my table above for which parts are "later"). Unless, that is, you are referring to them in aggregates. Then the HAVING clause was tacked on so that you'd have a way to do a filter -- the same thing as WHERE -- after the GROUP BY. Does it all make sense once you understand it? Yes, in that you can see how you'd end up with this system if you were adding things piece by piece and never went back to redesign from square one. Wow, I have a lot of ranting to do about SQL. I feel like I haven't even scratched the surface. And hell, I still pick SQL databases every time I start a project! The damn language is useful enough and the products work great. But it has all the design elegance of the US tax code. [0] https://github.com/rspeele/Rezoom.SQL https://github.com/rspeele/Rezoom.SQL [1] https://docs.microsoft.com/en-us/dotnet/api/system.linq.enumerable?view=netframework-4.7.2 https://docs.microsoft.com/en-us/dotnet/api/system.linq.enum...

Tallain 8y ago
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.

Tallain 8y ago

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.