Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A Critique of SQL, 40 Years Later

carlineng.com

Post reply on HN
Print view View original

181–190 of 260 posts

Re: A Critique of SQL, 40 Years Later

#181

Post by cmrdporcupine »

mamcx wrote: post #61
toto444 wrote: post #14

Earlier quoted context omitted.

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.

> I now understand that it is an IDE related thing not something fundamental to the language. No, is fundamental issue to the language! The relational model is clear. You START with a relation and then compose with relational operators that return relations. ie: rel | project Sql do it weird . Is like in OO, where instead of define a class THEN define the properties, you define the properties THEN define the class. A…

Yeah. And SQL does it very weird because it mixes up the meaning of "selection". Selection doesn't mean what most people seem to think it means. In the relational algebra "select" is really what is happening in the "where" clause. Projection is the name for the thing that chooses the attributes ("columns") to put in the final result.

Re: A Critique of SQL, 40 Years Later

#182

Post by cmrdporcupine »

jolmg wrote: post #75
mamcx wrote: post #61

Earlier quoted context omitted.

> I now understand that it is an IDE related thing not something fundamental to the language. No, is fundamental issue to the language! The relational model is clear. You START with a relation and then compose with relational operators that return relations. ie: rel | project Sql do it weird . Is like in OO, where instead of define a class THEN define the properties, you define the properties THEN define the class. A…

Having SELECT come first makes sense to me because it's the only part of the statement that's required. FROM and everything else is optional. Also when reading a statement, you're mostly interested in what the returned fields are rather details like where they came from or how they're ordered. It kind of makes sense to put it at the start. Maybe other syntax forms have their benefits, specially when writing, but I do…

Select doesn't mean that tho. Not in the relational model. It refers to the filtering clause.

Following their stupid English syntax, but using the proper verbs it should rightfully be "PROJECT x, y FROM foo SELECT WHERE a = b"

Re: A Critique of SQL, 40 Years Later

#183

Post by cmrdporcupine »

crazygringo wrote: post #37

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…

Fabian Pascal made a whole career out of writing about how stupid "NULL" is in SQL. See if you can find his writings some time. He was positively livid about it.

Notably NULL of any kind is completely absent from the relational algebra. There are other ways to express absence. Notably, this is all supposed to be first-order predicate logic stuff, and... in that world "null" is also not a "thing".

Also SQL insists on allowing duplicate "rows" in its "tables"; whereas the relational model is very clear that the contents of a relation ("table") is a set, not a bag.

These two confusions in SQL actually lead to many many data integrity problems as follow-on effects and also complicate query planning and optimization.

Re: A Critique of SQL, 40 Years Later

#184

Post by tabtab »

asah wrote: post #120
tabtab wrote: post #43

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 f…

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.

Meta ability (dynamism) will generally have an average performance penalty. If you want speed, you hard-code more up front. Meta-ability generally is best for ad-hoc and short-term uses.

One performance trick is for the query optimizer assume the calculation will be the same as the last time the same query or sub-query was issued, and simply dump the results and start over if that assumption is false. If it keeps having to dump, then it assumes you are editing a lot and stops guessing for a day or so. (You could issue an optimizer command to resume guessing if need be.)

Re: A Critique of SQL, 40 Years Later

#185

Post by SnowHill9902 »

exabrial wrote: post #10

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…

If you don’t wrap your UPDATEs in a transaction you are doing it wrong.

Re: A Critique of SQL, 40 Years Later

#186

Post by SnowHill9902 »

bearjaws wrote: post #8

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…

So don’t COMMIT your UPDATEs.

Re: A Critique of SQL, 40 Years Later

#187

Post by warrenm »

johannes1234321 wrote: post #144
warrenm wrote: post #30

Earlier quoted context omitted.

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 si…

> You can simulate noSQL with graph and RDBMS tools - but, again, you shouldn't What is the feature which makes the noSQL which you shouldn't do in a relational database? To me noSQL always looks like a subset of relational database. The only thing, maybe, is that you can truly put everything in, but with modern JSON features and all the other things I don't see a downside in using relational. (Except a little learni…

The "schema on the fly" is the draw for noSQL - nothing has to be rigidly predefined like an RDBMS mandates

Re: A Critique of SQL, 40 Years Later

#188

Post by SoftTalker »

3pm wrote: post #174
SoftTalker wrote: post #56

Earlier quoted context omitted.

Yep. That's why I write most of my logic in stored procedures. Working with tables and queries is so much easier in PL/pgsql than dealing with ORMs and their leaky abstractions. My application code just calls stored procedures. It's unaware of the tables and underlying data model.

There is no one-size-fits-all, but most of the time I would be against SP because: 1) SPs usually mix persistence concerns with business logic. Making it harder to understand business intent. I find objects much more expressive than raw data. Sometimes you want to concentrate on the plain logic, without worrying about how something gets saved. Also you will not have to rewrite everything if you ever want to change ho…

In my experience, the application framework changes much more often than the database.

PHP, ASP, ASP.NET, Angular, React, Django, whatever. Pick a year, pick a framework. The database stays steady.

Re: A Critique of SQL, 40 Years Later

#189

Post by SoftTalker »

sivers wrote: post #81
SoftTalker wrote: post #56

Earlier quoted context omitted.

Yep. That's why I write most of my logic in stored procedures. Working with tables and queries is so much easier in PL/pgsql than dealing with ORMs and their leaky abstractions. My application code just calls stored procedures. It's unaware of the tables and underlying data model.

Cool! I do this, but I haven't seen anyone else do it. Is any of your code public? I wrote about it at https://sive.rs/pg and posted my SQL shopping cart at https://github.com/sivers/store Please contact me if you'd like to share tips: https://sive.rs/contact

No I don't really have any public code.

I read your blog post and I agree 100%

The database is the easiest place to code business logic that pertains to the data. Write it once, it's available for all client applications.

Re: A Critique of SQL, 40 Years Later

#190

Post by zasdffaa »

cmrdporcupine wrote: post #143
cryptonector wrote: post #119

Earlier quoted context omitted.

Any alternative to SQL has to transpile to SQL in order to gain traction.

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.

Why does it rule out 'more sophisticated and elegant behaviours'?
Post reply on HN