4 ms·

There's a lot wrong with SQL. Chris Date [1] wrote several books comparing SQL to a more pure implementation relational algebra, Tutorial D. In Date's words: >

by sa46 5y ago

There's a lot wrong with SQL. Chris Date [1] wrote several books comparing SQL to a more pure implementation relational algebra, Tutorial D. In Date's words: > SQL is incapable of providing the kind of firm foundation we need for future growth and development. Instead, it’s the relational model that has to provide that foundation. [...] We see SQL as a kind of database COBOL, and we would like to see some other language become available as a better alternative to it. Summarizing Date's points: - Real relations don't contain duplicate tuples but SQL tables allow duplicate rows. - Tuples and therefore relations don't ever contain nulls. Personally, I have trouble understanding how to support this limitation given LEFT JOIN. - Domain types only provide type aliases not a new type. For example, you can compare or join on different domain types if the base type is the same. Date uses the example of `p.weight = sp.qty` to show a comparison on domain types that shouldn't be allowed. - SQL has many ways to express the same query. His example shows at least 12 ways to answer "Get part numbers for parts that either are screws or are supplied by supplier S1, or both." - SELECT DISTINCT should have been the default instead of SELECT ALL. My personal gripes: - SQL is quite chatty, so much so that you can omit words in some incantations. - It's hard to dynamically build queries. It composes poorly for anything dynamic, even simple things like ordering by a column name. [1]: https://en.wikipedia.org/wiki/Christopher_J._Date https://en.wikipedia.org/wiki/Christopher_J._Date

BulgarianIdiot 5y ago
It seems a lot of those things you list as "wrong" come from lack of understanding of the reasons behind these choices, and born out of pure idealism in vacuum. For example, allowing duplicate rows is irrelevant, because if you have a primary key, there are no duplicate rows. SQL doesn't require primary keys because relational algebra has no such concept as a "primary key". There are just keys. However without defining a primary key, enforcing unique rows would mean the database silently indexing the ENTIRE ROW'S CONTENTS, including potentially blobs and large text fields. This would obviously be nonsense. Likewise, having SELECT DISTINCT be a default would mean a very expensive processing step in your query processing being a default. An expensive step that in fact doesn't matter, because the vast majority of queries don't produce duplicate results in practice. DISTINCT is optional because the need for it is exceptional, and its cost is high. Even you don't buy the "have no NULL" argument. So I don't have to defend this. Real-world data is not perfectly "rectangular". Optional attributes are a thing. So having a primitive for it makes sense. Once again, SQL allows you to define a field as non-nullable, so complaining about it being there if you EXPLICITLY WANT IT is silly. Regarding having many ways to express the same query: that's true for all languages. This is one of the biggest issues in optimizing compilers, canonicalizing expressions so patterns can be recognized. There's no way to do it at the source, so complaining SQL also does it, is like shouting at clouds. Regarding the type system, type systems can always be better, but let's not forget type systems (typically) exist to eliminate mistakes, not to enable new features. The kind of mistake where you compare "weight and quantity" is not likely. More subtle errors are possible, like comparing metric and imperial measures, but since SQL is often used in tandem with a system's language (Java, C++) or a script, all this domain logic is offloaded to them and their type system. Even if SQL had a detailed type system, therefore, most people wouldn't bother duplicating their detailed type definitions from Java to SQL or back. The only remaining issue is SQL is chatty. Which is quite ironic given the procedural code for what SQL does would be several times the size of the SQL query. SQL is a high-level language, and it being explicit is fine. And few extra letters here and there for a keyword don't make or break a language. I prefer chatty over cryptic.
- mulmen 5y ago
  Totally agree with your post but wanted to add on to this part: > More subtle errors are possible, like comparing metric and imperial measures, but since SQL is often used in tandem with a system's language (Java, C++) or a script, all this domain logic is offloaded to them and their type system. PostgreSQL actually supports user-defined types[1]! You could do something like define [2] a “kilogram” type and a “pound” type and then summing or joining on the column is safe. That could still get sticky with grams and kilograms. Also, pounds are defined in terms of kilograms. So you could also define a “weight” type. This can have semantics like DATE[3] or BOOLEAN[4] so inserting “1kg”, “1kilogram”, “1000g” or “2.204623lb” all store the same value. You can then use (define) format functions like TO_CHAR(weight, string) to display weights in grams, lb or whatever you need. Of course, the other argument here is that all you really need are the primitive types and normalization. A “weight” table can just have a “unit”::string column. You can do conversions with another table such as “from_unit”::string, “to_unit”::string and “multiple”::float. Your “product_weight” table would then just have foreign key relationships to the weight and conversions tables. [1]: https://www.postgresql.org/docs/current/xtypes.html https://www.postgresql.org/docs/current/xtypes.html [2]: https://github.com/df7cb/postgresql-unit https://github.com/df7cb/postgresql-unit [3]: https://www.postgresql.org/docs/13/datatype-datetime.html#DATATYPE-DATETIME-INPUT https://www.postgresql.org/docs/13/datatype-datetime.html#DA... [4]: https://www.postgresql.org/docs/13/datatype-boolean.html https://www.postgresql.org/docs/13/datatype-boolean.html
goto11 5y ago
Some of Dates gripes are valid, but some are quite questionable. In SQL you enforce uniqueness of rows by defining a primary key. Dates concern seem to be that you can have duplicate rows if no primary key is defined, but why would you do that in the first place? A table should always have a primary key. His gripe against nulls are controversial - E.F.Codd suggested nulls himself, so when Date claims null have no place in the relational model he is just stating a personal opinion. And while nulls are kind of weird, his suggested alternative is far worse. His complain about comparing different types is valid IMHO. SQL is weakly typed and types are silently converted. I think it would be be much better if it was strongly typed and values has to be explicitly converted when e.g. comparing a string to a number. The issue about custom types is a consequence of SQL being weakly typed.
- BulgarianIdiot 5y ago
  What's his alternative to null in a nutshell?
  - goto11 5y ago
    Sentinel values of the same type, e.g. -1 to indicate a missing integer.
    - BulgarianIdiot 5y ago
      Ouch, yeah that's bad.

by sa46 5y ago

BulgarianIdiot 5y ago

It seems a lot of those things you list as "wrong" come from lack of understanding of the reasons behind these choices, and born out of pure idealism in vacuum. For example, allowing duplicate rows is irrelevant, because if you have a primary key, there are no duplicate rows. SQL doesn't require primary keys because relational algebra has no such concept as a "primary key". There are just keys. However without defining a primary key, enforcing unique rows would mean the database silently indexing the ENTIRE ROW'S CONTENTS, including potentially blobs and large text fields. This would obviously be nonsense. Likewise, having SELECT DISTINCT be a default would mean a very expensive processing step in your query processing being a default. An expensive step that in fact doesn't matter, because the vast majority of queries don't produce duplicate results in practice. DISTINCT is optional because the need for it is exceptional, and its cost is high. Even you don't buy the "have no NULL" argument. So I don't have to defend this. Real-world data is not perfectly "rectangular". Optional attributes are a thing. So having a primitive for it makes sense. Once again, SQL allows you to define a field as non-nullable, so complaining about it being there if you EXPLICITLY WANT IT is silly. Regarding having many ways to express the same query: that's true for all languages. This is one of the biggest issues in optimizing compilers, canonicalizing expressions so patterns can be recognized. There's no way to do it at the source, so complaining SQL also does it, is like shouting at clouds. Regarding the type system, type systems can always be better, but let's not forget type systems (typically) exist to eliminate mistakes, not to enable new features. The kind of mistake where you compare "weight and quantity" is not likely. More subtle errors are possible, like comparing metric and imperial measures, but since SQL is often used in tandem with a system's language (Java, C++) or a script, all this domain logic is offloaded to them and their type system. Even if SQL had a detailed type system, therefore, most people wouldn't bother duplicating their detailed type definitions from Java to SQL or back. The only remaining issue is SQL is chatty. Which is quite ironic given the procedural code for what SQL does would be several times the size of the SQL query. SQL is a high-level language, and it being explicit is fine. And few extra letters here and there for a keyword don't make or break a language. I prefer chatty over cryptic.

mulmen 5y ago
Totally agree with your post but wanted to add on to this part: > More subtle errors are possible, like comparing metric and imperial measures, but since SQL is often used in tandem with a system's language (Java, C++) or a script, all this domain logic is offloaded to them and their type system. PostgreSQL actually supports user-defined types[1]! You could do something like define [2] a “kilogram” type and a “pound” type and then summing or joining on the column is safe. That could still get sticky with grams and kilograms. Also, pounds are defined in terms of kilograms. So you could also define a “weight” type. This can have semantics like DATE[3] or BOOLEAN[4] so inserting “1kg”, “1kilogram”, “1000g” or “2.204623lb” all store the same value. You can then use (define) format functions like TO_CHAR(weight, string) to display weights in grams, lb or whatever you need. Of course, the other argument here is that all you really need are the primitive types and normalization. A “weight” table can just have a “unit”::string column. You can do conversions with another table such as “from_unit”::string, “to_unit”::string and “multiple”::float. Your “product_weight” table would then just have foreign key relationships to the weight and conversions tables. [1]: https://www.postgresql.org/docs/current/xtypes.html https://www.postgresql.org/docs/current/xtypes.html [2]: https://github.com/df7cb/postgresql-unit https://github.com/df7cb/postgresql-unit [3]: https://www.postgresql.org/docs/13/datatype-datetime.html#DATATYPE-DATETIME-INPUT https://www.postgresql.org/docs/13/datatype-datetime.html#DA... [4]: https://www.postgresql.org/docs/13/datatype-boolean.html https://www.postgresql.org/docs/13/datatype-boolean.html

goto11 5y ago

Some of Dates gripes are valid, but some are quite questionable. In SQL you enforce uniqueness of rows by defining a primary key. Dates concern seem to be that you can have duplicate rows if no primary key is defined, but why would you do that in the first place? A table should always have a primary key. His gripe against nulls are controversial - E.F.Codd suggested nulls himself, so when Date claims null have no place in the relational model he is just stating a personal opinion. And while nulls are kind of weird, his suggested alternative is far worse. His complain about comparing different types is valid IMHO. SQL is weakly typed and types are silently converted. I think it would be be much better if it was strongly typed and values has to be explicitly converted when e.g. comparing a string to a number. The issue about custom types is a consequence of SQL being weakly typed.

BulgarianIdiot 5y ago
What's his alternative to null in a nutshell?
- goto11 5y ago
  Sentinel values of the same type, e.g. -1 to indicate a missing integer.
  - BulgarianIdiot 5y ago
    Ouch, yeah that's bad.