News for hackers. Stuff that matters.
Re:SQL is syntactic sugar for relational algebra by roenxi on Saturday March 23, 2024 @02:22AM (#39797871)
I'm glad that the article concluded "No" to it's own headline. Calling SQL "syntactic sugar" is an insult to sugar. The "helpful diagram explaining how the scoping rules work" alone should make people blanch. The language is a syntactic disaster that we've been saddled with out of habit and inertia.
Re:SQL is syntactic sugar for relational algebra by bugbuddy on Saturday March 23, 2024 @02:31AM (#39797904)
Call a masochist but I love me good old SQL. I find most problems with systems I have worked with are the result of managements putting people who never paid attention in database classes in charge. Or maybe nobody really cares about doing a good job. In any case, my professors would have given all the legacy SQL databases that I have come across F-.

The worse part is nobody dares changing anything because it would require application code changes. Don’t blame SQL. Blame incompetent people doing SQL badly.

Re:SQL is syntactic sugar for relational algebra by b33j0r on Saturday March 23, 2024 @03:12AM (#39798089)
I didn’t “get” SQL for the first 6-8 years of professional full-stack engineering. I was chasing, along with many in my cohort, the ORM abstraction dragon we thought we needed to work on j2ee, django, or sqlalchemy teams quickly.

It’s perplexing to me now why SQL was perplexing. Even knowing about relational theory and enough DBA to pass interviews, it somehow always seemed like we should have been able to treat databases like our OOP’s.

To think of the hours I wasted. Maybe I heard in a meeting “it’s the join table” and another colleague said “just use the association table,” and I was trying the n^2 debugging approach to solve it with model classes.

The best advice I got on this made sense later: “I wouldn’t even try to manage that [dataset] myself. Just figure out how to tell a database to do it—that’s its only job.”

Re:SQL is syntactic sugar for relational algebra by bitwize on Saturday March 23, 2024 @03:25AM (#39798141)
Understanding relational algebra helps you write better SQL the way understanding lambda calculus helps you write better JavaScript. The language is warty and but a shadow of the pristine purity underneath, but it gets the job done and the theory guides your reasoning on how to use the language.
Re:SQL is syntactic sugar for relational algebra by lkuty on Saturday March 23, 2024 @05:55AM (#39798703)
In particular, I find the closure property of SQL very helpful and always think in terms of queries gettings tables and producing tables (derived most of the time). https://en.wikipedia.org/wiki/Closure_(mathematics)
Re:SQL is syntactic sugar for relational algebra by AdrianB1 on Saturday March 23, 2024 @09:18AM (#39799724)
I really "got" SQL about 15 years after I started using it. The explanation is very simple: until that moment, SQL was a minor component for me in a big pie. Then one day I got into the position to troubleshoot some apps with >1 TB database behind them and I figured that SQL is more than simple CRUD operations. For most full stack developers that I know, SQL is still that minor component in the back of their mind and they are usually right.
Re:SQL is syntactic sugar for relational algebra by eek2121 on Saturday March 23, 2024 @05:09AM (#39798520)
Agreed, though disagree about the database class thing. I got a C in my class. I was a DBA at the time and definitely knew my shit. That was kind of the issue. He was teaching by the book with no regard to the database engine used. I was using SQL Server and MySQL daily.

There was a fundamental disconnect. I approached a class project differently not only because I was a DBA, but I am neurodivergent. Because my design didn’t align with his expectations, I got a C.

I had a 4.0 GPA prior to that, but that made me realize I wasn’t a fit for college.

Good thing as well because I am debt free today.

Re:SQL is syntactic sugar for relational algebra by AdrianB1 on Saturday March 23, 2024 @09:20AM (#39799738)
I failed COBOL in college in a similar situation: I was professionally writing COBOL, my project was too divergent from what the teacher expected. I took that exam 3 times to barely pass. Good times, good times.
Re:SQL is syntactic sugar for relational algebra by doubloon on Saturday March 23, 2024 @10:48AM (#39800332)
i took compilers and prof wanted us to write lexer code by hand for a specific language. instead i wrote a generic lexer generator and fed in the language definition to generate the lexer code, got a bad grade for not following instructions. it seems kind of funny now but at the time i was rather cross.
Re:SQL is syntactic sugar for relational algebra by 20after4 on Saturday March 23, 2024 @12:29PM (#39801073)
I also got a C in my cs relational databases class. I also got more out of that class than just about any other in CS. I'm still kind of mad about it though. We had a group project assignment and our group had far-and-away the best project, of which I did 90% of the work. My bad grade came down to just one thing: the class required us to use Oracle and I refused on moral grounds.
Re:SQL is syntactic sugar for relational algebra by elcritch on Saturday March 23, 2024 @09:42AM (#39799869)
In my experience few CS students seem to take a relational databases course. It was optional in my program. Unfortunately, that means many of those databases were created by folks with no formal grounding in databases. Some folks pick it up on their own, but likely not as thoroughly as those who've taken a good course on the topic.
Re:SQL is syntactic sugar for relational algebra by default-kramer on Saturday March 23, 2024 @11:23AM (#39800603)
Yes, people who don't understand SQL databases can easily create "F-" situations. The problem is that people who are great at SQL databases can't do any better than a C+ because that's as good as SQL gets.
Re:SQL is syntactic sugar for relational algebra by randomdata on Saturday March 23, 2024 @01:21PM (#39801499)
> Blame incompetent people doing SQL badly.

Everything you spoke to is around people doing relational algebra badly. That has little to do with SQL. The same mistakes would lead to the same problems if the database used, say, QUEL instead.

Perhaps it's not SQL that you love at all?

Re:SQL is syntactic sugar for relational algebra by solumunus on Saturday March 23, 2024 @02:30AM (#39797903)
I agree that SQL syntax is terrible, but for some potentially masochistic reason I still like writing it. It could certainly be done better though and I don’t understand how there hasn’t been more innovation in this space.
Re:SQL is syntactic sugar for relational algebra by IshKebab on Saturday March 23, 2024 @04:01AM (#39798273)
There's PRQL which is really nice. But it has pretty big downsides: it only supports querying data, not modifying it; and since it's not database-specific it doesn't support all the features of whatever database you're using. E.g. it can't access complex data types like structs/arrays in DuckDB.
Re:SQL is syntactic sugar for relational algebra by refset on Saturday March 23, 2024 @05:37AM (#39798621)
> I don’t understand how there hasn’t been more innovation in this space

I think it's simply that most businesses and investors don't register SQL as having any real problems, and especially now with a resurgent interest in SQL the idea of attempting anything novel feels too risky.

Shameless plug of one recent attempt to offer something different: XTQL https://docs.xtdb.com/intro/what-is-xtql.html

Re:SQL is syntactic sugar for relational algebra by account-5 on Saturday March 23, 2024 @06:27AM (#39798819)
Having been forced to write queries as JSON in elastic I definitely prefer writing SQL. I would actively avoid utilizing any query language where I have to count brackets.
Re:SQL is syntactic sugar for relational algebra by refset on Saturday March 23, 2024 @06:50AM (#39798927)
Handcrafting JSON is undoubtedly always a pain, but the idea with XTQL is rather that it can be easily generated from any regular programming language.

> I would actively avoid utilizing any query language where I have to count brackets

That's really an editor/tooling problem, solvable in many ways, but I guess a Python-like/Parinfer approach would be your preference? (where whitespace/indentation is significant)

Re:SQL is syntactic sugar for relational algebra by account-5 on Saturday March 23, 2024 @09:19AM (#39799727)
True enough, tooling is key. I'm not a developer but I think SQL is a decent language that's easy to pick up. But I also like xpath, regex, and jq syntax too, so maybe I'm the weird one.
Re:SQL is syntactic sugar for relational algebra by bradleyjg on Saturday March 23, 2024 @07:50AM (#39799229)
PG’s blub essay isn’t especially charitable. There’s a good reason to want to stick with what you know and are comfortable with—-climbing endless learning curves means not getting things done and it isn’t simple to know ex ante what is worth the time
Re:SQL is syntactic sugar for relational algebra by refset on Saturday March 23, 2024 @08:27AM (#39799431)
Which is fine when everything is handcrafted and human-scale, but as soon as you start going down the path of machine generated SQL (both the generation and analysis thereof) the tax is non-trivial.
Re:SQL is syntactic sugar for relational algebra by masklinn on Saturday March 23, 2024 @01:33PM (#39801589)
It's not bad enough that it's worth actually trying to overturn, and the inertia from everything speaking SQL means there's a lot less pushback if you also do that.

SQL actually killed alternatives back in the 80s (and 90s, Postgres used to use a dialect of Ingres's QUEL, the "SQL" bit of PostgreSQL specifically denotes the switch over from QUEL to SQL).

Re:SQL is syntactic sugar for relational algebra by sherburt3 on Saturday March 23, 2024 @07:22AM (#39799091)
Strongly disagree, complete newbies can get up and running with SQL very quickly and it's expressive enough that experienced people can do things that no ORM would ever hope of being able to do. One criticism I have though is that whoever thought "SELECT" should come before "FROM" should be fired.
Re:SQL is syntactic sugar for relational algebra by Mutjake on Saturday March 23, 2024 @07:52AM (#39799236)
I sort of feel the same…but on the other hand if you consider ”delete from” exists also, it’s not completely unsensible to consider you first tell what operation you’re about to perform to the data. Would be nice to start with the source entity name for sure. Dunno what ”select 1” would look like, I guess the from foo would be optional.

Random saturday ramblings, sorry about that :-D

Re:SQL is syntactic sugar for relational algebra by williamdclt on Saturday March 23, 2024 @09:59AM (#39799994)
DELETE FROM is even worse. Accidentally/mindlessly press cmd+enter before you wrote the WHERE? Poof, data gone. Make it FROM … DELETE!

I also wish we needed to explicitly take locks. In PostgreSQL at least (I think other dialects/engines too), figuring out what operation will take what lock and potentially mean downtime is left as an exercise to the reader. Force me to write WITH EXCLUSIVE TABLE LOCK when I add a non nullable column with a default!

Re:SQL is syntactic sugar for relational algebra by ndriscoll on Saturday March 23, 2024 @10:37AM (#39800259)
If you're going to run commands that modify data directly on the cli, do it in a transaction so you can roll back. Also, start with `--` to make it a comment. Once you have the correct query and someone's checked your work, go back to the beginning of the line and remove the `--` so you can run it. It's also a good idea to write a select * or count first to check that things make sense, and then start your transaction, go up and modify your select into a delete/update, check affected rows looks good again, maybe do another select or two to check that data looks like you expect, commit.
Re:SQL is syntactic sugar for relational algebra by Izkata on Saturday March 23, 2024 @02:19PM (#39801975)
Fun fact: if you still have a MySQL (MyISAM) database, the transaction commands return success but don't actually function, so you can't roll back.
Re:SQL is syntactic sugar for relational algebra by Mutjake on Saturday March 23, 2024 @10:19AM (#39800101)
Well, to be fair ”from foo delete” would do the same I suppose :-D Unless there’d be an explicit end to the statement to designate you really want to delete everything. Which might not be a bad idea. Or make ”where …” mandatory and bring in ”delete_all” or ”delete everything from foo” as a syntactic guardrail. This is equally implementable, whichever the order of ”delete” and ”from” would be.
Re:SQL is syntactic sugar for relational algebra by Izkata on Saturday March 23, 2024 @02:22PM (#39802002)
The MySQL client has an --i-am-a-dummy flag that won't let you run a DELETE (or UPDATE, I think) without a WHERE.
Re:SQL is syntactic sugar for relational algebra by bbqq on Sunday March 24, 2024 @12:40AM (#39805204)
the Google SQL variant has where required for deletes. on my first encounter of it I was just like "huh, neat", you can always use 1=1 as the predicate if needed.
Re:SQL is syntactic sugar for relational algebra by antisthenes on Saturday March 23, 2024 @11:52AM (#39800777)
If you don't SELECT first before running the DELETE query, you shouldn't be anywhere near an IT job, let alone a production database.
Re:SQL is syntactic sugar for relational algebra by williamdclt on Sunday March 24, 2024 @01:00PM (#39808423)
Good tools prevent mistakes from happening rather than blaming the user for using it wrong
Re:SQL is syntactic sugar for relational algebra by alexvoda on Saturday March 23, 2024 @07:43AM (#39799189)
And yet I agree with both. SQL is at the same time easy to get started, incredibly powerful and yet also a complete disaster. Just like JavaScript but with standards you have to pay to read and implementers take their sweet time to implement. ORMs (with the possible exception of JOOQ, whose open source version is unfortunately also very limited) are hopelessly limited and have been a drag on the development of the field ever since.
Re:SQL is syntactic sugar for relational algebra by swasheck on Saturday March 23, 2024 @09:44AM (#39799891)
i think “disaster” is hyperbole. my brain thinks in concert with sql so it feels natural, though i will concede that the parsing/syntax order differences was an odd choice.

programmers and language users all have a mental translation layers that they use to go from conceptual->concrete using the syntax and idioms of their language of choice.

javascript doesn’t make sense in a lot of ways. calling it a disaster is hyperbole. go doesn’t make sense in a lot of ways. it’s not a disaster. unfamiliarity or disagreement with choices does not necessarily mean it’s a disaster

the only language with which i’m familiar that is a complete, unmitigated disaster is the english language, but i suppose that’s outside of the scope of this conversation

Re:SQL is syntactic sugar for relational algebra by runlaszlorun on Saturday March 23, 2024 @10:54AM (#39800383)
> the only language with which i’m familiar that is a complete, unmitigated disaster is the english language

Out of curiosity, are you a native speaker of english or did you learn it as a second language?

I’m a native speaker and feel sorry for anyone who has to learn all its irregularities, etc. But the few times I’ve mentioned this to anyone who had to learn as a second language, they’d correct me and tell it was easy. Which baffled me.

Obviously as a native speaker, I’m in no place to judge it.

Re:SQL is syntactic sugar for relational algebra by rqmedes on Saturday March 23, 2024 @09:46PM (#39804460)
SQL syntax seems how humans communicate to me. Can you pass me that cup from the cupboard
Re:SQL is syntactic sugar for relational algebra by alexvoda on Sunday March 24, 2024 @06:44AM (#39806307)
SQL syntax is only on the surface level similar to natural language. Besides, if we actually considered similarity to English to be an important criteria for a programming language, we would all still be using Cobol.
Re:SQL is syntactic sugar for relational algebra by lIIllIIllIIllII on Sunday March 24, 2024 @07:07AM (#39806408)
this totally depends on which language the human speaks. Chinese grammar is kind of like backwards English grammar.
Re:SQL is syntactic sugar for relational algebra by swasheck on Sunday March 24, 2024 @08:02PM (#39811664)
native english speaker. learned german (fluent) and italian (conversational). english is an insane amalgamation which may be why it’s become near lingua franca
Re:SQL is syntactic sugar for relational algebra by lukaseder on Tuesday March 26, 2024 @08:11AM (#39826847)
> (with the possible exception of JOOQ, whose open source version is unfortunately also very limited)

How is it "very" limited?

Re:SQL is syntactic sugar for relational algebra by rileymat2 on Saturday March 23, 2024 @09:07AM (#39799654)
I like it starting with the desired output columns, often when I work with a db quite a bit and have common naming conventions that’s all I need to know.

Even writing queries I think in terms of outputs then build the query from there.

I am blanking about the advantages of starting with from, people clearly share your view, but I have no idea why.

Maybe it comes from my C++ background where return type comes first, then function name, then inputs.

Re:SQL is syntactic sugar for relational algebra by arzig on Saturday March 23, 2024 @09:13AM (#39799694)
Ability to tab complete columns in an interactive settings.

If I’m in psql I can tab complete columns in the where clause but not in the select because I haven’t actually given any information about what I’m selecting from yet.

Re:SQL is syntactic sugar for relational algebra by rileymat2 on Saturday March 23, 2024 @09:18AM (#39799725)
This feels like a tradeoff we make where a convenience while writing once harms long term readability?

But that is a valid usecase!

Re:SQL is syntactic sugar for relational algebra by duped on Saturday March 23, 2024 @12:13PM (#39800934)
Imagine if methods on your C++ classes had to be called like method(object) instead of object.method(). While it's a meaningless syntactic difference, the latter makes autocomplete in an IDE possible.
Re:SQL is syntactic sugar for relational algebra by rileymat2 on Saturday March 23, 2024 @12:28PM (#39801058)
Sure, in that very small example, if you don't care internal about visibility.

But method(object1, object2, object3, object4) is different than object.method().

Re:SQL is syntactic sugar for relational algebra by duped on Sunday March 24, 2024 @03:07AM (#39805564)
I don't think visibility is important, since you want autocomplete to work both in public and private methods. It would be a crappy IDE experience if you couldn't autocomplete on private methods within other private methods.

And sure, multimethods are too complex for naive autocompletion. But it's useful for people to have tooling that can say "given an input tell me what outputs are possible" because that's how we write code.

Re:SQL is syntactic sugar for relational algebra by exabrial on Saturday March 23, 2024 @09:49AM (#39799923)
Completely agree with everything you said

Reminds me of reading music actually… many first time noob’s, mostly out of frustration, realize how sub optimal staff notation is and seek to write something better. In the end it’s ‘Good enough’ and accept it’s flaws because no rewrite has proven to be a 10x better solution.

Re:SQL is syntactic sugar for relational algebra by iamwil on Saturday March 23, 2024 @10:08AM (#39800035)
SQL seems to be the QWERTY of the data query world. It's far too entrenched.
Re:SQL is syntactic sugar for relational algebra by arrowsmith on Saturday March 23, 2024 @10:02AM (#39800006)
... unless you're playing guitar, in which case tab notation works way better ;)
Re:SQL is syntactic sugar for relational algebra by dizhn on Saturday March 23, 2024 @10:41AM (#39800288)
More like Tab + already knowing the song.:)
Re:SQL is syntactic sugar for relational algebra by rileymat2 on Saturday March 23, 2024 @09:49AM (#39799927)
It just occurred to me, do you have wide denormalized table queries where tables represent entities more than relations?

I think if so that might explain the difference in query structure.

Re:SQL is syntactic sugar for relational algebra by sherburt3 on Saturday March 23, 2024 @03:29PM (#39802487)
Yeah I maintain several BI reports and the codebase is about 20 years old, so multiple teams have taken creative liberties with the naming conventions for tables. Without intellisense I'd probably have gone insane by now.
Re:SQL is syntactic sugar for relational algebra by pkkm on Saturday March 23, 2024 @08:57AM (#39799603)
Agreed, I wish that SQL was more concise and organized more left-to-right, like shell pipelines or the F# pipe operator. But what are the alternatives? The only one I know is PRQL, and that doesn't seem to be getting popular (correct me if I'm wrong).
Re:SQL is syntactic sugar for relational algebra by __mharrison__ on Saturday March 23, 2024 @10:39AM (#39800276)
Pandas and now Polars can replace a lot of SQL. I find that many of my students find these easier to build up and debug.
Re:SQL is syntactic sugar for relational algebra by AdrianB1 on Saturday March 23, 2024 @09:24AM (#39799756)
It is organized, especially if you write it correctly (split lines and indent). Minified, it is a nightmare, but with a proper layout it is a pleasure to read it. I just did a code review for a 20,000 LoC piece and it was quite a breeze after I improved the layout a bit.
Re:SQL is syntactic sugar for relational algebra by pkkm on Saturday March 23, 2024 @10:09AM (#39800043)
"Disaster" may be an exaggeration, but I think the syntax could be better, proper layout or not. In particular, I find that complex SQL queries often need to be read in a "spiral" order due to SQL's attempt to resemble natural language. I find PRQL's consistent left-to-right/top-to-bottom reading order a clear improvement.
Re:SQL is syntactic sugar for relational algebra by listenallyall on Saturday March 23, 2024 @03:56PM (#39802675)
I can understand that the statement order can be awkward, such as choosing what to select before the source.

But help me with "more concise". SQL has no boilerplate, its keywords are brief, you express the exact logic of the result set you're seeking. There are tons of SQL overlays or replacements embedded in programming languages or BI/reporting tools, and they are universally, more difficult to work with than straight SQL.

Re:SQL is syntactic sugar for relational algebra by temporarely on Saturday March 23, 2024 @04:56PM (#39803045)
There is a paper at the end of this link trail and that paper discusses everything that is wrong with SQL including concision.

https://news.ycombinator.com/item?id=39798528

et voila: https://www.cidrdb.org/cidr2024/papers/p48-neumann.pdf

see their comments on WITHIN GROUP and redundant declaration of identifiers. As for boiler plate, SQL's turf war over plain English words as reserved names creates this interesting situation of boiler plate identifiers in SQL to disambiguate from reserved keywords.

Re:SQL is syntactic sugar for relational algebra by listenallyall on Saturday March 23, 2024 @08:02PM (#39803958)
This is nothing more than one guy's opinion dressed up to look like an academic study. He actually admits a few times that SQL features are concise. SQL definitely isn't perfect, and some vendors' implementations are better than others, but things like JOINs are pretty perfect. He considers specifying an INNER JOIN as syntactic sugar (over using WHERE equality), which seems backwards, as opposed to a more consistent practice of always using the JOIN keyword regardless of the type of join.
Re:SQL is syntactic sugar for relational algebra by pkkm on Saturday March 23, 2024 @10:02PM (#39804541)
That one was more of a nitpick, so maybe I shouldn't have included that. What I had in mind was that I would like a bit more symbols instead of words, for example value IN [1, 10] instead of value BETWEEN 10 AND 10.

Some operations on groups are also too verbose, like getting top 5 items in each group.

Re:SQL is syntactic sugar for relational algebra by listenallyall on Sunday March 24, 2024 @02:48AM (#39805509)
IN already has a different meaning. BETWEEN 1 AND 10 is exactly how a human would describe it to another person.
Re:SQL is syntactic sugar for relational algebra by anon84873628 on Saturday March 23, 2024 @06:51PM (#39803593)
Re:SQL is syntactic sugar for relational algebra by Scene_Cast2 on Saturday March 23, 2024 @09:34AM (#39799820)
My take is that SQL is a bit like C - very nice to know, but move off of it to a more "managed" environment for safety and sanity.
Re:SQL is syntactic sugar for relational algebra by williamdclt on Saturday March 23, 2024 @10:02AM (#39800008)
Definitely disagree on this one. ORMs are a leaky abstraction, they hide the wall you’re going to bump into anyway, and they bring their own gotchas on top
Re:SQL is syntactic sugar for relational algebra by Scene_Cast2 on Saturday March 23, 2024 @12:02PM (#39800854)
I worked with two frameworks so far - SQLalchemy core and a much bigger "orchestration" one (dagster / airflow equivalent). SQLalchemy got me type checking, column name checking, and DB migrations versioning with Alembic.

The bigger framework got me more static analysis and dependency graphs on top of that. Those features saved me lots of time and headaches (type checking, dependency graphs, etc). Mind you, the frameworks I worked with still allowed for raw SQL for those edge cases that still pop up.

Re:SQL is syntactic sugar for relational algebra by fudged71 on Saturday March 23, 2024 @10:29AM (#39800182)
Re:SQL is syntactic sugar for relational algebra by jameshart on Saturday March 23, 2024 @11:58AM (#39800822)
Syntactic sugar, in the sense that it is a syntax that is bad for you.