4 ms·

For the benefit of those looking to understand the premise of the question, The author’s complaints as to why SQL-based DBMSs are not ‘truly relational’ are enu

For the benefit of those looking to understand the premise of the question, The author’s complaints as to why SQL-based DBMSs are not ‘truly relational’ are enumerated as follows: > Why did they neglect the obvious need for each of a table’s columns to have a name? Why did they allow a table to have two or more columns with the same name? Why did they decide to allow more than one copy of the same row to appear in a table? Why did they invent NULL and introduce that third truth value, UNKNOWN? Where by ‘table’ he means ‘result of a query’. So the complaint amounts to SQLs failure to fully ‘close’ such that the result of any query would automatically be an indexed relation exactly equivalent in capability to a table. Which I guess is something every SQL user has run into from time to time, and is a source of annoyance, but doesn’t seem to have prevented people from actually using SQL to… manage… relational data. Also, much of this paper seems to be taken up with complaining that Codd didn’t think through all the details someone would need to iron out when actually implementing relational calculus, which seems like an odd place to lay the complaint. Codd demonstrated that a system based on relations was, in certain ways, complete and comprehensive (ie it could let you answer ‘any question’ about some data); that it didn’t also serve as an unambiguous spec for how to design a query language and engine seems to ask a bit much of the initial theoretical underpinnings of the entire field. Like complaining that Turing’s paper on universal computation doesn’t actually nail down how to design a CPU.

TrispusAttucks 4y ago
I'm pretty sure you can load a query result into an [1] in memory table and query that including generated indexes. [1] https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engin...
msla 4y ago
> Why did they invent NULL and introduce that third truth value, UNKNOWN? I fundamentally don't understand how you can have all of the kinds of JOINs SQL has without a NULL.
- maxdemarzi 4y ago
  You can by using “narrow” tables (key value and key key). Download the slide notes from this presentation of how Relational.ai is doing it: https://www.slideshare.net/maxdemarzi/developer-intro-deckpowerpoint https://www.slideshare.net/maxdemarzi/developer-intro-deckpo...
  - msla 4y ago
    It seems like narrow tables solve having NULLs in the tables you store, but they do nothing about NULLs in the tables you create using, say, a LEFT JOIN. Like, if you create a database with Name, Postnomials, and Prenomials, some people don't have Postnomials or Prenomials, so even if you create three narrow tables, when you JOIN them all to form the full polite addresses, you'll end up with NULLs in the result of that JOIN.
    - maxdemarzi 4y ago
      It works a little different in “Rel” (the query language Relational.ai uses). You would create multiple definitions of what a “full polite address” is for each “case” of valid arguments/empty columns and use that going forward. A bit like a UNION without the same column width requirements.
      - Pxtl 4y ago
        That seems clumsy. I would think the right approach to "SQL without LEFT JOIN" would be just to focus on making pulling down multiple related tables as distinct resultsets in a single query easier and have the client code work with a graph instead of hammering everything into a single tabular layout. Or leave the concept of "connect these two tables together and make them NULL where not applicable" as an exercise for the client.
        maxdemarzi 4y ago
        Quite the opposite. The idea is to move as much of the business logic into the database. “Rel” definitions are meant to be written once and reused everywhere. Instead of letting the client decide different business logic every time, you capture and control it in one place.
masukomi 4y ago
thank you so much. Was starting to read through it and not understanding what the core problem was.
jerf 4y ago
"So the complaint amounts to SQLs failure to fully ‘close’ such that the result of any query would automatically be an indexed relation exactly equivalent in capability to a table. Which I guess is something every SQL user has run into from time to time, and is a source of annoyance, but doesn’t seem to have prevented people from actually using SQL to… manage… relational data." Load up the debate in your mind about the importance of generics and how important Optional is and how important sum types are, regardless of your personal feelings about them. Now. Once upon a time, people wrote programs with nothing but global variables and GOTO statements. They didn't even have subroutines. They wrote code nevertheless. It was just bad code, for a much greater effort than they would need to write similar code today. The fact that people solve problems doesn't prove they're doing it optimally and that there can't be any improvement. The good news is, this problem continues to get better and better. I've been doing some Postgres work lately, and the good news is that, unlike when I tried this several years ago, you pretty much can now switch freely between something being a table, or an array of arrays, or a select statement, etc. The bad news, is every single path way was added ad-hoc, so they all have different names and even different naming schemes ("unnest" to turn arrays into a table, ARRAY to turn a table into an array), aren't coherently linked together, and the documentation is still fuzzy on the types of these things so it takes some experimentation to work out the correct "spelling" of all these conversions. Optimization behavior is even more opaque. I'm sure that will also get better over time. But it's a work in progress, not a completed job.
- kragen 4y ago
  in the relational model the construct that corresponds to optional is a second table that is missing some rows for example, every foo here has a bar, but not every foo has a baz foo bar 1 2 3 4 5 6 foo baz 1 7 5 8 you can think of this as baz being an optional attribute of whatever entities the foo values identify. if the second relation is not unique on foo (maybe its primary key is both columns!) you could think of it as being a set-valued relationship, associating any number of bazzes with each foo rather than just 0 or 1 from darwen and date's perspective, having null is an error-prone construct analogous to global variables and goto statements as a historical note, subroutines predate goto statements by about ten years; subroutines predate not only high-level languages but even symbolic assembler. it is true that many people have written programs with global variables and goto statements and no subroutines, but that wasn't because subroutines hadn't been invented yet, or even publicized; wilkes explained how to use subroutines in his 01951 book "The Preparation of Programs for an Electronic Digital Computer" https://archive.org/details/programsforelect00wilk https://archive.org/details/programsforelect00wilk but they were presumably invented somewhat earlier, possibly by david wheeler the whole array construct in postgres seems like it was probably a huge mistake
hgsgm 4y ago
I'm glad to see Chris Date in the paper. Chris Date has been railing about no RDBMS being an RDBMS for decades. They aren't "complaining" about Codd, anymore than Einstein "complained" about Newton. They are making valid academic criticisms, discussing solutions, and lamenting that in the past 50 years those solutions have not been implemented in practice.
- Pxtl 4y ago
  SQL is the perfect example of "worse is better". SQL was an amazing invention for its time, but it has not grown and developed the way other languages have. There is basically no industry velocity in upgrading SQL the way there was with procedural languages. SQL and C were both invented in the '70s. While C may not have grown as much as SQL has, new major languages that handle similar tasks to C have come into their own, willing to throw out old syntax or ideas where needed. Now, one could argue that SQLite is the Javascript of SQLs, and that T-Sql is the C# of SQL, and that PL/SQL is the COBOL of Sql, and that Postgres is the Java of SQL... but in all cases those C-derived languages were far more willing to experiment and break out of the narrow bounds set by their antecedents than SQL variants were. Obviously there are small and interesting experiments in the DB space, but none of those are the kind of industry-standardized improvements to the formula we see in the space of C-derived languages.
- jameshart 4y ago
  It does read kind of like a complaint to me. The question the paper poses is ‘why’ are there no relational DBMS’s. And it places a bunch of the blame, it seems, at the fact that Codd left some things ambiguous and the people behind SQL resolved that ambiguity in a way that weakened the ‘relationality’ of the result. But you’re right, it’s possible to read this more as ‘blameless post-mortem’ five-whying. Why aren’t there any RDBMSs? - Because SQL won, and SQL is not relational Why isn’t SQL relational? - because it’s creators misunderstood Codd’s paper in these ways Why was Codd’s paper open to misinterpretation? - because Codd didn’t think of these particular complexities… Which I guess is one way to follow the ‘why’ thread. But it does slightly ignore the important ‘why did SQL win?’ Branch…
  - 4y ago

TrispusAttucks 4y ago

I'm pretty sure you can load a query result into an [1] in memory table and query that including generated indexes. [1] https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engin...

msla 4y ago

> Why did they invent NULL and introduce that third truth value, UNKNOWN? I fundamentally don't understand how you can have all of the kinds of JOINs SQL has without a NULL.

maxdemarzi 4y ago
You can by using “narrow” tables (key value and key key). Download the slide notes from this presentation of how Relational.ai is doing it: https://www.slideshare.net/maxdemarzi/developer-intro-deckpowerpoint https://www.slideshare.net/maxdemarzi/developer-intro-deckpo...
- msla 4y ago
  It seems like narrow tables solve having NULLs in the tables you store, but they do nothing about NULLs in the tables you create using, say, a LEFT JOIN. Like, if you create a database with Name, Postnomials, and Prenomials, some people don't have Postnomials or Prenomials, so even if you create three narrow tables, when you JOIN them all to form the full polite addresses, you'll end up with NULLs in the result of that JOIN.
  - maxdemarzi 4y ago
    It works a little different in “Rel” (the query language Relational.ai uses). You would create multiple definitions of what a “full polite address” is for each “case” of valid arguments/empty columns and use that going forward. A bit like a UNION without the same column width requirements.
    - Pxtl 4y ago
      That seems clumsy. I would think the right approach to "SQL without LEFT JOIN" would be just to focus on making pulling down multiple related tables as distinct resultsets in a single query easier and have the client code work with a graph instead of hammering everything into a single tabular layout. Or leave the concept of "connect these two tables together and make them NULL where not applicable" as an exercise for the client.
      - maxdemarzi 4y ago
        Quite the opposite. The idea is to move as much of the business logic into the database. “Rel” definitions are meant to be written once and reused everywhere. Instead of letting the client decide different business logic every time, you capture and control it in one place.

masukomi 4y ago

thank you so much. Was starting to read through it and not understanding what the core problem was.

jerf 4y ago

"So the complaint amounts to SQLs failure to fully ‘close’ such that the result of any query would automatically be an indexed relation exactly equivalent in capability to a table. Which I guess is something every SQL user has run into from time to time, and is a source of annoyance, but doesn’t seem to have prevented people from actually using SQL to… manage… relational data." Load up the debate in your mind about the importance of generics and how important Optional is and how important sum types are, regardless of your personal feelings about them. Now. Once upon a time, people wrote programs with nothing but global variables and GOTO statements. They didn't even have subroutines. They wrote code nevertheless. It was just bad code, for a much greater effort than they would need to write similar code today. The fact that people solve problems doesn't prove they're doing it optimally and that there can't be any improvement. The good news is, this problem continues to get better and better. I've been doing some Postgres work lately, and the good news is that, unlike when I tried this several years ago, you pretty much can now switch freely between something being a table, or an array of arrays, or a select statement, etc. The bad news, is every single path way was added ad-hoc, so they all have different names and even different naming schemes ("unnest" to turn arrays into a table, ARRAY to turn a table into an array), aren't coherently linked together, and the documentation is still fuzzy on the types of these things so it takes some experimentation to work out the correct "spelling" of all these conversions. Optimization behavior is even more opaque. I'm sure that will also get better over time. But it's a work in progress, not a completed job.

kragen 4y ago
in the relational model the construct that corresponds to optional is a second table that is missing some rows for example, every foo here has a bar, but not every foo has a baz foo bar 1 2 3 4 5 6 foo baz 1 7 5 8 you can think of this as baz being an optional attribute of whatever entities the foo values identify. if the second relation is not unique on foo (maybe its primary key is both columns!) you could think of it as being a set-valued relationship, associating any number of bazzes with each foo rather than just 0 or 1 from darwen and date's perspective, having null is an error-prone construct analogous to global variables and goto statements as a historical note, subroutines predate goto statements by about ten years; subroutines predate not only high-level languages but even symbolic assembler. it is true that many people have written programs with global variables and goto statements and no subroutines, but that wasn't because subroutines hadn't been invented yet, or even publicized; wilkes explained how to use subroutines in his 01951 book "The Preparation of Programs for an Electronic Digital Computer" https://archive.org/details/programsforelect00wilk https://archive.org/details/programsforelect00wilk but they were presumably invented somewhat earlier, possibly by david wheeler the whole array construct in postgres seems like it was probably a huge mistake

hgsgm 4y ago

I'm glad to see Chris Date in the paper. Chris Date has been railing about no RDBMS being an RDBMS for decades. They aren't "complaining" about Codd, anymore than Einstein "complained" about Newton. They are making valid academic criticisms, discussing solutions, and lamenting that in the past 50 years those solutions have not been implemented in practice.

Pxtl 4y ago
SQL is the perfect example of "worse is better". SQL was an amazing invention for its time, but it has not grown and developed the way other languages have. There is basically no industry velocity in upgrading SQL the way there was with procedural languages. SQL and C were both invented in the '70s. While C may not have grown as much as SQL has, new major languages that handle similar tasks to C have come into their own, willing to throw out old syntax or ideas where needed. Now, one could argue that SQLite is the Javascript of SQLs, and that T-Sql is the C# of SQL, and that PL/SQL is the COBOL of Sql, and that Postgres is the Java of SQL... but in all cases those C-derived languages were far more willing to experiment and break out of the narrow bounds set by their antecedents than SQL variants were. Obviously there are small and interesting experiments in the DB space, but none of those are the kind of industry-standardized improvements to the formula we see in the space of C-derived languages.
jameshart 4y ago
It does read kind of like a complaint to me. The question the paper poses is ‘why’ are there no relational DBMS’s. And it places a bunch of the blame, it seems, at the fact that Codd left some things ambiguous and the people behind SQL resolved that ambiguity in a way that weakened the ‘relationality’ of the result. But you’re right, it’s possible to read this more as ‘blameless post-mortem’ five-whying. Why aren’t there any RDBMSs? - Because SQL won, and SQL is not relational Why isn’t SQL relational? - because it’s creators misunderstood Codd’s paper in these ways Why was Codd’s paper open to misinterpretation? - because Codd didn’t think of these particular complexities… Which I guess is one way to follow the ‘why’ thread. But it does slightly ignore the important ‘why did SQL win?’ Branch…
- 4y ago