6 ms·
Serious flaws in SQL (1990)
- lukaseder 3y agoEveryone has a different set of "serious" flaws to share.
- forinti 3y agoSQL needs to make you add a where clause to update and delete. If you really want to delete or update everything, then you should have to state it like this: delete all rows from customers; update all rows from items set price=0;
- mrkeen 3y agoI wouldn't mind if SELECT was guaranteed read-only as well. (rename SELECT INTO).
- acimim_ha 3y ago"SELECT * INTO table" is not ANSI standard.
- 24t 3y agoI find myself caught out more often by incomplete where clauses — paranoid dry runs with rollbacks have saved me more than once. Also fwiw DataGrip asks for confirmation before running an update or delete without a where clause
- est 3y agoOr add a mandatory `where` for delete and update queries. To delete/update all, add `where 1=1` -------------------------------- p.s. In mysql I always add `limit 1` for every update. If `limit 99999` isn't enough you are probably doing it wrong.
- jandrese 3y agoOr just allow "limit unlimited" for those cases where you are working with lots of rows.
- chuckadams 3y agoJetbrains IDEs add a warning to updates or inserts without a "where" clause, and asks for confirmation when you try to run them.
- tomrod 3y agoMost systems I work in raise a warning when an update or insert is missing a where clause. For all rows, we typically just use `where 1=1` to encompass all rows. Allows for cleaner readability too as each clause can be separated by an AND
- petepete 3y agoMySQL has (or at least had) --i-am-a-dummy-mode which means you have to specify the rows or include a limit. https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html#safe-updates https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html#safe...
- jimbob45 3y agoHad no idea this existed before this morning. T-SQL devs, if you're out there, please add something like this to MSSQL.
- ZiiS 3y agoDon't think it needs a new syntax, just that the WHERE clause is mandatory: DELETE FROM customers WHERE TRUE;
- tonnydourado 3y agoMy first thought is that "where TRUE" is just a wonky thing to have to write, but on further reflection, maybe that's kinda the point. Where-less deletes should be harder to write, making it look stupid will probably help.
- masto 3y agoI spent a past life in a world where it was deemed necessary to perform this kind of interactive database surgery, and I used two techniques to try to mitigate the risk: I had my psql client set to disable autocommit (some people fear this because of leaving locks around, but you pick your poison), and my default user didn't have write access. Neither are perfect, but I ended up not being the person who accidentally deleted a whole table because, as they had explained to me with an annoying "I'm so macho" attitude, they always typed the semicolon first.
- dools 3y agoI had some similar strategies: 1) Write the WHERE clause first 2) Run a SELECT on that WHERE clause 3) Edit the query to make it either update or delete as required Similarly, I only ever perform recursive filesystem commands using find and xargs. I run the find first, then hit the up arrow and pipe it to whatever I want to do (I learned that one from a very patient sysadmin after I accidentally chmod'd -R / 664)
- arscan 3y agoBeen a few decades, but I was in a similar situation and came up with a similar approach. Run the select first and then swap in an update or delete. If the query was somewhat involved to determine what to delete, I’d write the select query to just select row IDs of rows for deletion (assuming a simple primary key), then wrap that with a “delete from table where ID in (select ID from …complex query…)”.
- xnorswap 3y agoI agree preventing DELETE and UPDATE without predicates would be a useful server configuration. And you don't even need special syntax, it just forces someone to write DELETE * FROM [customers] WHERE 1=1 to satisfy the need for a predicate.
- bazoom42 3y agoHow about having sql statements default to “dry run”, only executing if an additional keyword (like “execute”) is added.
- byteknight 3y agoThat becomes a non-boundary because you'd write it every time.
- pak9rabid 3y agoIsn't this something that explicit transactions (BEGIN/COMMIT/ROLLBACK) do? Many DB clients can be configured to have autocommit disabled, so that you're forced to either COMMIT or ROLLBACK each transaction.
- cess11 3y agoThe difference in impact between DELETE * FROM tbl and DELETE * FROM tbl WHERE id > 1 isn't enough to warrant a change to the standard in my opinion. Data loss and corruption risks need other forms of mitigation and they'll cover the unconstrained cases as well.
- Sakos 3y agoI don't think this is something that can be solved at the language level. What's missing is a semantic, decently intelligent interface that can ask "hey, this is gonna delete everything in the table, is that what you wanted?" and it feels like we're slowly moving in that direction with the recent developments in AI.
- randomdata 3y agoThe original relational calculus, created by the same person who wrote the linked paper, did not allow unbounded deletes. One has to wonder how SQL, which was based on his work, managed to screw things up so badly. These were already solved problems before SQL was created.
- ziml77 3y agoI have Red Gate's SQL Prompt extension installed in SSMS. If I try to execute a delete or update without a where clause, it asks me to confirm that's what I want to do. It also clearly indicates in the editor that there's a problem. If I'm sure that's what I want to do and I want to avoid being warned I just add WHERE 1 = 1 to the end. It's a nice feature that adds a little bit of comfort (though I'm still wary enough to hesitate before running SQL data modifications against production)
- sinuhe69 3y agoIt proves that hyperbole and over-dramatization are not a new phenomenon. Btw, what are physiological features? I guess the authors meant physical features, didn’t they?
- evertheylen 3y ago> As used here, the term "psychological" refers to what is often called the human-factors aspects of a language. The term "logical" refers to the logical power of a language, especially the power achievable without resorting to the usual programming tricks, such as iterative loops. It goes on to give an example of two queries which "one might reasonably expect" to be equivalent but give different results. (Personally, I disagree, but maybe I've been biased too much by SQL.)
- housecarpenter 3y agoThe paper says psychological, not physiological. It looks like what Codd means by "psychological features" are features that are (or are intended to) make it easier for users to express what they want to do in the language, as opposed to "logical features" which are what actually enable things to do. It's similar to the concept of "syntactic sugar", I guess. In a well-designed language, there should be a relatively small, theoretically well-understood core of logical features, and the psychological features should be straightforwardly expressible in terms of those logical features. This makes it easier to understand how the language works, which to me, does seem like an important thing for a language (it makes it easier to learn, easier to optimize, easier to add new features to without causing surprising interactions, etc.) Codd's complaint is that SQL isn't like this, specifically with regard to the psychological feature of being able to nest queries. This was intended to be a more user-friendly alternative to using predicate logic. But instead of designing the nested queries feature as a syntactic sugar layer on top of an underlying predicate logic core, both features were implemented at the "core" level. The result is a hodgepodge language which isn't clearly based on either predicate logic or nested queries alone.
- mehulashah 3y agoThere was a lot of criticism of SQL back in the day, but it eventually became “inter-galactic data speak” as Stonebraker dubbed it. It’s hard to fight gravity when new languages are adopted.
- randomdata 3y agoThere still is, but SQL is mostly used as a compiler target these days, with users using higher level languages (e.g. "ORMs") to hide the nastinesses. As such, its flaws don't impact all that many people. Which is a shame as it needn't be that way. A well designed relational calculus could avoid these abstractions that come with their own problems.
- Swizec 3y ago> with users using higher level languages (e.g. "ORMs") to hide the nastinesses Maybe I’ve been doing it wrong my whole career because every project I’ve worked with the engineers eventually said ”Screw this leaky abstraction. Having to know SQL _and_ how the ORM works on top is too much, we’ll just put everything in an ORM.rawQuery(…)”
- magicalhippo 3y agoWe prefer writing our own SELECTs so we have more predictable performance, also it makes debugging production issues easier. A poor ORM-generated query might be fast enough during testing but crashes and burns when it hits client's database. Not us, but a perfect example was the recent story discussed here[1] on optimizing an ORM-generated query to get 1200x speedup. [1]: https://news.ycombinator.com/item?id=40069583 https://news.ycombinator.com/item?id=40069583
- hn_throwaway_99 3y ago100%. I've come to believe that essentially every software engineer, if they write software long enough, comes to despise ORMs. As I always say "They make the easy stuff a little easier, and they make the harder stuff way harder". I have yet to see an ORM actually save me time in the long run; most of the time it just feels like I'm battling it. SQL definitely has its warts, but I don't feel like ORMs make it possible to ignore those warts at all. I'd love it if a better language like PRQL was accepted by DB engines and just compiled to SQL in the DB. Was a good blog post a couple years ago that I think highlights a lot of problems with ORMs and query builders: https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...
- rbanffy 3y agoWell... That didn't age well... In the end, we all learned to work with SQL, and to go around its idiosyncrasies. We even learned how to put objects in tables and to force trees into rectangles.
- acimim_ha 3y agoand we got "IS [NOT] DISTINCT FROM" in the meantime, for better or worse
- forgetfulness 3y agoThe author of the paper invented SQL
- sixbrx 3y agoinvented the relational model rather
- randomdata 3y agoHe invented Alpha as part of his relational theorem, although it was never implemented. QUEL, of the Ingres/Postgres lineage, was heavily influenced by the design of Alpha and is the closest thing we have to what he envisioned. SQL comes from Boyce and Chamberlain.
- randomdata 3y ago> In the end, we all learned to work with SQL, and to go around its idiosyncrasies. In the same way we've learned to work with C and all its memory idiosyncrasies. But let's keep that between you and me as the poor Rust crowd is going to cry when you tell them that their language is unnecessary. The problems Codd speaks of here are exactly the source of database-related bugs I continually see out in the wild. I'd expect to see it even more frequently, but a lot of developers have started using higher level languages like 'ORMs' that add some layer of protection from those issues. It aged quite well, frankly.
- jroseattle 3y agoWay back in the day, I picked up a cheap ($5!!!) copy of Readings in Database Systems from Michael Stonebraker. I found it fascinating to read the original papers that proposed concepts, and then to see those concepts implemented and become the norm. What I didn't expect was the amount of drama within the context of those papers. In Codd's original paper on relational theory in the 60s, he spends a chunk of time dedicated to talking about IBM as "the man". Hilarious.
- commandlinefan 3y ago> concepts implemented and become the norm I had a coworker some time in the late 90's who was reading the book "Design Patterns". We were working in Java at the time and he remarked how odd it seemed to see a book that was targeted at C++ programmers use terms like "interfaces" which were "Java concepts".
- jokoon 3y agoAt least, refusing to use SQL gave me opportunities to understand how data structures work: B-tree, RB trees, hash tables, R-trees, Z order curve, quad tree, KD tree, BSP, grids, N-ary tree, geohash, etc etc
- chuckadams 3y agoWhich ironically enough, probably makes you more qualified than most to write efficient SQL or even a SQL implementation.
- owlstuffing 3y agoIt’s interesting that the relational model reflects interface inheritance as opposed to implementation inheritance. In my view it’s another indication that (true) delegation is generally a more suitable way to design software.
- da_chicken 3y agoI mean, most of these have been addressed. 1990 was a very long time ago. > SQL permits duplicate rows in relations Indeed. One of the first things you learn is that, no matter what you're doing, all tables should have a primary key. It's simply too annoying otherwise. It's just as easy to make your own surrogate key in the case where there aren't enough natural identifiers, and anyone with any experience will do so. It's almost entirely a beginner trap. That said, I've seen so many applications get a simple many-to-many relation wrong for representing tags or categories on an object that I do understand where Codd's complaint is coming from. > it supports an inadequately defined kind of nesting of a query within a query; Thanks to the efforts of ANSI and ISO, this is much improved. It should be noted that 1990 is before the introduction of the LEFT JOIN syntax in SQL-92. At this time, the only JOIN syntax was the ANSI-89 "comma" join, and if you wanted an outer join you had to rely on vendor extensions. This only took about 20 years to resolve. The alternate IN syntax at the top of p382, for example, is now valid syntax in PostgreSQL and other RDBMSs. Further, the EXISTS operator allows this type of query to occur. > it does not adequately support three-valued logic, let alone four-valued logic. Additional operators have improved three-valued logic quite a bit. I'm not sure I've seen many systems that would require or benefit from four-valued logic. I'm not entirely sure what Codd is looking for here. I think he wants MAYBE for outer joins? I don't know. Indeed, most DB designs seem to go out of their way to ensure that unless you're dealing with outer joins that you're likely to only have two-valued logic. Three-valued logic is already too complicated for a lot of people.
- jklowden 3y ago> It's just as easy to make your own surrogate key in the case where there aren't enough natural identifiers A row with no natural is misdesigned. Proof: what does the row _mean_ if its values don’t distinguish it uniquely in the real world?
- civilized 3y agoAllowing duplicate rows is the only practical decision for the following reasons: 1. Most of the ways you can mess up in SQL involve failing to respect unique keys in joins, and this can be done just as easily whether or not duplicate rows are allowed. 2. Enforcing no duplicates would be very expensive.