4 ms·

SQL is incredibly low level - Modelling Sum Types requires giving up correctness guarantees, performance, or both. - It's not expressive enough to get perform

by T-R 11y ago

SQL is incredibly low level - Modelling Sum Types requires giving up correctness guarantees, performance, or both. - It's not expressive enough to get performance or abstraction - I can't communicate "this is a one-to-one relationship", "this is append-only", "this is associative", or "these things commute" in anything but comments. And my co-workers need to remember to make those joins, and think about how they can use that information to hand-optimize, because the optimizer sure as hell isn't going to do it for them. - There's no abstraction/composition of anything at lower than a full query granularity, so everything needs to be inlined and repeated everywhere - this makes conceptually small changes, like going from single- to multi-column primary keys or vice-versa, require rewriting practically every view/query. - Some optimizer WATs are required by the spec ('where' clauses can be propagated down the AST even past another clause that does type refinement; CTEs always materialize the result set and act as optimization walls), others may be implementation dependent (At least in Postgres, "Union All", instead of acting like a simple stream concatenation, more often than not materializes the whole result set and pages it out to disk - your 0.2ms query now takes 2 seconds). SQL is "Lazy when the stars align" and optimized by "this won't break things in the trivial case" - most effort in non-trivial queries is spent trying to convince the optimizer not to hurt you on a whim. Don't get me wrong, the devs do absolutely impressive stuff with what they've got - these quirks are clearly what needs to be done to compensate for the spec, and the spec can't change for compatibility resons - but it doesn't make it easy to work with. - Things like missing a column in a join clause (like when changing that primary key) cause significant bugs that don't actually throw an exception/type error anywhere - they just change the size of your result set. This could be completely fixed with type-level literals. - Types are internally inconsistent, and supplied functions are pretty much ad-hoc "we thought this might be useful". Type conversions regularly involve first converting to a string or wrapping in a nested query. - The syntax is pants-on-head ridiculous for anything non-trivial, making education difficult and maintenance tedious. (especially window functions - SELECT a, b, LAST_VALUE(b) OVER (ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS x). The problem, though, isn't just the amount of code and maintenance issues this creates - it's the sheer amount of information that everyone on your team who touches the database needs to know and not overlook just to not create subtle bugs and significant performance issues. And all this knowledge is completely non-transferrable. SQL is so bad that standard practice is just to not do anything on the database unless you have to; eating the connection overhead, adding yet another cache, and having a layer of wrapper functions are just considered the cost of doing business, leading a lot of people to think SQL is perfectly sufficient for what it does.

greggyb 11y ago
I do truly appreciate the critique you've offered, as it has some well-thought out objections to the language. I don't want to get into a back and forth on how to deal with the specific issues - they're good food for thought, but I think it wouldn't lead to a productive conversation. I would object that SQL is actually incredibly high level, though its abstractions differ from the typical ones seen in other programming languages. As a declarative query language, I can describe a join between two arbitrary (no need to do anything besides have the tables exist) tables and the query optimizer will do a very good job of choosing among several different algorithms to perform that join for me. Without a thought to any statistics of the data, the query is optimized appropriately, including the construction of a temporary index if that is the best way to compute the join. Or, transactions in general - ACID guarantees are maintained with a simple transaction - almost no boilerplate, just saying where it begins and where it ends with some options for specific behavior, and the transaction is guaranteed on arbitrary data structures without any other code necessary. I won't go into the data consistency guarantees you can make just by defining a couple of foreign keys and constraints (performance aside - these can be optimized - this would be awful to put together by hand in a project) These are some of the abstractions in SQL which are absurdly high level compared to doing something similar in another language. Several of your bullets also support that it is a high level language, e.g. 4 "others may be implementation dependent" - this is not a statement ever made about assembly for example. SQL is, at the end of the day, a query language built around relational algebra, already an abstract topic, and implemented on arbitrary hardware, operating systems, filesystems, and so on. It's not got a lot of high level concepts that exist in other languages, sure, but it's got a lot of stuff built in that would be a bear and a half to implement on your own. Its high level concepts are in general very orthogonal to those in other languages. Again, I'm not disagreeing with your bullets, and I appreciate the time you took to reply and your points are exactly the sort of thing I was asking for. I just think that you're ignoring some of the power in it for want of the power of constructs you're more familiar with.
- T-R 11y ago
  It's definitely relevant that my complaints are from a Haskell perspective (as this is a Haskell thread). If I'm working in just about any procedural/OOP language, sum types are a completely foreign concept. If I'm working in PHP, I don't expect a consistent API. If I'm working in a strict language, I don't expect to be able to abstract my data model away from how I loop over it in memory. If I'm used to anything without some support for dependent types, I just assume that an empty list, or a list with too many entries couldn't possibly be detected at compile time. The high level constructs it does have are strictly less powerful, largely as a function of not being composable/consistent and certainly not as polymorphic as the kinds of things in Foldable/Traversable, though certainly better than writing your own for-loops. The thing is, these are practically all you do in SQL, so it's pretty frustrating that, when working with it, so much time is dedicated to dealing with these solved problems. They're the reason you can't import a "votes" or "comments" package to your database, and why being a database expert is such a niche, specialized, and lucrative position - because so much knowledge isn't captured in the language, or the compiler, or libraries (even though it quite feasibly could be), but just spread by books and blog articles. It's why building complex things on relational databases is so hard - the level you're working at is strictly limited to "what keys do I join on to get the result set I want" and "how do I make this only loop once" - it's not inherent in the relational model/relational algebra, it's strictly a problem with SQL. To add, as a declarative query language, it's worth also comparing to things like Prolog/Datalog. Prolog certainly doesn't get you all of the things I mentioned, either (e.g., no dependent types), but it is a much higher level of abstraction - you can describe things like "this is a one-to-one relationship" at a high level. The flipside is that, to write performant queries, you need to understand the Warren Abstract Machine, but because of its consistency, that's a significantly lower barrier to entry than trying to understand a specific database's optimizer implementation, or the arbitrary decisions in the SQL spec, and the knowledge there is actually transferrable.

by T-R 11y ago

greggyb 11y ago

I do truly appreciate the critique you've offered, as it has some well-thought out objections to the language. I don't want to get into a back and forth on how to deal with the specific issues - they're good food for thought, but I think it wouldn't lead to a productive conversation. I would object that SQL is actually incredibly high level, though its abstractions differ from the typical ones seen in other programming languages. As a declarative query language, I can describe a join between two arbitrary (no need to do anything besides have the tables exist) tables and the query optimizer will do a very good job of choosing among several different algorithms to perform that join for me. Without a thought to any statistics of the data, the query is optimized appropriately, including the construction of a temporary index if that is the best way to compute the join. Or, transactions in general - ACID guarantees are maintained with a simple transaction - almost no boilerplate, just saying where it begins and where it ends with some options for specific behavior, and the transaction is guaranteed on arbitrary data structures without any other code necessary. I won't go into the data consistency guarantees you can make just by defining a couple of foreign keys and constraints (performance aside - these can be optimized - this would be awful to put together by hand in a project) These are some of the abstractions in SQL which are absurdly high level compared to doing something similar in another language. Several of your bullets also support that it is a high level language, e.g. 4 "others may be implementation dependent" - this is not a statement ever made about assembly for example. SQL is, at the end of the day, a query language built around relational algebra, already an abstract topic, and implemented on arbitrary hardware, operating systems, filesystems, and so on. It's not got a lot of high level concepts that exist in other languages, sure, but it's got a lot of stuff built in that would be a bear and a half to implement on your own. Its high level concepts are in general very orthogonal to those in other languages. Again, I'm not disagreeing with your bullets, and I appreciate the time you took to reply and your points are exactly the sort of thing I was asking for. I just think that you're ignoring some of the power in it for want of the power of constructs you're more familiar with.

T-R 11y ago
It's definitely relevant that my complaints are from a Haskell perspective (as this is a Haskell thread). If I'm working in just about any procedural/OOP language, sum types are a completely foreign concept. If I'm working in PHP, I don't expect a consistent API. If I'm working in a strict language, I don't expect to be able to abstract my data model away from how I loop over it in memory. If I'm used to anything without some support for dependent types, I just assume that an empty list, or a list with too many entries couldn't possibly be detected at compile time. The high level constructs it does have are strictly less powerful, largely as a function of not being composable/consistent and certainly not as polymorphic as the kinds of things in Foldable/Traversable, though certainly better than writing your own for-loops. The thing is, these are practically all you do in SQL, so it's pretty frustrating that, when working with it, so much time is dedicated to dealing with these solved problems. They're the reason you can't import a "votes" or "comments" package to your database, and why being a database expert is such a niche, specialized, and lucrative position - because so much knowledge isn't captured in the language, or the compiler, or libraries (even though it quite feasibly could be), but just spread by books and blog articles. It's why building complex things on relational databases is so hard - the level you're working at is strictly limited to "what keys do I join on to get the result set I want" and "how do I make this only loop once" - it's not inherent in the relational model/relational algebra, it's strictly a problem with SQL. To add, as a declarative query language, it's worth also comparing to things like Prolog/Datalog. Prolog certainly doesn't get you all of the things I mentioned, either (e.g., no dependent types), but it is a much higher level of abstraction - you can describe things like "this is a one-to-one relationship" at a high level. The flipside is that, to write performant queries, you need to understand the Warren Abstract Machine, but because of its consistency, that's a significantly lower barrier to entry than trying to understand a specific database's optimizer implementation, or the arbitrary decisions in the SQL spec, and the knowledge there is actually transferrable.