Comment by CopyOnWrite | Hacker News Reader

Comment by CopyOnWrite | Hacker News Reader

By now I stopped counting the attempts to replace SQL.

There is a lot of valid critic for SQL and I would be very happy if some things would have been designed different.

OTOH the architecture and mathematics behind relational databases are simple, composable and stood the test of time more than most other designs, methodologies or approaches to software development.

Though SQL can be improved, even with my average SQL skills I never had trouble getting information out of a database and fancy stuff like window functions make to my understanding even standard SQL Turing complete.

SQL has the native database support, for most companies the data and the database will outlive any specific application or even the whole ecosystem of a programming language/platform (Visual Basic, Visual FoxPro, Python 2, ...)

Further, we have fantastic books, knowledge, ORMs, query builders and a gigantic ecosystem in tools for SQL and SQL databases.

Acadia might be brilliant from a technological point of view, but it does not matter, because it does not look like a big enough improvement compared to SQL that it seems worth to invest in it. I will rather improve my knowledge of standard SQL or my knowledge for a specific relational database.

Finally Acadia does not really seem to raise the bar compared to other ORMs/Query builder. I get that from a FP point of view map/filter are nicer than a SELECT ... WHERE, but at some point in the projects I participated one would end up interacting directly with the database anyway, and at that moment I am back at SQL, so what did I gain?

SQL has one flaw: The verb should come last. So, "FROM users WHERE id = 1 DELETE" or "FROM users WHERE email = 'foo@example.com' SELECT id". That'd cut back on some accidental "oops I dropped the whole table" because I submitted a delete query before writing the where clause.

Other than that, it's perfect, no notes.

The part that has stood the test of time and genuinely seems to carve reality at the seams is the query part. The data definition and data manipulation parts are just ok.

The problem with the query part is that query fragments aren't composable.

CTEs are how you compose SQL.

I don't quite like how the same CTE lives in 60 different places in my codebase, but at least the WITH clause changed things for me.

Also really liked Snowflake's result_scan for composing chains, mostly because I don't rerun expensive parts again and again. You can use ->> as a shortcut, but I don't think it uses results caching internally to skip waiting for them to all re-run & actually optimizes the whole thing.

you create one view and have the ctes query that, to deduplicate the implementation

Even so, "FROM t SELECT t.foo, …" has an ergonomic advantage over "SELECT t.foo, … FROM t" in that editors can autocomplete column names without needing to backtrack while editing.

IIRC, this is why C# query syntax uses the former.

Agreed, however it gets easier when using SQL plugins on IDEs, instead of raw cmdline admin tooling.

SQL is based on the relational model but doesn't really conform to the mathematics e.g. doesn't exhibit set semantics.

Can you elaborate?

It can't express every mathematical set operation, but it does have UNION, EXCEPT, and INTERSECT.

What they're saying is: The relational data model and algebra are based on set semantics. Relations (equivalent of SQL's "tables") are sets of sets (tuples), not bags of "rows". There's no such thing or possibility as duplicate tuples ("rows" of "columns").

This has a number of elegant properties (and also improves the kinds of optimizations a query planner / execution stage can apply.)

A similar divergence is that the relational model has no concept of nulls. Presence/absence is expressed through "item not in set" in various ways, and by properly normalizing the data.

SQL also isn't properly expression oriented or composable at all. A relational algebraic language absolutely can be, and can lend itself to much more elegant data handling.

In many ways SQL is to "relational" like Java or C++ are to "object oriented" -- it got in very early to market, got mainstream success, and dominated the field, and in so doing it mangled people's perceptions of what a database is, and also made people either define "relational" as "SQL" (sigh), and even worse because they misunderstand what relational is while also hating SQL, they try to throw the baby out with the bathwater with their successors.

> In many ways SQL is to "relational" like Java or C++ are to "object oriented"

Very cogent.

Relations also have no concept of ordering. But bag semantics is both closer to efficient implementations and closer to user expectations than set semantics. Using set semantics everywhere also makes queries harder to optimize, because you have to selectively "de-deduplicate" for efficiency, instead of just sticking DISTINCT operators where they're needed.

A result in SQL can contain duplicate items unless you tell it explicitly to deduplicate, so uses multiset/bag semantics. The relational model is built on set semantics, where every item is unique. Just because it can express those operations doesn't mean the idea is baked into the language semantics. e.g. the difference between Haskell and Python + first class functions; you can do functional programming in Python but it's not a functional language.

Sets and bags are trivially interconvertible so it's really not a big deal: https://h2.jaguarpaw.co.uk/posts/set-bag-irrelevance/

The point is if you're doing relational algebra you want to work with relations. The key reason why set semantics are nice is because the operations are guaranteed to return relations, so you don't have to check or make accountings of which return values are sets and which are bags, or worry about machinery to convert between the two.

It's like how you can store numbers internally as floating points or rationals and trivially convert between the two. But if all you ever do is floating point math, you might prefer to store the numbers as floating points rather than rationals and then convert to floating point.

I don't think that's actually what one wants in practice from an RDBMS, but if it really is then one can add UNIQUE to all your SQL queries and get it. SQL is not somehow lacking in that regard.

Reply on news.ycombinator.com