Because SQL is a nightmare.
(Standard is in thousands of pages; nobody fully implements it; not actually a single language as usually each db has deviations and extensions; nonmodular, composability is hard - an afterthought)
And the worst part: nothing better exist; single’ish bad language is better than dozens of new shortlived ones that have quirks in various other places.
But somebody needs to be idealist and keep trying.
That is quite a hyperbolic statement and I have to disagree with you. I've used SQL on a variety of databases over 15 years, and yes while some like MySQL have poorly implemented anything other than basic SQL/RDBMS features, most are very similar in feature sets. There are vendors-specific additional features like how Oracle supports hierarchical queries with CONNECT, but you don't have to use them.
- CTEs are very close if not the same across Oracle, PostgreSQL, DB2, Hive, Snowflake, and MS SQL Server - I believe even Sybase too but it's been a while. - Joins work all largely the same even though a couple of those support additional join types, especially when you want to join on functions that return data sets.
- Window functions are supported by every major DB with similar or the same syntax too. Any differences take 5sec to lookup in documentation.
My only complaint is loading data is highly vendor specific.
I used SQL in various implementations for about 15 years. I didn't find much fault in it until I started using KQL (the language which seems to have inspired Pql).
The difference in enjoyability is stark: I truly hate SQL now.
"At the same time, I have to say too that 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)."
MySQL has supported CTEs since v8.0. Sadly, still no support for RETURNING though, which limits their usefulness, but it does in fact support CTEs now.
Most of these problems are pretty trivial to solve by simply reading the manual for the database engine you happen to be using, which typically will document deviations from the standard in serviceable detail.
I'd be more sympathetic to concerns about switching engines if I had, at any point in a career now heading for its 25th year, ever seen that occur.
Given the frequency with which ORMs are used in greenfield to defend against this notional problem, and the many problems that using an ORM always inflicts, this may well be the costliest form of premature optimization I've ever seen. It certainly can't be outside the top three.
And the worst part: nothing better exist; single’ish bad language is better than dozens of new shortlived ones that have quirks in various other places.
But somebody needs to be idealist and keep trying.