A Critique of SQL, 40 Years Later

Thanks for listing these. It's very useful.

(1) is a function of what SQL you might transpile to. Any SQL w/ rich types (like PG) or w/ JSON support (most others) will be able to meet (1) with varying levels of hackiness.

(2) even if some SQL RDBMS allows you to have tables w/o primary keys, the thing transpiling to SQL wouldn't be obligated to have tables w/o primary keys. Some SQL RDBMSes do not allow tables to lack PKs (e.g., SQLite3 doesn't), but even this wouldn't be a problem -if one wanted tables-with-dups- because one could use hidden row IDs to make up for that.

(3) just as with (2), the new thing transpiling to SQL wouldn't have to allow NULLs, and could generate SQL that uses `IS [NOT] NULL` / `IS [NOT] DISTINCT FROM NULL` to obtain the desired semantics if the new thing kept any notion of NULL.

(4) is not a semantics issue, and anyways, some SQL RDBMSes let you implement tables yourself, and for the others there's CTEs and TEMP tables.

Of these only (1) is a problem if you want to target pure standard SQL, though it depends on which SQL standard you target.

We're definitely not talking about transpiling to 1980s SQL!

So, while there might some semantic impedance mismatches for a transpiling approach, and the choice of RDBMS to transpile to matters, on the whole I'm unconvinced that there are such problems that are very serious. My own imagination is failing me in my attempts to come with such semantic impedance mismatches.

> This is equally true if FROM comes first since you have no idea if customer_name in your SELECT way at the end actually comes from a table directly, a subselect, calculation, etc.

When you get to the point where you see customer_name, you know what it came from because everything you read prior to that.

Imagine going into a shop to order three burgers and saying “the first one is without onion, the second is without onions and the third without picked, I’d like a big Mac, a quarter pounder and a hamburger please.” It’s just not the natural reading order.

> I would be interested in seeing what a complex WITH or subselect would look like if you're putting FROM first

The premis for this seems to be that your arguing that moving SELECT to the start of the query even though this isn’t he natural evaluation order nor reading order somehow helps to increase readability. But I don’t believe the there’s any reason to assume that. Just because it’s how things are currently and how you learned it doesn’t mean it’s better, it just means you’ve grown accostumed to it and therefore you feel it’s simpler. But everyone learning SQL from scratch who don’t initially learn actively that select isn’t applied in the order it’s written ends up making the same silly mistakes and being confused about the same things that would be obvious if we just put it where it actually belongs.

In "Stating the obvious", C.J. Date wrote 50 (FIFTY) pages solely on the subject of how SQL screwed up with the most fundamental operator of all : testing for equality. I'll repeat : FIFTY pages about everything that SQL got wrong with just the equality operator.

Go figure about the rest ...

In the relational model, no there is no such thing as "optional values".

Even Codd often hinted (quite a bit of material in the 1990 book gives such hints) that "if a row has a null somewhere, it SHOULDN'T BE CONSIDERED AS BELONGING TO THE RELATION". E.g. if an attribute of a FK is null, no FK checking should be done at all on account of that null. The FK checking should be done only when the FK value is "complete".

As for outer join, one problem with it is that its result is (intended to be) the extension of a disjuctive predicate, the disjuncts in question being exactly the predicate for the "matching" case and the other one the predicate for the "no match" case. Now if you see a row in the result, how can you tell from that row which of the two disjuncts made that row appear in the result ?

Outer join is problematic by definition precisely because of the possibly ambiguous interpretation that derives from its disjunctive predicate.

(Would you do that with base tables ??? I mean design a base table that can hold a row for strictly more than one possible reason ??? I mean where the user then sees a row in that table and subsequently cannot tell whether that row is there for reason/meaning 1 or for reason/meaning 2 ??? I'll tell what you -and anyone else- would do in such a case : you'd also add the indicator telling whether it's meaning 1 or 2 (and then the problem of 'what if it's both' is also solved). Now think back about what could be done in outer join to resolve that very same kind of disjunctive ambiguity.)

:-)

And those that are built on "first principles" end up looking like a dog's breakfast just as well because people expect to not have to learn a novel language.

People don't want to be told to forget everything they thought they knew. People don"t want to be told they've been wrong for 40 yrs. Even if you add that it's not entirely their own fault because they've been consistently misled by an entire industry.

You can crack the problem of supporting CREATE ASSERTION, they'll still dismiss it (handwave handwave) believing you're just bluffing.

And I mostly disagree with the optimism built on Datalog.