18 ms·

A Critique of SQL, 40 Years Later

187 points by mariuz 4y ago

cmrdporcupine 4y ago
"Despite the improvements listed above, in a 2014 interview, CJ Date said “we didn’t realize how truly awful SQL was or would turn out to be (note that it’s much worse now than it was then, though it was pretty bad right from the outset).” This quote leaves me wondering – if Date himself were to write an updated critique, what would it look like? My best guess is most of his criticism would revolve around further departures of SQL from the relational model, but specific examples escape me." I am not sure how the author has missed the copious amount of material that Date (& Darwin) wrote on this topic? The obvious one being the Third Manifesto (whole book available here [1] since it's out of print now). But even "Database in Depth" [2] has extensive discussion about this despite being a less "opinionated" and "ranty" book (highly recommended BTW) Yes, not explicitly updated since 2014, but there's really nothing new to say since then? Date is definitely concerned about the departures from the relational model in SQL. But it's (EDIT) not just about purism. He's also pretty in general peaved off at SQL for similar concerns that the author presents here about syntax and expressiveness. Third Manifesto's "tutorial D" mandates the addition of a richer type system, including higher level types that match on relation constraints, and "operators" (similar to OO methods) on those types; basically a kind of predicate dispatch. I imagine he's pretty sick of being ignored. [1] https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf [2] https://www.oreilly.com/library/view/database-in-depth/0596100124/ https://www.oreilly.com/library/view/database-in-depth/05961...
- fipar 4y ago
  I agree with everything you're saying, though I'm assuming here: > Date is definitely concerned about the departures from the relational model in SQL. But it's just about purism. Perhaps you meant to say "But it's not just about purism"?
  - cmrdporcupine 4y ago
    Oops, yes, thanks for the catch. Editing.
- ErwinSmout 4y ago
  In fact, Date has recently revised a whole slew of "old" papers of his, got them up-to-date wrt his current thinking, and (re-) published them. His "A critique of SQL/86" paper(s) are now chapter 8, 9 & 10 of "Database Dreaming, Vol I" (there's a Vol II too).
dominotw 4y ago
SQL is having somewhat of a moment in the bigdata world, thanks in part to 'modern datastack' and new age datawarehouses like snowflake,bigquery. However there are a lot of pushback from 'traditional' dataengineers who were trained on spark/scala. Its bit of hardsell to go from a highly typed language to a free for all text based logic. I think the following is needed for sql to be finally accepted as 'serious' contender. create compiled sql language ( not pandas) 1. that compiles to sql and addresses some of the issues bought up in the post like nested aggregations. 2. make code reusable. Eg: apply year over year growth to a table that has the requisite columns. Compiler should check this in ide. 3. make materializations first class concept in the language. No seperate dbt layer. 4. crate a way to package and distribute libraries that you can import into your project . 5. a unit testing framework that makes it easy to test the logic without having to setup test tables in the database.
- vendiddy 4y ago
  I think these are great suggestions. It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL. 2 & 3 are my biggest pain points. I can't just extract functions like I can with a regular programming language. Instead, SQL queries get increasingly complex with no great tools to manage that. For 3, products like https://materialize.com/ https://materialize.com/ look interesting for being able to create derived materialized views that can efficiently be kept up to date.
  - fatherzine 4y ago
    "someone could design a functional-style programming language that compiles to SQL". See e.g. R dplyr
  - sbuttgereit 4y ago
    I'm currently doing work that uses Elixir's Ecto which goes a great deal towards what I think you're aiming at. I can write my SQL in a familiar, yet functional and composable style; while knowing what SQL will be produced in the end. Ecto, as I understand it, was inspired by Microsoft's/c# LINQ. I've not worked with that, but heard similar praises for that as exists with Ecto. I'm saying this as with most of my experience being in SQL.
Cockbrand 4y ago
While all the criticism is probably correct, and SQL definitely shows its age, it's still very much good enough for pretty much all its current applications. Also, SQL's basics are very easy to learn. These aspects make it very hard to imagine a language that might gain enough traction to actually replace SQL in a foreseeable future.
- cmrdporcupine 4y ago
  Many have tried. Most are hobby projects. None have had the full force of a real production ready DBMS behind them. And really, projects like this are fighting the general ignorance the industry as a whole has about what a relational database actually is. It's better since the NoSQL wave crested and we stopped hearing stupid shit like "my data isn't tabular and doesn't fit in a schema", but there's still a preponderance of people who haven't stopped to learn the lessons that Date and Codd tried to teach decades ago, and constantly reach for graph/network/hierarchical databases without understanding why we originally tried to move beyond those back in the 70s. Unfortunately because of this most attempts to do "better than SQL" end up not working from first principles and look like a dogs breakfast of ill-formed "that'd be neat" ideas. And, yeah, as I said the "pure relational" projects that are out there are usually either academic projects or hobby. (Yes I've had a few of my own) I think there might be hope in the Datalog-type systems that have emerged recently.
  - cryptonector 4y ago
    Any alternative to SQL has to transpile to SQL in order to gain traction.
    - cmrdporcupine 4y ago
      Which right away rules out a whole bunch of more sophisticated and elegant behaviours, honestly. The other alternative might be to implement one's new thing as a patch to alter the frontend of Postgres. I looked at this many years ago and the engineering effort was immense. But it might be easier now.
devin-petersohn 4y ago
I always appreciate blog posts like this, there are obviously cases where SQL shines, and in part I think the dataframe abstraction helps with filling a lot of the missing pieces that SQL doesn't handle so well (composability, debuggability, interactivity, etc.) Even pandas (with all its faults) is more flexible as a language than SQL[1]. I'm of the opinion that there's a better together story in the end, but I guess we will see. [1] https://ponder.io/pandas-vs-sql-food-court-michelin-style-restaurant/ https://ponder.io/pandas-vs-sql-food-court-michelin-style-re...
bearjaws 4y ago
The only thing I would really blame solely on SQL is that UPDATE and DELETE statements don't require you to specify a limit. I have seen many times in my career where a rogue delete just truncates a table, a simple statement of intent (e.g. LIMIT 1) would tell the query planner that if it is about update/delete more than 1 row, it should error. In fact MySQL actually returns a warning if you do this. TRUNCATE clearly states your intention to delete everything, but DELETE by default also deletes everything. There were also definitely some bad paradigms invented as a result of SQL (e.g. all business logic lives in the database as stored procedures or stored functions), but that was bound to happen whenever a shiny new tool comes out and engineers want to use it.
- dspillett 4y ago
  > TRUNCATE clearly states your intention to delete everything, but DELETE by default also deletes everything. I think the other way around. “DELETE Something” to me means get rid of the whole of Something. “TRUNCATE Something” (in general English, not SQL) to me means to make something shorter, which probably doesn't mean removing it completely. Perhaps “EMPTY Table” would have been a better choice than “TRUNCATE Table”, but by the time it reached the standard¹ TRUNCATE had long since been picked and until then it was not the responsibility of the SQL standard: you only had DELETE and without a filter it makes sence that this deletes all. ---- [1] note that TRUNCATE was not in the SQL standards until SQL2008, long after it was common in various DMBSs as a minimally logged alternative to DELETE-without-filtering-clauses.
- Shorel 4y ago
  Require? Some UPDATEs don't allow me to specify a limit! Infuriating stuff.
- merb 4y ago
  > TRUNCATE clearly states your intention to delete everything, but DELETE by default also deletes everything. btw. they are completly different. TRUNCATE basically ignores all transaction semantices for the sake of performance (which can be really really bad) in mysql it can't be rolled back, in postgres it's not mvcc safe. TRUNCATE most often uses either an exclusive lock or some other mechanism. if you know what you are doing truncate can be helpful, but delete has its benefits as well and truncate should be avoided by beginners.
exabrial 4y ago
The only thing that affects my daily life with SQL is that FROM should be before the SELECT keyword. This would _greatly_ improve type-ahead support in SQL IDEs. Nothing is perfect, but that is really the main beef. Another commentor already nailed having a LIMIT WITH ERROR clause to be specified on UPDATE,DELETE statements and explicitly throw an error otherwise. SQL is on of my favorite tools to use and I don't see it getting replaced by anything any time soon.
- toto444 4y ago
  Every time SQL is mentioned on HN someone comes to complain about FROM coming after SELECT. I use SQL every day and not a single time have I found reason to complain about it. Can you give a bit more detail about what's wrong with it being like it is ? EDIT : thanks all for your reply. I now understand that it is an IDE related thing not something fundamental to the language.
  - deleted 4y ago
    [deleted]
  - Hinrik 4y ago
    Like the commenter alluded to, it allows accurately constrainted type-ahead. If a query starts with "SELECT FROM my_table " and expects one or more column names at that point, your IDE can already suggest the column names from my_table (and not from any other table).
  - cogman10 4y ago
    Imagine a table `Foo` with the schema `FooId, name, date, favorite_color, active` Now, you want to pull the ID for the latest `foo` for a specific date, but you don't know any of the column names. The modern workflow looks like this You write `SELECT * FROM Foo` then you say, "Ok, now I can get autocomplete" `SELECT FooId FROM Foo` "Ok, now I can write the where clause" `Select FooId From Foo WHERE date=?` It becomes an exercise in moving the cursor around just to get the autocomplete going. If you are really familiar with the schema, not a problem. But if you just remember a few details about it, then you are stuck in this weird back and forth cursor moving thing. That's why it'd be more ergonomic to have something like `From Foo Select FooId where date=?` Because you never need to move your cursor and you could get all the autocomplete you need at the right times. This becomes especially true when writing joining statements FROM Foo f JOIN Bar b ON f.FooId = b.FooId SELECT f.FooId WHERE b.active = 1
thomas536 4y ago
I once read an article about SQL and how reordering the sections of a query would make it more ergonomic for users (iirc things like specifying what you want first and then how to present it last, more like a pipeline). I searched many times over the years but have not been able to find it again.
- colinmcd 4y ago
  Perhaps this one? https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with-select/ https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with...
  - thomas536 4y ago
    Perhaps? I remember the article arguing more comprehensively why a different order would be beneficial, but maybe I am just misremembering
gibsonf1 4y ago
I think the issue isn't SQL, but the table paradigm for storing data. Humans do not store data in separate tables that need joining, they store data in a fully connected graph (hyper-graph). Its about relationships and hierarchies - the graph allows incredibly fast hierarchical reasoning, as most things involve hierarchical reasoning. The relational table, and Sql by correlation, are terrible at the human approach to working with data especially hierarchies.
- warrenm 4y ago
  I work extensively with Splunk which is dominantly based on noSQL underneath (MongoDB, among other, proprietary technologies) I've also recently been [re]introduced to graph databases (which are highly similar to the pre-relational network database paradigm) You can simulate graph relationships with an RDBMS or noSQL - but you shouldn't You can simulate an RDBMS with a graph db or noSQL - but you shouldn't You can simulate noSQL with graph and RDBMS tools - but, again, you shouldn't They all have their place - and ca even work quite well together, if you use them as they're intended
  - gibsonf1 4y ago
    But thats just it, having many silos for connected information is just not the way we humans do it, nor necessary. A single conceptually indexed space/time hypergraph (as we humans do it) for any part of the world is all you need, scalable up to many billions of edges for that one part of the world.
    - tremon 4y ago
      having many silos for connected information is just not the way we humans do it What do you mean? I can think of many silos containing connected information: municipal residence records, marriage registries, birth records, police reports, tax records, medical records (for each hospital) are all silos connected by a citizen's identity.
      - gibsonf1 4y ago
warrenm 4y ago
the title doesn't match the first statement, which states a math unfact: >A Critique of SQL, 40 Years Later 08.11.2022 >The SQL language made its first appearance in 1974, as part of IBM’s System R database. It is now over 50 years later, and SQL is the de facto language for 1974 isn't 50 years ago yet :)
- adrian_b 4y ago
  The title did not refer to years passed since the introduction of SQL, but to years passed since the publication of the paper with the title "A Critique of the SQL Database Language", i.e. from 1984-11. So the title is correct.
  - jakespoon 4y ago
    Title may be correct. First sentence is totally wrong. Why would I read further?
    - cryptonector 4y ago
      It's off by two years. People often round 48 to 50. I don't mind TFA rounding 48 to 50; you seem to, so don't read it if you don't want to.
    - carlineng 4y ago
      Author here. Fixed the mistake. You may now proceed with reading the remainder of the post ;)
- cryptonector 4y ago
  If it'd been 1971 and TFA still said "50 years later", would you have responded that "1971 is more than 50 years ago"?
RyanHamilton 4y ago
qSQL based on the concepts of ordered lists is more appropriate for many queries, examples available here: https://www.timestored.com/b/kdb-qsql-query-vs-sql/ https://www.timestored.com/b/kdb-qsql-query-vs-sql/ Kdb the system that qSQL is ran within, allows full use of variables and all builtin functions with tables/functions/variable/columns. It really is a case of less is more. What this allows is functional form queries. Imagine being able to query: ?[tableName;lessThan(age;10)] and have perfect functional form representation for all queries. No ORM, no string concatenation. It seems some other database creators are at least becoming area of these things and integrating parts.
- cryptonector 4y ago
  I really never want to assume order in tables. That's because there could be many orders of interest, and that's one reason to have multiple indexes. And also because for a table, the order of rows can be hard to guarantee. Obviously something like qSQL would try hard to guarantee table row order, and that's great in the cases where qSQL is useful, but more generally it's going to impose on how you work with the database. On the other hand, SQL is set-based. But being set-based is weird in the world of computers because in memory everything is ordered. Sets are a fiction in programming, as they're always ordered in some way, and the set abstraction can only try to hide that order. And that order can be very useful. But there can be many orders that can be useful, but only one in which things are stored in memory -- the others can only be extra indexes. So the general purpose thing (SQL) has to be set-based, offering explicit ordering, and taking advantage of actual order for optimization.
crazygringo 4y ago
I've written a bajillion queries and have tons of nitpicks, but it's the twin meanings of NULL that really kills me. NULL can be the value of a field in a record, but it is also used to indicate the lack of a record in a JOIN. If I run: SELECT x.a, y.b FROM x LEFT JOIN y on x.a = y.a and I get back [5, NULL] I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table y that starts with 5. Obviously there are lots of workarounds, but to me this is a truly fundamental design flaw. SQL should have specified another NULL-like value, call it EMPTY, used only for joins that find no data. And analagous to IS NULL, it would be checked using IS EMPTY.
- onlyrealcuzzo 4y ago
  How do you expect `y.b` to behave on EMPTY rows? Should it return EMPTY or NULL?
- goto11 4y ago
  A nullable column can always be extracted to a separate table with a non-nullable column and a foreign key. If you left-join this back with the base table, you will get the nulls again for the missing values. So to me it seem nicely symmetrical to use the same kind of NULL value.
- dspillett 4y ago
  > but it's the twin meanings of NULL that really kills me NULL only has one meaning: NULL. This is roughly analogous to unknown. The one that his a lot of people is WHERE <value> NOT IN (<set>) where <set> contains a NULL. Because NOT IN unrolls to “<value> <> <s1> AND <value> <> <s2> AND … AND <value> <> <sx>” any NULL values in the set makes one predicate NULL which makes the whole expression NULL even if one or more of the other values match. > I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table y that starts with 5. Not directly, but you can infer the difference going by the value of (in your example) y.a - if it is NULL then there was no match, otherwise a NULL for y.b is a NULL from the source not an indication of no match. > SQL should have specified another NULL-like value This sort of thing causes problems of its own. Are the unknowns equivalent? Where are they relevant? How do they affect each other? Do you need more to cover other edge cases? I have memories of VB6's four constants of the apocalypse (null, empty, missing, nothing). This is one of the reasons some purists argue against NULL existing in SQL at all, rather than needing a family of NULL-a-likes.
tabtab 4y ago
Many of those complaints seem theoretical. I like to focus on practical concerns. The biggest problem I see is that the SQL language has grown too complex. It's related to the "Lack of Orthogonality" problem mentioned in the article, but I see different solutions. SQL is not based on combinations of simpler concepts, but hard-coded keywords. But how to orthogonize (factor) it gets into philosophical differences. My favorite alternative is an experimental language called SMEQL (Structured Meta-Enabled Query Language): https://wiki.c2.com/?TqlRoadmap https://wiki.c2.com/?TqlRoadmap It's more API-like to reduce the need for specialized keywords. And you can "calculate" column lists via queries instead of have to list columns. For example, if a table has 60 columns and you want to SELECT all 60 minus two columns, you can't without listing all 58. With SMEQL you can use a query to return a list (table) of those 58 and feed it to the equivalent of a SELECT clause. Things like CREATE TABLE are fed a table if you desire so you can "compute" your schema. You can generate entire schemas from data dictionaries. Anything that can be done with a table is. You can create in-line (virtual) tables if you want it command-driven, but it's still "using tables to do everything". You can use textual code to create such virtual tables or a table editor as it fits the situation. SMEQL is as close to "everything is a table" as I've seen. Even your FROM list can be table-ized. I used to do similar with dBASE/xBASE, and it was really nice, especially for ad-hoc work such as one-off research requests. And as somebody mentioned here, null handling needs a serious revisit in SQL. I could rant all day about SQL null handling, especially for strings.
- asah 4y ago
  I agree!!! this is my pet peeve as well, and I sometimes fantasize about ripping into PostgreSQL and adding column-minus. One wrinkle: computed columns would interfere with query optimization. That said (and here I speak heresy) there are times when syntactic convenience trumps performance.
  - cryptonector 4y ago
    The syntax could be "* (minus, columns, here)".
  - tabtab 4y ago
jokoon 4y ago
I wish there were SQL "primitives" functions instead of the SQL language. For example if I want to pick a single row by id, with SQL I must send a query string, which results in parsing, which means lower latency. If I want to randomly select 100k rows among a database of 1 million entries, I need to build 10k query strings (I think?), which won't be fast to parse. I don't think this happens when using C pointers in C, C++ or arrays in other languages.
- RedShift1 4y ago
  Isn't that what prepared statements solve? Only parsed on the first execution and after that only the parameters change.
  - tremon 4y ago
    Exactly. Prepared statements or stored procedures avoid the continuous re-parsing of identical queries.
- zasdffaa 4y ago
  If by 'randomly select' you do mean randomly, the TABLESAMPLE is what you want (although random means random pages not rows and the amount returned may be over or under) SELECT * FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ; for example. You can of course specify by rows instead.
- rrrrrrrrrrrryan 4y ago
  To get 100k random rows in T-SQL it's: SELECT TOP (100000) * FROM tblNm ORDER BY newid()
- xpe 4y ago
  Why 10k queries? You can probably do this with one query.
cbm-vic-20 4y ago
> "we didn’t realize how truly awful SQL was or would turn out to be (note that it’s much worse now than it was then, though it was pretty bad right from the outset)." I wish the "truly awful" stuff I come up with was 0.1% as successful as SQL.
- ErwinSmout 4y ago
  And Tony Hoare probably wished he hadn't made his "billion dollar mistake". But at any rate, you could also try and ponder how unbelievably exceptionally awful it must have been that existed before SQL if something as truly awful as SQL could still be as successful as it has been.
snowstormsun 4y ago
https://youtu.be/lS7zNbB-XfI?list=PLn0nrSd4xjjaSLBSzmno-3Ods6FJE9nlO&t=2257 https://youtu.be/lS7zNbB-XfI?list=PLn0nrSd4xjjaSLBSzmno-3Ods...
naranha 4y ago
My problem with SQL is that it's 99% Excel and 1% A database query language. And the boundaries between are often not well defined. Why even have a SUM function, when it is not accelerated by an index, that makes using it automatically non-scalable. I think there should be a core language, perhaps similar to SQL perhaps not, as an interface to pure DB functionality, all the other 99% could be done in Excel and while it's nice to have, it should be more clearly separated.
- nhumrich 4y ago
  Perhaps excel is 99% SQL and not the other way around, especially since SQL is older.
- acdha 4y ago
  > Why even have a SUM function, when it is not accelerated by an index, that makes using it automatically non-scalable. Because it’s useful and that last part isn’t true? Databases do more than indexes and you have trade offs regarding the impact of adding too many indexes. It's also not only not a problem for scalability but in reality an important way to _improve_ scalability. Consider a simple example where we do "SELECT SUM(price) FROM orders GROUP BY customer_id". Removing SUM() from the language would massively increase the amount of data which needs to be processed into the response — which makes anything else you're doing like sorting harder – and it prevents various optimizations the database engine might make. For example, on AWS RDS Aurora the database pushes all of that down to the storage nodes so each storage node will return the sums for the records which are on that node and the main node can sum those intermediate values without needing to transfer a single source row over the network. Since SUM() is part of the language and well-defined, that's safe for one database team to implement without the risk of their results not matching a competing database. Other things to think about are computed views and stored procedures: in both cases, there are situations where these are dramatic performance improvements or otherwise desirable and having a richer language means that those constructs can solve more problems.
  - naranha 4y ago
    But even SUM in AWS RDS Aurora is not optimal in terms of performance. You could do much better by having partial SUMs in the index btree nodes, that way you could get the sum of billions of records without processing any row individually. CouchDB supports this (even though it's by far not the most efficient database). I wish Postgres and others provided access to that sort of low level map-reduce index functionality. Transferring large amounts of data in the response is solvable by using shared memory, so I'm not concerned about that. And a SQL like language could be provided on top of that. If a low level interface was standardized you could even have SQL like languages that are portable between DBs.
adius 4y ago
PRQL (Pipelined Relational Query Language) to the rescue! https://prql-lang.org https://prql-lang.org
erichocean 4y ago
All SQL database implement the actual Relational Calculus internally—it's required to implemented a SQL optimizer. SQL is just a language for submitting Relational Calculus to the database (+ DDL statements). If you wanted to, for instance, you could add a language alongside SQL in Postgres, submit the results to the internal RC optimizer, execute the optimized query, and get back the results. In your new language, you can address all of the issues Carlin Eng/Chris Date identify in the article.
tibbydudeza 4y ago
I wish there was some smart machine learning technology that will take my shitty SQL developer queries and make it more performant - sometimes it is like sitting in front of a bubbling cauldron and invoking magic incantations.
iddan 4y ago
I think we can do much better than SQL without losing its inherent power. Projects like Prisma and EdgeDB make me optimistic regarding the future of relational querying languages
bawolff 4y ago
Its interesting,the author dismisses >Mismatch with Host Language But to me, i feel like this is the biggest problem with sql. Yes every language is different, but having different mental models nonetheless causes friction, and leads to things like ORMs which are often even worse.
ErwinSmout 4y ago
In "Stating the obvious", C.J. Date wrote 50 (FIFTY) pages solely on the subject of how SQL screwed up with the most fundamental operator of all : testing for equality. I'll repeat : FIFTY pages about everything that SQL got wrong with just the equality operator. Go figure about the rest ...

187 points by mariuz 4y ago

"Despite the improvements listed above, in a 2014 interview, CJ Date said “we didn’t realize how truly awful SQL was or would turn out to be (note that it’s much worse now than it was then, though it was pretty bad right from the outset).” This quote leaves me wondering – if Date himself were to write an updated critique, what would it look like? My best guess is most of his criticism would revolve around further departures of SQL from the relational model, but specific examples escape me." I am not sure how the author has missed the copious amount of material that Date (& Darwin) wrote on this topic? The obvious one being the Third Manifesto (whole book available here [1] since it's out of print now). But even "Database in Depth" [2] has extensive discussion about this despite being a less "opinionated" and "ranty" book (highly recommended BTW) Yes, not explicitly updated since 2014, but there's really nothing new to say since then? Date is definitely concerned about the departures from the relational model in SQL. But it's (EDIT) not just about purism. He's also pretty in general peaved off at SQL for similar concerns that the author presents here about syntax and expressiveness. Third Manifesto's "tutorial D" mandates the addition of a richer type system, including higher level types that match on relation constraints, and "operators" (similar to OO methods) on those types; basically a kind of predicate dispatch. I imagine he's pretty sick of being ignored. [1] https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf [2] https://www.oreilly.com/library/view/database-in-depth/0596100124/ https://www.oreilly.com/library/view/database-in-depth/05961...

fipar 4y ago
I agree with everything you're saying, though I'm assuming here: > Date is definitely concerned about the departures from the relational model in SQL. But it's just about purism. Perhaps you meant to say "But it's not just about purism"?
- cmrdporcupine 4y ago
  Oops, yes, thanks for the catch. Editing.
ErwinSmout 4y ago
In fact, Date has recently revised a whole slew of "old" papers of his, got them up-to-date wrt his current thinking, and (re-) published them. His "A critique of SQL/86" paper(s) are now chapter 8, 9 & 10 of "Database Dreaming, Vol I" (there's a Vol II too).

dominotw 4y ago

SQL is having somewhat of a moment in the bigdata world, thanks in part to 'modern datastack' and new age datawarehouses like snowflake,bigquery. However there are a lot of pushback from 'traditional' dataengineers who were trained on spark/scala. Its bit of hardsell to go from a highly typed language to a free for all text based logic. I think the following is needed for sql to be finally accepted as 'serious' contender. create compiled sql language ( not pandas) 1. that compiles to sql and addresses some of the issues bought up in the post like nested aggregations. 2. make code reusable. Eg: apply year over year growth to a table that has the requisite columns. Compiler should check this in ide. 3. make materializations first class concept in the language. No seperate dbt layer. 4. crate a way to package and distribute libraries that you can import into your project . 5. a unit testing framework that makes it easy to test the logic without having to setup test tables in the database.

vendiddy 4y ago
I think these are great suggestions. It seems like you're suggesting that someone could design a functional-style programming language that compiles to SQL. 2 & 3 are my biggest pain points. I can't just extract functions like I can with a regular programming language. Instead, SQL queries get increasingly complex with no great tools to manage that. For 3, products like https://materialize.com/ https://materialize.com/ look interesting for being able to create derived materialized views that can efficiently be kept up to date.
- fatherzine 4y ago
  "someone could design a functional-style programming language that compiles to SQL". See e.g. R dplyr
- sbuttgereit 4y ago
  I'm currently doing work that uses Elixir's Ecto which goes a great deal towards what I think you're aiming at. I can write my SQL in a familiar, yet functional and composable style; while knowing what SQL will be produced in the end. Ecto, as I understand it, was inspired by Microsoft's/c# LINQ. I've not worked with that, but heard similar praises for that as exists with Ecto. I'm saying this as with most of my experience being in SQL.

Cockbrand 4y ago

While all the criticism is probably correct, and SQL definitely shows its age, it's still very much good enough for pretty much all its current applications. Also, SQL's basics are very easy to learn. These aspects make it very hard to imagine a language that might gain enough traction to actually replace SQL in a foreseeable future.

cmrdporcupine 4y ago
Many have tried. Most are hobby projects. None have had the full force of a real production ready DBMS behind them. And really, projects like this are fighting the general ignorance the industry as a whole has about what a relational database actually is. It's better since the NoSQL wave crested and we stopped hearing stupid shit like "my data isn't tabular and doesn't fit in a schema", but there's still a preponderance of people who haven't stopped to learn the lessons that Date and Codd tried to teach decades ago, and constantly reach for graph/network/hierarchical databases without understanding why we originally tried to move beyond those back in the 70s. Unfortunately because of this most attempts to do "better than SQL" end up not working from first principles and look like a dogs breakfast of ill-formed "that'd be neat" ideas. And, yeah, as I said the "pure relational" projects that are out there are usually either academic projects or hobby. (Yes I've had a few of my own) I think there might be hope in the Datalog-type systems that have emerged recently.
- cryptonector 4y ago
  Any alternative to SQL has to transpile to SQL in order to gain traction.
  - cmrdporcupine 4y ago
    Which right away rules out a whole bunch of more sophisticated and elegant behaviours, honestly. The other alternative might be to implement one's new thing as a patch to alter the frontend of Postgres. I looked at this many years ago and the engineering effort was immense. But it might be easier now.

devin-petersohn 4y ago

I always appreciate blog posts like this, there are obviously cases where SQL shines, and in part I think the dataframe abstraction helps with filling a lot of the missing pieces that SQL doesn't handle so well (composability, debuggability, interactivity, etc.) Even pandas (with all its faults) is more flexible as a language than SQL[1]. I'm of the opinion that there's a better together story in the end, but I guess we will see. [1] https://ponder.io/pandas-vs-sql-food-court-michelin-style-restaurant/ https://ponder.io/pandas-vs-sql-food-court-michelin-style-re...

bearjaws 4y ago

The only thing I would really blame solely on SQL is that UPDATE and DELETE statements don't require you to specify a limit. I have seen many times in my career where a rogue delete just truncates a table, a simple statement of intent (e.g. LIMIT 1) would tell the query planner that if it is about update/delete more than 1 row, it should error. In fact MySQL actually returns a warning if you do this. TRUNCATE clearly states your intention to delete everything, but DELETE by default also deletes everything. There were also definitely some bad paradigms invented as a result of SQL (e.g. all business logic lives in the database as stored procedures or stored functions), but that was bound to happen whenever a shiny new tool comes out and engineers want to use it.

dspillett 4y ago
> TRUNCATE clearly states your intention to delete everything, but DELETE by default also deletes everything. I think the other way around. “DELETE Something” to me means get rid of the whole of Something. “TRUNCATE Something” (in general English, not SQL) to me means to make something shorter, which probably doesn't mean removing it completely. Perhaps “EMPTY Table” would have been a better choice than “TRUNCATE Table”, but by the time it reached the standard¹ TRUNCATE had long since been picked and until then it was not the responsibility of the SQL standard: you only had DELETE and without a filter it makes sence that this deletes all. ---- [1] note that TRUNCATE was not in the SQL standards until SQL2008, long after it was common in various DMBSs as a minimally logged alternative to DELETE-without-filtering-clauses.
Shorel 4y ago
Require? Some UPDATEs don't allow me to specify a limit! Infuriating stuff.
merb 4y ago
> TRUNCATE clearly states your intention to delete everything, but DELETE by default also deletes everything. btw. they are completly different. TRUNCATE basically ignores all transaction semantices for the sake of performance (which can be really really bad) in mysql it can't be rolled back, in postgres it's not mvcc safe. TRUNCATE most often uses either an exclusive lock or some other mechanism. if you know what you are doing truncate can be helpful, but delete has its benefits as well and truncate should be avoided by beginners.

exabrial 4y ago

The only thing that affects my daily life with SQL is that FROM should be before the SELECT keyword. This would _greatly_ improve type-ahead support in SQL IDEs. Nothing is perfect, but that is really the main beef. Another commentor already nailed having a LIMIT WITH ERROR clause to be specified on UPDATE,DELETE statements and explicitly throw an error otherwise. SQL is on of my favorite tools to use and I don't see it getting replaced by anything any time soon.

toto444 4y ago
Every time SQL is mentioned on HN someone comes to complain about FROM coming after SELECT. I use SQL every day and not a single time have I found reason to complain about it. Can you give a bit more detail about what's wrong with it being like it is ? EDIT : thanks all for your reply. I now understand that it is an IDE related thing not something fundamental to the language.
- deleted 4y ago
  [deleted]
- Hinrik 4y ago
  Like the commenter alluded to, it allows accurately constrainted type-ahead. If a query starts with "SELECT FROM my_table " and expects one or more column names at that point, your IDE can already suggest the column names from my_table (and not from any other table).
- cogman10 4y ago
  Imagine a table `Foo` with the schema `FooId, name, date, favorite_color, active` Now, you want to pull the ID for the latest `foo` for a specific date, but you don't know any of the column names. The modern workflow looks like this You write `SELECT * FROM Foo` then you say, "Ok, now I can get autocomplete" `SELECT FooId FROM Foo` "Ok, now I can write the where clause" `Select FooId From Foo WHERE date=?` It becomes an exercise in moving the cursor around just to get the autocomplete going. If you are really familiar with the schema, not a problem. But if you just remember a few details about it, then you are stuck in this weird back and forth cursor moving thing. That's why it'd be more ergonomic to have something like `From Foo Select FooId where date=?` Because you never need to move your cursor and you could get all the autocomplete you need at the right times. This becomes especially true when writing joining statements FROM Foo f JOIN Bar b ON f.FooId = b.FooId SELECT f.FooId WHERE b.active = 1

thomas536 4y ago

I once read an article about SQL and how reordering the sections of a query would make it more ergonomic for users (iirc things like specifying what you want first and then how to present it last, more like a pipeline). I searched many times over the years but have not been able to find it again.

colinmcd 4y ago
Perhaps this one? https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with-select/ https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with...
- thomas536 4y ago
  Perhaps? I remember the article arguing more comprehensively why a different order would be beneficial, but maybe I am just misremembering

gibsonf1 4y ago

I think the issue isn't SQL, but the table paradigm for storing data. Humans do not store data in separate tables that need joining, they store data in a fully connected graph (hyper-graph). Its about relationships and hierarchies - the graph allows incredibly fast hierarchical reasoning, as most things involve hierarchical reasoning. The relational table, and Sql by correlation, are terrible at the human approach to working with data especially hierarchies.

warrenm 4y ago
I work extensively with Splunk which is dominantly based on noSQL underneath (MongoDB, among other, proprietary technologies) I've also recently been [re]introduced to graph databases (which are highly similar to the pre-relational network database paradigm) You can simulate graph relationships with an RDBMS or noSQL - but you shouldn't You can simulate an RDBMS with a graph db or noSQL - but you shouldn't You can simulate noSQL with graph and RDBMS tools - but, again, you shouldn't They all have their place - and ca even work quite well together, if you use them as they're intended
- gibsonf1 4y ago
  But thats just it, having many silos for connected information is just not the way we humans do it, nor necessary. A single conceptually indexed space/time hypergraph (as we humans do it) for any part of the world is all you need, scalable up to many billions of edges for that one part of the world.
  - tremon 4y ago
    having many silos for connected information is just not the way we humans do it What do you mean? I can think of many silos containing connected information: municipal residence records, marriage registries, birth records, police reports, tax records, medical records (for each hospital) are all silos connected by a citizen's identity.
    - gibsonf1 4y ago

warrenm 4y ago

the title doesn't match the first statement, which states a math unfact: >A Critique of SQL, 40 Years Later 08.11.2022 >The SQL language made its first appearance in 1974, as part of IBM’s System R database. It is now over 50 years later, and SQL is the de facto language for 1974 isn't 50 years ago yet :)

adrian_b 4y ago
The title did not refer to years passed since the introduction of SQL, but to years passed since the publication of the paper with the title "A Critique of the SQL Database Language", i.e. from 1984-11. So the title is correct.
- jakespoon 4y ago
  Title may be correct. First sentence is totally wrong. Why would I read further?
  - cryptonector 4y ago
    It's off by two years. People often round 48 to 50. I don't mind TFA rounding 48 to 50; you seem to, so don't read it if you don't want to.
  - carlineng 4y ago
    Author here. Fixed the mistake. You may now proceed with reading the remainder of the post ;)
cryptonector 4y ago
If it'd been 1971 and TFA still said "50 years later", would you have responded that "1971 is more than 50 years ago"?

RyanHamilton 4y ago

qSQL based on the concepts of ordered lists is more appropriate for many queries, examples available here: https://www.timestored.com/b/kdb-qsql-query-vs-sql/ https://www.timestored.com/b/kdb-qsql-query-vs-sql/ Kdb the system that qSQL is ran within, allows full use of variables and all builtin functions with tables/functions/variable/columns. It really is a case of less is more. What this allows is functional form queries. Imagine being able to query: ?[tableName;lessThan(age;10)] and have perfect functional form representation for all queries. No ORM, no string concatenation. It seems some other database creators are at least becoming area of these things and integrating parts.

cryptonector 4y ago
I really never want to assume order in tables. That's because there could be many orders of interest, and that's one reason to have multiple indexes. And also because for a table, the order of rows can be hard to guarantee. Obviously something like qSQL would try hard to guarantee table row order, and that's great in the cases where qSQL is useful, but more generally it's going to impose on how you work with the database. On the other hand, SQL is set-based. But being set-based is weird in the world of computers because in memory everything is ordered. Sets are a fiction in programming, as they're always ordered in some way, and the set abstraction can only try to hide that order. And that order can be very useful. But there can be many orders that can be useful, but only one in which things are stored in memory -- the others can only be extra indexes. So the general purpose thing (SQL) has to be set-based, offering explicit ordering, and taking advantage of actual order for optimization.

crazygringo 4y ago

I've written a bajillion queries and have tons of nitpicks, but it's the twin meanings of NULL that really kills me. NULL can be the value of a field in a record, but it is also used to indicate the lack of a record in a JOIN. If I run: SELECT x.a, y.b FROM x LEFT JOIN y on x.a = y.a and I get back [5, NULL] I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table y that starts with 5. Obviously there are lots of workarounds, but to me this is a truly fundamental design flaw. SQL should have specified another NULL-like value, call it EMPTY, used only for joins that find no data. And analagous to IS NULL, it would be checked using IS EMPTY.

onlyrealcuzzo 4y ago
How do you expect `y.b` to behave on EMPTY rows? Should it return EMPTY or NULL?
goto11 4y ago
A nullable column can always be extracted to a separate table with a non-nullable column and a foreign key. If you left-join this back with the base table, you will get the nulls again for the missing values. So to me it seem nicely symmetrical to use the same kind of NULL value.
dspillett 4y ago
> but it's the twin meanings of NULL that really kills me NULL only has one meaning: NULL. This is roughly analogous to unknown. The one that his a lot of people is WHERE <value> NOT IN (<set>) where <set> contains a NULL. Because NOT IN unrolls to “<value> <> <s1> AND <value> <> <s2> AND … AND <value> <> <sx>” any NULL values in the set makes one predicate NULL which makes the whole expression NULL even if one or more of the other values match. > I have no way of knowing if that means there's a record [5, NULL] in table y, or if there's no record in table y that starts with 5. Not directly, but you can infer the difference going by the value of (in your example) y.a - if it is NULL then there was no match, otherwise a NULL for y.b is a NULL from the source not an indication of no match. > SQL should have specified another NULL-like value This sort of thing causes problems of its own. Are the unknowns equivalent? Where are they relevant? How do they affect each other? Do you need more to cover other edge cases? I have memories of VB6's four constants of the apocalypse (null, empty, missing, nothing). This is one of the reasons some purists argue against NULL existing in SQL at all, rather than needing a family of NULL-a-likes.

tabtab 4y ago

Many of those complaints seem theoretical. I like to focus on practical concerns. The biggest problem I see is that the SQL language has grown too complex. It's related to the "Lack of Orthogonality" problem mentioned in the article, but I see different solutions. SQL is not based on combinations of simpler concepts, but hard-coded keywords. But how to orthogonize (factor) it gets into philosophical differences. My favorite alternative is an experimental language called SMEQL (Structured Meta-Enabled Query Language): https://wiki.c2.com/?TqlRoadmap https://wiki.c2.com/?TqlRoadmap It's more API-like to reduce the need for specialized keywords. And you can "calculate" column lists via queries instead of have to list columns. For example, if a table has 60 columns and you want to SELECT all 60 minus two columns, you can't without listing all 58. With SMEQL you can use a query to return a list (table) of those 58 and feed it to the equivalent of a SELECT clause. Things like CREATE TABLE are fed a table if you desire so you can "compute" your schema. You can generate entire schemas from data dictionaries. Anything that can be done with a table is. You can create in-line (virtual) tables if you want it command-driven, but it's still "using tables to do everything". You can use textual code to create such virtual tables or a table editor as it fits the situation. SMEQL is as close to "everything is a table" as I've seen. Even your FROM list can be table-ized. I used to do similar with dBASE/xBASE, and it was really nice, especially for ad-hoc work such as one-off research requests. And as somebody mentioned here, null handling needs a serious revisit in SQL. I could rant all day about SQL null handling, especially for strings.

asah 4y ago
I agree!!! this is my pet peeve as well, and I sometimes fantasize about ripping into PostgreSQL and adding column-minus. One wrinkle: computed columns would interfere with query optimization. That said (and here I speak heresy) there are times when syntactic convenience trumps performance.
- cryptonector 4y ago
  The syntax could be "* (minus, columns, here)".
- tabtab 4y ago

jokoon 4y ago

I wish there were SQL "primitives" functions instead of the SQL language. For example if I want to pick a single row by id, with SQL I must send a query string, which results in parsing, which means lower latency. If I want to randomly select 100k rows among a database of 1 million entries, I need to build 10k query strings (I think?), which won't be fast to parse. I don't think this happens when using C pointers in C, C++ or arrays in other languages.

RedShift1 4y ago
Isn't that what prepared statements solve? Only parsed on the first execution and after that only the parameters change.
- tremon 4y ago
  Exactly. Prepared statements or stored procedures avoid the continuous re-parsing of identical queries.
zasdffaa 4y ago
If by 'randomly select' you do mean randomly, the TABLESAMPLE is what you want (although random means random pages not rows and the amount returned may be over or under) SELECT * FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ; for example. You can of course specify by rows instead.
rrrrrrrrrrrryan 4y ago
To get 100k random rows in T-SQL it's: SELECT TOP (100000) * FROM tblNm ORDER BY newid()
xpe 4y ago
Why 10k queries? You can probably do this with one query.

cbm-vic-20 4y ago

> "we didn’t realize how truly awful SQL was or would turn out to be (note that it’s much worse now than it was then, though it was pretty bad right from the outset)." I wish the "truly awful" stuff I come up with was 0.1% as successful as SQL.

ErwinSmout 4y ago
And Tony Hoare probably wished he hadn't made his "billion dollar mistake". But at any rate, you could also try and ponder how unbelievably exceptionally awful it must have been that existed before SQL if something as truly awful as SQL could still be as successful as it has been.

snowstormsun 4y ago

https://youtu.be/lS7zNbB-XfI?list=PLn0nrSd4xjjaSLBSzmno-3Ods6FJE9nlO&t=2257 https://youtu.be/lS7zNbB-XfI?list=PLn0nrSd4xjjaSLBSzmno-3Ods...

naranha 4y ago

My problem with SQL is that it's 99% Excel and 1% A database query language. And the boundaries between are often not well defined. Why even have a SUM function, when it is not accelerated by an index, that makes using it automatically non-scalable. I think there should be a core language, perhaps similar to SQL perhaps not, as an interface to pure DB functionality, all the other 99% could be done in Excel and while it's nice to have, it should be more clearly separated.

nhumrich 4y ago
Perhaps excel is 99% SQL and not the other way around, especially since SQL is older.
acdha 4y ago
> Why even have a SUM function, when it is not accelerated by an index, that makes using it automatically non-scalable. Because it’s useful and that last part isn’t true? Databases do more than indexes and you have trade offs regarding the impact of adding too many indexes. It's also not only not a problem for scalability but in reality an important way to _improve_ scalability. Consider a simple example where we do "SELECT SUM(price) FROM orders GROUP BY customer_id". Removing SUM() from the language would massively increase the amount of data which needs to be processed into the response — which makes anything else you're doing like sorting harder – and it prevents various optimizations the database engine might make. For example, on AWS RDS Aurora the database pushes all of that down to the storage nodes so each storage node will return the sums for the records which are on that node and the main node can sum those intermediate values without needing to transfer a single source row over the network. Since SUM() is part of the language and well-defined, that's safe for one database team to implement without the risk of their results not matching a competing database. Other things to think about are computed views and stored procedures: in both cases, there are situations where these are dramatic performance improvements or otherwise desirable and having a richer language means that those constructs can solve more problems.
- naranha 4y ago
  But even SUM in AWS RDS Aurora is not optimal in terms of performance. You could do much better by having partial SUMs in the index btree nodes, that way you could get the sum of billions of records without processing any row individually. CouchDB supports this (even though it's by far not the most efficient database). I wish Postgres and others provided access to that sort of low level map-reduce index functionality. Transferring large amounts of data in the response is solvable by using shared memory, so I'm not concerned about that. And a SQL like language could be provided on top of that. If a low level interface was standardized you could even have SQL like languages that are portable between DBs.

adius 4y ago

PRQL (Pipelined Relational Query Language) to the rescue! https://prql-lang.org https://prql-lang.org

erichocean 4y ago

All SQL database implement the actual Relational Calculus internally—it's required to implemented a SQL optimizer. SQL is just a language for submitting Relational Calculus to the database (+ DDL statements). If you wanted to, for instance, you could add a language alongside SQL in Postgres, submit the results to the internal RC optimizer, execute the optimized query, and get back the results. In your new language, you can address all of the issues Carlin Eng/Chris Date identify in the article.

tibbydudeza 4y ago

I wish there was some smart machine learning technology that will take my shitty SQL developer queries and make it more performant - sometimes it is like sitting in front of a bubbling cauldron and invoking magic incantations.

iddan 4y ago

I think we can do much better than SQL without losing its inherent power. Projects like Prisma and EdgeDB make me optimistic regarding the future of relational querying languages

bawolff 4y ago

Its interesting,the author dismisses >Mismatch with Host Language But to me, i feel like this is the biggest problem with sql. Yes every language is different, but having different mental models nonetheless causes friction, and leads to things like ORMs which are often even worse.

ErwinSmout 4y ago

In "Stating the obvious", C.J. Date wrote 50 (FIFTY) pages solely on the subject of how SQL screwed up with the most fundamental operator of all : testing for equality. I'll repeat : FIFTY pages about everything that SQL got wrong with just the equality operator. Go figure about the rest ...