ErwinSmout

Not necessarily.

If you ponder the importance of proper (robust, reliable, dependable) data management for data that keeps nuclear plants going, for farmaceutical research data, for anything happening on the financial markets, for medical records, for data concerning payroll and the like, etc. etc. then you might appreciate that all the stuff mentioned in the list is indeed really "just toys".

And Tony Hoare probably wished he hadn't made his "billion dollar mistake".

But at any rate, you could also try and ponder how unbelievably exceptionally awful it must have been that existed before SQL if something as truly awful as SQL could still be as successful as it has been.

SQL (the language that is, as defined by ISO) has COLLECT and UNNEST operators which come reasonably close (not 100% of course) to "Date's concept of relation-valued attributes". Moreover, I'm told those two operators are supported by at least one of the three big dogs.

"The one that his a lot of people is WHERE <value> NOT IN (<set>) where <set> contains a NULL. Because NOT IN unrolls to “<value> <> <s1> AND <value> <> <s2> AND … AND <value> <> <sx>” any NULL values in the set makes one predicate NULL which makes the whole expression NULL even if one or more of the other values match."

Sorry, but this is FALSE.

"Even if one of the other values match" will make that particular INequality test FALSE and one FALSE conjunct will make the entire conjunction FALSE, even in 3VL.

Yes. The reason is lazy people expect to be able to ask five questions in one go and expect one single answer to answer all five of them.

The key is to realize that submitting a query is to ask a question. So how many queries to you think you have to issue if the number of questions you have is five ?

It is technically perfectly possible to "ask five questions at the same time" to any given truly relational DBMS. Said DBMS will give five answers at the same time (and not leave you to guess which answer corresponds to which question). That SQL doesn't do it, does not mean it's impossible.

The predicate corresponding to an outer join is fundamentally disjunctive in nature (any outer join is equivalent to a UNION of at least 2 SELECTs) meaning that "after the fact", there is no way to determine which particular one of the disjuncts made the row appear in the result.

"Many of those complaints seem theoretical. I like to focus on practical concerns."

The only reason ever why people engage into theory, is precisely because of a deeply rooted desire to address "practical concerns". Hawking literally stated that his aim was to understand everything about the entire universe.

People who "like to focus on practical concerns" see an apple fall and (after they've seen it happen often enough and never ever otherwise) ultimately conclude that "apples fall and that's just how it is". Newton saw an apple fall and ultimately unveiled the concept of gravity. In doing so, Newton also unveiled the (admittedly extremely exceptional) circumstances in which it might be possible for us to see an apple "fall upward into the skies" after all.

:-)

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.

"There were also definitely some bad paradigms invented as a result of SQL (e.g. all business logic lives in the database as stored procedures or stored functions),"

I hope you don't mean by that that integrity enforcement (checking the compliance of the data with ALL declared business rules) does not belong in the DBMS. Because the exact opposite is true : inside the DBMS is the only place where it belongs. Or if not the only, then certainly still the primary.

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.)

"NULL only has one meaning: NULL. This is roughly analogous to unknown."

From the re-published version of "Much ado about nothing, part 2" (re-published in "Database Dreaming, Vol II) :

"An outer join produces nulls because we ask the DBMS to show us data from one table even when no matching data can be found in another table. Such nulls have no meaning, no semantic content."

Personally I'd add to that : and even if it's the case that they do, that meaning is patently not "simply unknown" but rather a very clear indication of non-existence.

But I have no doubt the apologists will obviously always apologize no matter what and handwave arguments such as these away with "So what ? What's the difference." Well, it's the difference between knowing of non-existence and not knowing at all.

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 fact, Date has recently revised a whole slew of "old" papers of his, got them up-to-date wrt his current thinking, and (re-) published them. His "A critique of SQL/86" paper(s) are now chapter 8, 9 & 10 of "Database Dreaming, Vol I" (there's a Vol II too).

What they "called out as flawed" was the impossibility to declare any such rule with SQL as it stood. What they were "criticizing" was precisely the fact that a data language that was supposed to be "expressively complete" was *unable* to express such a rule. You can't claim "expressive completeness" for a language if there is demonstrably a case where said language's expressiveness fails to meet the mark. It's not the "behaviour of the language" they were criticizing in all/any of those use cases that were supported by the language as it stood. They were criticizing the fact that there was a use case [and a relatively reasonable one on the face of it] that the language couldn't support.

Because "speaking SQL" [even before SQL as such was even invented, hence the scare quotes] was intended as a skill to be practiced only by those who also grasped the [mathematical] logic of what they were doing when "speaking SQL". None of the people who "speak SQL" these days fit that bill.

And FWIW, what I wrote was in reply to "mental model". It was *you* who apparently equated that with "speaking English". The "mental model" I described as "exactly the problem" is the "mental model" *of the corresponding logic*, which, though mathematically consistent, is hopelessly unintuitive. Being English-speaking or not has nothing to do with understanding a particular logic underpinning the behaviour of a particular Data Manipulation Language.

With one single exception : you cannot use a CHECK clause to check that a table must be nonempty, because the semantics of the CHECK clause are that it must be satisfied by all rows in the table, and therefore the empty table trivially satisfies any CHECK clause.

I invite you to inspect, even if only cursorily, my above exchange with mr. antonvs. My point is that Micro$oft was created by programmers (in fact in its earliest days it was mostly a compilers company), that to this very day the vast majority of what it does falls into the category "by programmers, for programmers", and it may indeed be the case that they are extremely good at that. And if you are yourself a programmer, then it is obviously only self-evident that you are then going to praise/applaud Micro$oft for what they do. It's just that this "programmer perspective" may yield a hopelessly narrowed down tunnel vision on what the real goal should be. That real goal being : making complete sense to the final end-users. And as an example of "not making complete sense" : I open an xls file with multiple sheets in it. I browse through the contents, paging up and down through the sheets. I'm done browsing and I close the file. Microsoft asks me if I want to save my changes. The reason of course (or presumably) being that somewhere hidden inside, there's a "current sheet" indicator and it is considered part of the data because it gets saved alongside the actual sheet contents, so the file can open up with the last sheet displayed already active. But you show me one single user (and especially one not coming from an IT background) who never once in his life reacted to that with "WHAT FRIKKING CHANGES ARE YOU TALKING ABOUT ? I CHANGED NOTHING.".

No, Date did not "miss the point". He properly identified that all of Meijer's arguments he used to make his point were in fact complete bunk. And he clearly and factually answered why that was so, on a blow-by-blow basis. You call that a "nitpick at what he saw as mischaracterizations", I call that "identifying the blatantly ignorant elephant in the room".

The only perspective should be "how can we get information from users (or any other form of information-capturing device in fact), make records of that information and keep those records as long as the information may be needed [and as long as it pays off to keep them], so that we can later get that information back to same [or other] users". Let's call that perspective "HG" for "Holy Grail".

Codd's intent has always been to achieve HG. Date's intent has always been to achieve HG. Both properly identified the core asset we work with : data. It's always ultimately about the data. Both consequentially properly identified the first step needed to make achieving HG possible : a model of data built on foundations of mathematics. (To quote Codd : "database design is not going to be possible if the only concepts available are bits and bytes".) The model of data they came up with is the relational one. And achieving HG is not going to be done by ditching the model of data. On the contrary. It might be achievable by replacing it with some other model of data, but that is going to require exhaustive demonstration of how and why the replacing model of data is better than the relational one, and delivering such exhaustive demonstration is going to require understanding the relational one in the first place. Of the entire mob that is into what you call the "programmer perspective", there is not a single individual that does.

And Date's point in his reply was : neither does Meijer. Backed by evidence, so it was neither "shooting the messenger" nor "a nitpick" but unfortunately it might require understanding the RM to understand the evidence.

To close, I have some hints for you : (1) there ARE NO "existing relational databases" (2) "BigQuery" is not a "database" but a "DBMS" (there's a Dijkstra quote somewhere about how mastery of mathematics and mastery of language fortuitously tend to always come hand in hand - and there's a corollary to that) and (3) yes, the entire industry has indeed been "wrong for decades" because the RM has never been properly understood. See the McGoveran quote on www.dbdebunk.com. Ironically, this grave mistake might be due exactly to "too much programmer perspective" at the time those pseudo-relational systems were built.

Dude, please, give me a break.

Erik Meijer is that, eurhm, person, I remember from publicly stating that his favourite resarch method is "throwing things at the wall and see what sticks". WOW. That's the way of the true academic. Of course it might have been the case that he was being really truly utterly facetious/cynical, but I certainly don't recall having any sense of that, not even remotely, upon reading that remark. (And if it means anything to you, I don't recall someone like Dijkstra ever writing anything like that anywhere. But of course Dijkstra was one of the last *TRUE* academics.)

And he is also that, eurhm, person, I remember from "All your databases are belong to us", which drove Chris Date to writing the response he did (published both in the ACM and in his own book "Stating the obvious") in which he publicly shamed the ACM itself for lending its pages to such sheer utter nonsense.

You might want to read the substance. If it shows anything at all, it's the absolute absence of value there is to be found in having a PhD these days.

And there is very, VERY, little in your reply that does not fall either into the category of "argumentum ad verecundiam" or else into that of superficial handwaving.

The basic text of the book was written before Lex died (must have been +- 2006), my work in which I cracked that problem saw the light of day after that time. So "we do not yet know ..." was technically correct at the time they wrote it, it no longer would be now.

That other snippet "Standard SQL is relationally complete ... CHECK clause ..." is technically correct, but the standard allows subqueries referencing other tables than the one the CHECK clause is on, but as far as I'm aware no product supports that (and the ones that do leave the user exposed to risk of faulty behaviour). Sadly, such a feature is necessary if we'd want to write, say, an FK constraint in the form of CHECK clause on the referencing table : CHECK (EXISTS (SELECT 1 FROM PARENT WHERE <FK equality tests here> ) ).

It was probably also influenced by Codd's own preference for the data language in calculus-based style (Codd's roots as a mathematician are to "blame" here), as opposed to algebra-based (the style typically preferred by developers).

"And yet an API is kinda desirable."

Yeah. however I may not live to see the day when that desire, which I agree is felt by 99.99% of the developer community (hell, even by 99.99% of the end user community because don't come and tell me that what that community is feeling isn't some sort of sense that "the developers just can't offer us any answers"), actually gets to be fulfilled.

"compiling to a standard AST"

I think that what you might be failing to appreciate is that achieving that requires a "standard algebra", and that no such thing exists at this present day.

In the perception of the Micro$oft users who are brainwashed with the idea that what Micro$oft does is good for the developers.

(In fact, it might even be outright true. But that's not a guarantee that what the Micro$oft users do with the Micro$oft tools is necessarily also the *BEST* thing for the user of that software product that the Micro$oft users produce.)

Link between me and SIRA_PRISE : I am the author.

codeshitter ad-homs : yeah well I know they are. The fact of the matter is the history between SIRA_PRISE and me (and why I did it in the first place) is now almost 20 yrs old, and I know how it's been received, and that's primarily due to (a) how the codeshitters (and the way how they are subject to the Dunning-Kruger effect) have come to dominate the entire industry and (b) how that economic system I too am bound to operate/survive in prevents the managers who control the system from taking "too much" (say, > 0.01%) risks.

And about "Pretty sure no declarative statement can be made efficient automatically so that remains a dream" : there fucking sure ain't no better way to piss me off, not just to the other side of the planet, but to plain outright Mars or Jupiter. You're just too pretty damn sure of yourself. AM4DP makes +- the same statement as you although the authors there still did manage to *NOT* make the same logically flawed inference from "not anybody knowing today how it can be done" to "cannot be done". You apparently fall into the category of people who do make that inference. The article exposing the exact opposite of your convictions was published in Oracle Users Magazine somewhere around 2013, IIRC. I'd need to look up the exact details by now, but it was already an incredible surprise Oracle User Group even wanted to publish on the solution of a problem that Oracle Corporation itself wasn'y (and still isn't, probably due to the power of certain people within that company who have been labeled 'bean keepers') prepared to invest in.

In fact, the only reason I wrote that article in the first place was Toon Koppelaars using that very word 'dream' (which you also used in your reply) to associate the idea with SQL's CREATE ASSERTION, when I already knew it needn't be a 'dream' no longer ... Well, if anyone just wanted to believe, which they don't, you inluded, apparently ... To Toon's credit, although he also used the adjective 'impossible' to qualify that 'dream', he did manage to end his title phrase with a *question* mark. You ended yours with an exclamation mark.

And just for the record, this statement is *NOT* to be interpreted as "every thinkable constraint can be implemented with sub-millisecond violation detection". Sunt certi denique fines, quos ultra citraque nequit consistere rectum. The "fines" in case being the bounds of what algorithmics as it is known today, can do for us. Constraints like "all the people who obtained a degree in mathematics must be paid in the top 5% of salaries" (if anyone ever wanted to formally declare and enforce any rule like that) inevitably takes us into realms of 2nd-order logic that no known algorithm today can guarantee us execution times like the ones we have grown accustomed to from what is supported by SQL systems these days).

As for "do you have a reliable statement" ... I have two answers but neither have the quality of being dependable on the academic level of meaning you might probably want to attach to the words "reliable" and "dependable". The first answer is "No, because the only other person in the entire world that I'm aware of achieving results in the same area is professor Davide Martinenghi and his PhD thesis on the subject, of which I don't even consider myself capable enough to academically assess the equivalence between his results and mine" and the second answer is "No, because that 'reliable' statement would have to be made by me myself and I have nothing(*) but my own implementation to 'prove' it and I'm very well aware that on an academic level, a seemingly working implementation is not a proof".

(*) I did write a paper on the subject and it's been seen by Chris Date (who by proxy answered that he was 'impressed'), by Hugh Darwen, and by Adrian Hudnott. And I will rather take it to my grave with me than disclose it any further.

And about the *scrutinously detailed subject* of your question whether "efficient 'create assertion' in sql is possible in general" : I have grown convinced that *IN SQL*, it isn't. But the reasons are *ENTIRELY* related to SQL's depending on 3VL. I have grown convinced (in fact, for my own conviction, I think I have sufficiently demonstrated) that in some *other* DMl language that *embraces 2VL*, it is *perfectly feasible*. And in fact, I think SIRA_PRISE itself is its own proof, in that respect : *ALL* the rules that SIRA_PRISE imposes as business rules on its users are implemented as mere declared database constraints on the catalog, and are enforced using the *exact* same machinery that also enforces the *user* declared business rules on the *user* databases. When I showed my system to a former colleague of mine who made his entire career in data [base] administration backed by a degree in mathematics, he merely responded that there just ain't no better POC than that.

See the "business case for SIRA_PRISE". Imagine how many years of codeshitter-hours you [or, if you're in the DBMS market, your customers] would no longer have to pay for if you could have *ALL* of your [strictly data-related] business rules enforced by a mere *declaration* made by the business analyst c.q. the data administrator (note the absence of 'base' in that term), as opposed to having to rely on those very same codeshitters because there simply ain't no other way for you to enforce same set of said business rules other than to pay that mob for coding all that application-enforced integrity (which they are bound to get wrong because it is fundamentally beyond their ability) or the stored procedures that achieve the same but are still procedural (and is still bound to fail for essentially the same reason).

If you know about TTM, you might also know about "Applied Mathematics for Database Professionals". What I'm referring to is their "execution model 6" becoming possible *WITHOUT* any [procedural form of] coding [by mere programmers].

Another way of saying this is "You can have CREATE ASSERTION if you want to".

Imagine you need to write a query to answer "what are the cities with more than 4000 inhabitants".

What more natural could there be than to write this as (old 1992 correlated query style)

SELECT cityName FROM Cities C WHERE ((SELECT COUNT() FROM Inhabitants I WHERE I.cityName = C.cityName) > 4000)

Agreed ???

YOU CAN'T DO THIS IN SQL [standard version] !!! You must write

SELECT cityName FROM Cities C WHERE (4000 < (SELECT COUNT() FROM Inhabitants I WHERE I.cityName = C.cityName))

This makes SQL about the only language in the world that allows you to write "a<b" in certain places but forbids you to write "b>a" in those very same places. Some professional piece of language design.

(Disclaimer : I don't know how matters are in SQL:2016 wrt to this issue, but it drove a former IBM employee who was at the time a member of the SQL standards committee to (a) start work on TTM and (b) leave IBM as soon as he could.)

I doubt that it does. There is way more to "integrating [host] language and queries" than the MIN() of what Micro$oft engineers are (a) capable of understanding and (b) allowed by their own management to put in the products they come up with.

And in the case of SQL, that is precisely the problem.

That's because the true foundation is that relational algebra is (a) an algebra and (b) closed over relations. (a) means you should have an expressions system that allows arbitrary nesting and (b) means you should be able to arbitrarily nest relational expressions in particular. SQL fails heavily on (b) (it fails less so than it used to do with the SQL:1992 standard but even so, it's still far from where we should have been and SQL itself is still an abomination).

Moreover, your very examples illustrate how "thinking SQL" is *NOT* the path to finding proper solutions. .skip() and .take() clearly originate from the world of ranking problems and it drips off of every word that you were thinking of finding a "modern" way to specify [things like] "TOP 10", but those solutions are themselves a fundamentally crippled way to attack the problem.

That "the API becomes unwieldy" is in fact inevitable. In order to support .where(<boolean expression>) as well as .select(<any expression>, ascolname) you need a way to pass a parameter of type something-like-expression to the .where() and .select() methods, and lambdas or at least something like them appear to be the right way to do that (e.g. .where((foothing) -> (foothing<0)), but languages offer no means to "introspect into the lambda" which is what is needed to translate it into SQL.

Plus (assume my .where() example was Java), the host language compiler is never going to find a way to cope with the declaration of the 'input' of the 'lambda', *precisely* because [the definitions of] those things are outside the scope of what the host language compiler knows about. So you need a way to do something like "import my-db-definition;" And then you need to put machinery in place to verify at runtime that the definitions as they are for the db, are still the same (or compatible) with the definitions that the program was compiled with. Etc. etc. etc.