Expressivenes of SQL vs TTM
The SQL data model has a number of oddities, such as NULLs, duplicate rows and columns, that the TTM relational model does not allow, but even so the SQL DQL can be used to write wide range of queries on a 'pure' TTM RM. My contention is that broadly speaking within this space SQL DQL is more expressive than TTM or any implementation of a TTM/D.
These are the features:
- outer joins
- ordered queries
- fixed point recursion
- generalised aggregation
- tables with no columns.
Support is as follows:
- SQL has 1-3, not 4 (but often more than just the basics), not 5
- Most TTM/D implementations have TCLOSE, limited aggregation, not 1-2.
- TTM defines generalised TCLOSE and 4 but most do not implement
- Dave/Rel has a proposal for 1 (without nulls), is opposed to 2 and 3
- Andl has 2 (mostly), 3 (while) and 4, not 1.
On my reading the TTM-sphere has lagged well behind SQL in terms of the expressiveness of its DQL. Is that intentional, or the passage of time?
Quote from dandl on July 19, 2021, 1:04 amOn my reading the TTM-sphere has lagged well behind SQL in terms of the expressiveness of its DQL. Is that intentional, or the passage of time?
I see the difference as very simple. TTM doesn't describe a complete language and expects implementers to fill in the gaps however they see fit, while the SQL standard does describe a complete language.
Quote from dandl on July 19, 2021, 1:04 amThe SQL data model has a number of oddities, such as NULLs, duplicate rows and columns, that the TTM relational model does not allow, but even so the SQL DQL can be used to write wide range of queries on a 'pure' TTM RM. My contention is that broadly speaking within this space SQL DQL is more expressive than TTM or any implementation of a TTM/D.
These are the features:
- outer joins
- ordered queries
- fixed point recursion
- generalised aggregation
- tables with no columns.
Support is as follows:
- SQL has 1-3, not 4 (but often more than just the basics), not 5
- Most TTM/D implementations have TCLOSE, limited aggregation, not 1-2.
- TTM defines generalised TCLOSE and 4 but most do not implement
- Dave/Rel has a proposal for 1 (without nulls), is opposed to 2 and 3
- Andl has 2 (mostly), 3 (while) and 4, not 1.
On my reading the TTM-sphere has lagged well behind SQL in terms of the expressiveness of its DQL. Is that intentional, or the passage of time?
It's a bit of an apples-vs-oranges comparison, TTM vs SQL, because D prescribes a general-purpose programming language with integrated relational capabilities, so it covers SQL capability and more but in a general-purpose-language way. That's quite different from the SQL way.
- I do indeed have a proposal for #1. It's implemented in Rel. (See https://github.com/DaveVoorhis/Rel/blob/master/_Deployment/doc/CHANGES.txt lines 85 - 96.)
- Also implemented in Rel, but it's a non-relational language feature -- ORDER emits an array of tuples in a specified order.
- Recursion is via conventional recursive function definitions (which may accept and return relations); no query-specific mechanism required.
- Implemented in Rel. See https://reldb.org/c/index.php/read/user-defined-aggregate-operators/
- Tutorial D has TABLE_DUM and TABLE_DEE.
Some SQL implementations (or, indeed, the SQL standard) may indeed be more expressive than Tutorial D "by the book", but Tutorial D is intended for pedagogy, not production. As a specification of abstraction and prescription of broad characteristics, TTM isn't comparable to SQL. Only a specific Industrial D can really be compared to (some dialect of) SQL.
Comparing capability of some SQL implementation to some Industrial D is left as an exercise for the reader.
Quote from Dave Voorhis on July 19, 2021, 9:19 amQuote from dandl on July 19, 2021, 1:04 amThe SQL data model has a number of oddities, such as NULLs, duplicate rows and columns, that the TTM relational model does not allow, but even so the SQL DQL can be used to write wide range of queries on a 'pure' TTM RM. My contention is that broadly speaking within this space SQL DQL is more expressive than TTM or any implementation of a TTM/D.
These are the features:
- outer joins
- ordered queries
- fixed point recursion
- generalised aggregation
- tables with no columns.
Support is as follows:
- SQL has 1-3, not 4 (but often more than just the basics), not 5
- Most TTM/D implementations have TCLOSE, limited aggregation, not 1-2.
- TTM defines generalised TCLOSE and 4 but most do not implement
- Dave/Rel has a proposal for 1 (without nulls), is opposed to 2 and 3
- Andl has 2 (mostly), 3 (while) and 4, not 1.
On my reading the TTM-sphere has lagged well behind SQL in terms of the expressiveness of its DQL. Is that intentional, or the passage of time?
It's a bit of an apples-vs-oranges comparison, TTM vs SQL, because D prescribes a general-purpose programming language with integrated relational capabilities, so it covers SQL capability and more but in a general-purpose-language way. That's quite different from the SQL way.
Mine was not. Codd 1972 clearly distinguishes between the data sub-langage (DQL) and the host programming language (SQL or D). Although TTM blurs the boundaries, it implicitly defines a DQL consisting of the 'usual' RA (RM Pre 18), plus generalised aggregation (OO Pre 6) and TCLOSE (RM VSS 5). Interestingly, it does not define extend but both App-A and TD have this and limited TCLOSE.
My question is simply about why the TTM DQL is less expressive than the SQL DQL. Why did D&D not ensure that the TTM SQL was as expressive as SQL DQL, or at least justify their decision not to do so?
- I do indeed have a proposal for #1. It's implemented in Rel. (See https://github.com/DaveVoorhis/Rel/blob/master/_Deployment/doc/CHANGES.txt lines 85 - 96.)
- Also implemented in Rel, but it's a non-relational language feature -- ORDER emits an array of tuples in a specified order.
- Recursion is via conventional recursive function definitions (which may accept and return relations); no query-specific mechanism required.
- Implemented in Rel. See https://reldb.org/c/index.php/read/user-defined-aggregate-operators/
These are all additions to the D programming language, not the DQL. This is like saying in SQL: you can have ORDER BY but only if you write a stored procedure. Doesn't fly.
- Tutorial D has TABLE_DUM and TABLE_DEE.
Some SQL implementations (or, indeed, the SQL standard) may indeed be more expressive than Tutorial D "by the book", but Tutorial D is intended for pedagogy, not production. As a specification of abstraction and prescription of broad characteristics, TTM isn't comparable to SQL. Only a specific Industrial D can really be compared to (some dialect of) SQL.
Comparing capability of some SQL implementation to some Industrial D is left as an exercise for the reader.
Considering only the DQL, none of the implementations (except Andl) make any attempt to provide a DQL to match SQL. Isn't that the obvious target?
Quote from dandl on July 20, 2021, 12:43 amQuote from Dave Voorhis on July 19, 2021, 9:19 amQuote from dandl on July 19, 2021, 1:04 amThe SQL data model has a number of oddities, such as NULLs, duplicate rows and columns, that the TTM relational model does not allow, but even so the SQL DQL can be used to write wide range of queries on a 'pure' TTM RM. My contention is that broadly speaking within this space SQL DQL is more expressive than TTM or any implementation of a TTM/D.
These are the features:
- outer joins
- ordered queries
- fixed point recursion
- generalised aggregation
- tables with no columns.
Support is as follows:
- SQL has 1-3, not 4 (but often more than just the basics), not 5
- Most TTM/D implementations have TCLOSE, limited aggregation, not 1-2.
- TTM defines generalised TCLOSE and 4 but most do not implement
- Dave/Rel has a proposal for 1 (without nulls), is opposed to 2 and 3
- Andl has 2 (mostly), 3 (while) and 4, not 1.
On my reading the TTM-sphere has lagged well behind SQL in terms of the expressiveness of its DQL. Is that intentional, or the passage of time?
It's a bit of an apples-vs-oranges comparison, TTM vs SQL, because D prescribes a general-purpose programming language with integrated relational capabilities, so it covers SQL capability and more but in a general-purpose-language way. That's quite different from the SQL way.
Mine was not. Codd 1972 clearly distinguishes between the data sub-langage (DQL) and the host programming language (SQL or D). Although TTM blurs the boundaries, it implicitly defines a DQL consisting of the 'usual' RA (RM Pre 18), plus generalised aggregation (OO Pre 6) and TCLOSE (RM VSS 5). Interestingly, it does not define extend but both App-A and TD have this and limited TCLOSE.
My question is simply about why the TTM DQL is less expressive than the SQL DQL. Why did D&D not ensure that the TTM SQL was as expressive as SQL DQL, or at least justify their decision not to do so?
- I do indeed have a proposal for #1. It's implemented in Rel. (See https://github.com/DaveVoorhis/Rel/blob/master/_Deployment/doc/CHANGES.txt lines 85 - 96.)
- Also implemented in Rel, but it's a non-relational language feature -- ORDER emits an array of tuples in a specified order.
- Recursion is via conventional recursive function definitions (which may accept and return relations); no query-specific mechanism required.
- Implemented in Rel. See https://reldb.org/c/index.php/read/user-defined-aggregate-operators/
These are all additions to the D programming language, not the DQL. This is like saying in SQL: you can have ORDER BY but only if you write a stored procedure. Doesn't fly.
- Tutorial D has TABLE_DUM and TABLE_DEE.
Some SQL implementations (or, indeed, the SQL standard) may indeed be more expressive than Tutorial D "by the book", but Tutorial D is intended for pedagogy, not production. As a specification of abstraction and prescription of broad characteristics, TTM isn't comparable to SQL. Only a specific Industrial D can really be compared to (some dialect of) SQL.
Comparing capability of some SQL implementation to some Industrial D is left as an exercise for the reader.
Considering only the DQL, none of the implementations (except Andl) make any attempt to provide a DQL to match SQL. Isn't that the obvious target?
Maybe 20 years ago?
I don't know that SQL is a reasonable target today, except in specialist niches. Targeting SQL with a new general-purpose database query language to underpin your day-to-day business applications and dynamic Web sites, no, probably not.
Of course, TTM as a set of proscriptions and prescriptions doesn't preclude -- as far as I can see, at a glance -- adding as much capability to some "base" D as you like, to match and exceed SQL in capability if that's your goal. Such a thing seems well within what a D is meant to be and indeed the TTM prescriptions and proscriptions appear to set a reasonable minimum standard, but not an upper limit. Note that it doesn't specify precisely how you meet or exceed some level of database language capability, so if SQL handles recursion like this and (say) Tutorial D / Rel handles it like that, I don't see that as being a problem. Indeed, Tutorial D is illustration of precisely that.
And note that Tutorial D has no real notion of a "DQL" as such, nor should it. It's a general-purpose programming language (or at least pedagogically alludes to one -- a hypothetical Industrial D) not a (mere) query (sub)language. Per DTATRM page 97 (of the PDF):
... As already noted, Tutorial D is computationally complete, meaning that entire applications
can be written in the language; it is not just a “data sublanguage” that relies on some host language
to provide the necessary computational capabilities. ...
SIRA_PRISE has a form of outer join, though obviously not the SQL kind with NULL.
SIRA_PRISE has a facility to ask the server to transmit a result relation in ordered form. As Dave already pointed out, anything beyond ("ordered relations" e.g.) that is non-relational and therefore non-core, non-fundamental.
SIRA_PRISE has generalized TCLOSE.
SIRA_PRISE has generalized aggregation in the form of a relational operator to be found nowhere else.
SIRA_PRISE has full support for nilary relations.
I'll add a point 6 :
6. Quota queries
SIRA_PRISE does not have support for quota queries except in the form in which it can also be engineered in SQL but at any rate, such a feature is exactly as non-fundamental and non-core as point 2. was, for the very same reason.
Quote from Erwin on July 20, 2021, 1:42 pmSIRA_PRISE has a form of outer join, though obviously not the SQL kind with NULL.
SIRA_PRISE has a facility to ask the server to transmit a result relation in ordered form. As Dave already pointed out, anything beyond ("ordered relations" e.g.) that is non-relational and therefore non-core, non-fundamental.
SIRA_PRISE has generalized TCLOSE.
SIRA_PRISE has generalized aggregation in the form of a relational operator to be found nowhere else.
SIRA_PRISE has full support for nilary relations.
I'll add a point 6 :
6. Quota queries
SIRA_PRISE does not have support for quota queries except in the form in which it can also be engineered in SQL but at any rate, such a feature is exactly as non-fundamental and non-core as point 2. was, for the very same reason.
Thank you for pointing this out. I wasn't aware it reached this level. You will be able to provide a solution to the parts explosion problem set in DTATRM, which other implementations do not.
There are many kinds of ordered queries, all of which conform to the relational model and none of which have anything to do with generating output in ordered form. Codd 1972 assumed the existence of ordered comparisons between attribute values, and that's all you need. Examples include:
- The usual MAX and MIN aggregation functions
- Quota queries
- Lead, lag and delta between tuples
- Moving average and other similar calculations on time-ordered data
- SQL Windowing
You didn't mention fixed point recursion, as per SQL CTE.
So SIRA_PRISE, extensive as it is, still falls short of SQL as a DQL.
Considering only the DQL, none of the implementations (except Andl) make any attempt to provide a DQL to match SQL. Isn't that the obvious target?
Maybe 20 years ago?
I don't know that SQL is a reasonable target today, except in specialist niches. Targeting SQL with a new general-purpose database query language to underpin your day-to-day business applications and dynamic Web sites, no, probably not.
More like 30. SQL has been the technology of choice for apps, largely due to Microsoft and ODBC, sine about 1990-1995. IBM implemented the CLI which is similar, but even that lost out to ODBC. All the competitors quietly faded away. By this time Java was around and MS was still focussed on Basic, but even so in the early days of TTM there was no real appetite for a language of this kind for general client-side application development.
During the late 1990s there was an opportunity for a TTM/D to take on the role of server-side programming (stored procedures) but there is too much missing (especially the DML).
There are plenty of opportunities now for the ideas in TTM, but not for a language to replace Java/C#/C++/Python etc and not for accessing a shared database. Once you get into single-user embedded database the competition narrows right down. I'll give it some thought and post something.
Quote from dandl on July 21, 2021, 2:23 amConsidering only the DQL, none of the implementations (except Andl) make any attempt to provide a DQL to match SQL. Isn't that the obvious target?
Maybe 20 years ago?
I don't know that SQL is a reasonable target today, except in specialist niches. Targeting SQL with a new general-purpose database query language to underpin your day-to-day business applications and dynamic Web sites, no, probably not.
More like 30. SQL has been the technology of choice for apps, largely due to Microsoft and ODBC, sine about 1990-1995. IBM implemented the CLI which is similar, but even that lost out to ODBC. All the competitors quietly faded away. By this time Java was around and MS was still focussed on Basic, but even so in the early days of TTM there was no real appetite for a language of this kind for general client-side application development.
Yeah, 20 years ago was when the window was still open a crack, and Alphora D4 almost got through it (and has since, I think, sputtered to a halt.) Then it didn't and the crack slammed shut.
Yes, 30 years ago there would have been a better chance.
During the late 1990s there was an opportunity for a TTM/D to take on the role of server-side programming (stored procedures) but there is too much missing (especially the DML).
There are plenty of opportunities now for the ideas in TTM, but not for a language to replace Java/C#/C++/Python etc and not for accessing a shared database. Once you get into single-user embedded database the competition narrows right down. I'll give it some thought and post something.
SQLite is kind of eating the world, there. With good reason, too. As SQL goes, it's nice and there are some good admin/viewing tools around it.
I think the opportunity for TTM ideas lies in niche areas. Maybe very niche. As I've mentioned before, there are specialist implementations of the relational model (mostly based around Datalog, as far as I can tell) that are succeeding (for an unspecified value of "succeeding") in vertical markets where (from what I've seen) the number of possible customers can be counted on a hand or two, but they're willing to pay a lot to achieve capabilities that SQL and its usual ancillary languages and tools simply don't offer at all.