And if you're set on building an "entire DB engine" then you can just as well go the full mile and go for relational. As opposed to SQL.
15 karma · joined September 7, 2016
Since the addition of SQL/PSM (1996 IIRC) it has become a computationally complete language (procedural like many of the others) with variables and loops and what have you.
Date&Darwen formally pinned down what such a language has to look like in their three subsequent editions of "the third manifesto" (first edition roundabouts 1995). There are at least two fully operational systems that comply.
Which is precisely why "better" is both possible and desirable.
And which is also why either of forward and backward "compatibility", to the fullest 100% extent, is intrinsically and inherently impossible.
And as for "waste" : if while learning a better language, they are in addition also learning the relational model, and to think relationally, (their knowledge of both of which will be VERY poor if all they've ever seen is SQL) then there can't have been much time "wasted", can it ?
The DBA you were mentioning here, e.g., could first do a query in the EXPLAIN results, and he'll have his "company-wide queries audit" in a matter of seconds.
And "hints" in the language are not exactly going to improve "interoperability" if their meaning is still allowed to be implementation-defined, are they ?
However. You are wrong tying the problem to joins. I remember an analyst who launched a SELECT COUNT because he was just curious about the number of rows in the table. No joins involved but users did suffer. Elsewhere in this thread I've seen the problem be tied to table scans, and that's also wrong. A table scan isn't a problem if it's a 5-page table. As Darwen often argued : why are people always only lamenting about those couple of tables with millions of rows ? Why should we deprive users of the power of relational algebra if their database simply aren't that big ?
It's a matter of determining the cost of the data access strategy (regardless of JOIN/EXCEPT/what_have_you) and (implementing a protocol for) capping it at runtime (or earlier if possible). No need for language changes here.
The crux is : it takes much much more to achieve that than what the average C# or java coder yup has to offer. So the average C# or java coder yup will dismiss those things too.
(I understand full well that that is a problem if the company has kicked out the DBA role and handed it over to the individual programmers, but perhaps that is precisely the problem.)
Your 'transpile' handwaving won't fly. You are blindly presuming that the 'SQL equivalent' (a) always exists (it doesn't) and (b) can always be generated by an automated transpiler (it can't).
If you want to believe that popularity is a measure or reliable indicator of quality, I can't be bothered with you.
PS I have a worked-out example on my site of what it takes to enforce a business rule "no one has a salary higher than his manager's" in SQL (> 100 LOC) and in SIRA_PRISE (one single relatively simple formula of the RA to declare). You decide which is "better".
That's not a bug, that's a feature. Deriving from the deliberate intent to make physical independence (which was simply nonexistent at the time the model was conceived) a reality.
I'll give you just one : in SQL, you can write "WHERE 4 > (SELECT COUNT() FROM ... WHERE ... )" but you cannot write "WHERE (SELECT COUNT() FROM ... WHERE ... ) < 4" [Darwen, "The askew Wall"].
Or iow, for certain specific kinds of 'a' and 'b', you can write "a < b" but not "b > a". Do you actually know ANY language that exposes that kind of thing ??? REALLY ???
(And pls note carefully that I didn't say graph was obsoleted as a mathematical theory. What I did say was that graph was obsoleted as an appropriate mathematical theory * for underpinning doing data management with it *.)
It should not even take half a brain to understand why [pseudo-]relational replaced and obsoleted graph 4 decades ago.
Oh, and if you want to know why people don't want to find more "useful" operators than what they're used to from good old two-valued logic then I have a hint for you too : it's because they all immediately sense that their brains are not up to it as soon as they actually try (and my actually doing the maths has very clearly shown me why - so as you suggested to me "perhaps give it a try").
The way I see it there are practitioners who care about theory (and take the bother to try and understand some of it, and even more so the consequences on their practical tasks) and there are those who don't.
(Aside : the world of fact-oriented modeling and especially FCO-IM - that's communication oriented modeling - has this stance that our databases are actually not modeled according to how the world is, they are modeled after how our comunications about the world are. An interesting distinction you might want to ponder a bit more deeply.)
(BTW I doubt very much that "Codd designed null into the RM". Even his 12 rules mention only "a systemic way to deal with missing information", not "null".)
Computer SCIENCE is that ugly thing that tells us that three-valued logic gives rise to 19683 distinct binary logical operators, while two-valued has 16.
Computer SCIENCE is that ugly thing that tells us that if you want a computer language over a three-valued logic to be expressively complete, then you need to implement all of those 19683 logical binary operators one way or another. In the worst case, that's 19683 operator names for the programmer to remember. And you come here claiming that it's "trivial" because you have a "sense" of what the results ought to be ? That proves just one thing but site policy probably won't allow me to spell that out.
(In case you were wondering what the 16 names are in two-valued logic : they aren't needed because the system being two-valued gives rise to certain symmetries that gracefully allow us to reduce the set we need to remember to just {AND OR NOT} (or some such) which beautifully parallels the way we communicate in everyday life.)
Where have I said any such thing ?
Then I ... what ? Then you do what needs to be done as specified by the business in the case the queried piece of information is unknown.