5 ms·

First the authors show that for certain use cases, a single query is not ideal. Either the result set will be larger than it needs to be, or it will have to use

First the authors show that for certain use cases, a single query is not ideal. Either the result set will be larger than it needs to be, or it will have to use something like ARRAY_AGG which "discard[s] all schema and relational information on the way." So let's assume that we are in a situation where a single query is not desirable. Then the authors show ways to use multiple queries (in section 3, "SQL-BASED REWRITE METHODS"). Each method has its drawbacks. If you've done enough SQL, these patterns will look familiar to you. Compare the syntax needed here vs the proposed "SELECT RESULTDB" syntax in Listing 3 and you should see that their proposal is easier to write. Then in section 4, the authors "present an algorithm that can be integrated into a DBMS to efficiently compute the result set of our SELECT RESULTDB queries." Presumably this would be more efficient than any of the alternatives from section 3.

mamcx 3y ago
This in fact shows one of the artificial limitations of SQL (and not of the relational model): It does not have a `GROUP BY` functionality! What `sql` calls `GROUP BY` is `SUMMARIZE`.
- fipar 3y ago
  Yes, this is exactly what this shows. I think the authors' work is interesting but they shouldn't have said 'relational database' in the paper, just 'sql database' instead ("We keep an SQL database storing information about professors ..." and so on). In "industry", I don't argue with people saying Oracle/MySQL/PostgreSQL are relational databases because that would make me an insufferable colleague and would hardly add any value to the discussion, but for an academic paper on databases, I would prefer more accurate language.
  - proamdev123 3y ago
    How is a “SQL database” different from a “relational database”? I’m in industry, so I’ve only ever heard these databases described as relational databases. I’d love to understand more.
    - deleted 3y ago
      [deleted]
    - fipar 3y ago
      I'm in industry too, though I did go through formal studies at university and databases was one of the topics that interested me the most back then (late 90s). I dropped out without graduating so I'm definitely not in academia, but I do keep up with some of what's going on though my ACM membership, and it is within that context that I made my comment about the authors' choice of words in their paper. With that said, some examples of differences between an SQL database and a relational one (which, to be honest, AFAIK, is something that doesn't exist in a production-ready available implementation): - In a relational database everything is a relation (including the result of applying any operator). Within the Relational Model, this is called the Information Principle, and among other characteristics, the header and tuples in a relation are a set and therefore have no order. SQL is not set oriented, evidenced by the fact that columns and rows do have an order (to the point that most, if not all SQL products let you specify the place in a table's definition in which you want to insert a new column, something that makes no sense whatsoever for a relation). - In line with the previous item, SQL allows duplicates, which relations don't have (because sets don't have duplicates). - According to some (notably, Codd disagreed with this), NULLs and three-valued logic are not part of the relational model, while SQL obviously supports NULLs. - In a relational database, values are stored in relation variables, and these change by being replaced completely with the relational assignment operator. Say you have a relation users, and you want to remove fipar from it, the relational way to do that would be to say users := users minus (users where username = fipar) (pardon the crude pseudocode, hopefully the intent is clear). This means there's no way for the update to be done partially. SQL databases used transactionally comply with this, but some let you relax ACID properties for performance, and when that is done, the universe of possible results for operations includes outcomes that would not happen with relational assignment. The list is much bigger, and there are lots of edge cases. For a proper treatment from someone who knows what they're talking about I'd recommend this book for a thorough answer to your question: https://www.oreilly.com/library/view/sql-and-relational/9781449319724/ https://www.oreilly.com/library/view/sql-and-relational/9781... Not a difference, but since this is a common misconception, the "relational" in a relational database is not about relationships between tables. If we simplify by saying that a relation in a relational database is analogue to a table in an SQL database, you can have a relational database with a single relation (i.e., a single table). The relation is between the header and the tuples (rows in SQL). The idea is that the headers form a statement about the world modeled by the database, and every tuple is a combination of values for which the statement is true. In light of this, it should be obvious why duplicates make no sense in a relation. Saying something twice doesn't make it more true! Another misconception is that relational databases don't scale. That makes no sense, because the relational model has nothing to say about the implementation. Saying the model doesn't scale because you can't use Foreign Keys after certain data size and throughput threshold are crossed in MySQL (say) makes as much sense as saying that arithmetic doesn't scale because you hit an overflow while using a specific model of calculator.
- proamdev123 3y ago
  Can you expand on this? What is the difference between `GROUP BY` and `SUMMARIZE`? I’m familiar with SQL, but not the artificial limitations that you’re referring to, and I’d like to better understand.
  - michaelmior 3y ago
    I'm guessing they're referring to the fact that typically when you use `GROUP BY` in SQL (aside from aggregate functions such as `ARRAY_AGG`), you're typically not returning a "group" of results, but a summary of the results in the group.
    - mamcx 3y ago
      Exactly.
crazygringo 3y ago
> First the authors show that for certain use cases, a single query is not ideal. Reading it closely, this is what I already disagree with. You do a good job of summarizing their two main arguments, so allow me to rebut: > Either the result set will be larger than it needs to be Yes, they're using the example of repeating information (denormalization), such as multiple courses taught by the same professor. But it's genuinely hard to see this as a drawback -- that's a feature. Data should be stored as normalized as possible, but queries are supposed to denormalize to present information in the desired format. (And if you're dealing with what would be an overly-large amount of repeated values, you just run multiple queries yourself instead of one. And if round-trip latency is some kind of issue with running queries sequentially, you can always issue queries in parallel instead.) > which "discard[s] all schema and relational information on the way." Again, this is a feature. You're not supposed to retrieve all possible relational information in query results. You write your query to retrieve and differentiate precisely what you need and no more. Discarding irrelevant information is a feature, not a bug. More than that -- you want your query to define and adhere to its output format regardless of the underlying database structure, precisely so you can refactor things in the database and rewrite the query but not need to rewrite the code that uses the query results. I guess my overall bafflement is that the things they describe as "not ideal" seem to me like features rather than problems, and these features have been highly beneficial in my practical experience of writing a lot of database-driven apps.
- jrumbut 3y ago
  > Data should be stored as normalized as possible, but queries are supposed to denormalize to present information in the desired format. The desired format depends on the application. At this point a substantial portion of all SQL queries are generated by (and the results consumed by) ORMs. For that use case having results that include Products and Categories separately (so you can instantiate Product and Category objects) is more useful than a single table.
- hrdwdmrbl 3y ago
  > And if you're dealing with what would be an overly-large amount of repeated values, you just run multiple queries yourself instead of one. It is a bit annoying to do this though. It would be nice if it was done for me automatically be some db driver. > And if round-trip latency is some kind of issue with running queries sequentially, you can always issue queries in parallel instead. Some languages or frameworks don’t provide great parallel ization features though. I guess at the end of the day if it’s an extension to SQL that you can optionally use, then I would have some situations where I would use it.

mamcx 3y ago

This in fact shows one of the artificial limitations of SQL (and not of the relational model): It does not have a `GROUP BY` functionality! What `sql` calls `GROUP BY` is `SUMMARIZE`.

fipar 3y ago
Yes, this is exactly what this shows. I think the authors' work is interesting but they shouldn't have said 'relational database' in the paper, just 'sql database' instead ("We keep an SQL database storing information about professors ..." and so on). In "industry", I don't argue with people saying Oracle/MySQL/PostgreSQL are relational databases because that would make me an insufferable colleague and would hardly add any value to the discussion, but for an academic paper on databases, I would prefer more accurate language.
- proamdev123 3y ago
  How is a “SQL database” different from a “relational database”? I’m in industry, so I’ve only ever heard these databases described as relational databases. I’d love to understand more.
  - deleted 3y ago
    [deleted]
  - fipar 3y ago
    I'm in industry too, though I did go through formal studies at university and databases was one of the topics that interested me the most back then (late 90s). I dropped out without graduating so I'm definitely not in academia, but I do keep up with some of what's going on though my ACM membership, and it is within that context that I made my comment about the authors' choice of words in their paper. With that said, some examples of differences between an SQL database and a relational one (which, to be honest, AFAIK, is something that doesn't exist in a production-ready available implementation): - In a relational database everything is a relation (including the result of applying any operator). Within the Relational Model, this is called the Information Principle, and among other characteristics, the header and tuples in a relation are a set and therefore have no order. SQL is not set oriented, evidenced by the fact that columns and rows do have an order (to the point that most, if not all SQL products let you specify the place in a table's definition in which you want to insert a new column, something that makes no sense whatsoever for a relation). - In line with the previous item, SQL allows duplicates, which relations don't have (because sets don't have duplicates). - According to some (notably, Codd disagreed with this), NULLs and three-valued logic are not part of the relational model, while SQL obviously supports NULLs. - In a relational database, values are stored in relation variables, and these change by being replaced completely with the relational assignment operator. Say you have a relation users, and you want to remove fipar from it, the relational way to do that would be to say users := users minus (users where username = fipar) (pardon the crude pseudocode, hopefully the intent is clear). This means there's no way for the update to be done partially. SQL databases used transactionally comply with this, but some let you relax ACID properties for performance, and when that is done, the universe of possible results for operations includes outcomes that would not happen with relational assignment. The list is much bigger, and there are lots of edge cases. For a proper treatment from someone who knows what they're talking about I'd recommend this book for a thorough answer to your question: https://www.oreilly.com/library/view/sql-and-relational/9781449319724/ https://www.oreilly.com/library/view/sql-and-relational/9781... Not a difference, but since this is a common misconception, the "relational" in a relational database is not about relationships between tables. If we simplify by saying that a relation in a relational database is analogue to a table in an SQL database, you can have a relational database with a single relation (i.e., a single table). The relation is between the header and the tuples (rows in SQL). The idea is that the headers form a statement about the world modeled by the database, and every tuple is a combination of values for which the statement is true. In light of this, it should be obvious why duplicates make no sense in a relation. Saying something twice doesn't make it more true! Another misconception is that relational databases don't scale. That makes no sense, because the relational model has nothing to say about the implementation. Saying the model doesn't scale because you can't use Foreign Keys after certain data size and throughput threshold are crossed in MySQL (say) makes as much sense as saying that arithmetic doesn't scale because you hit an overflow while using a specific model of calculator.
proamdev123 3y ago
Can you expand on this? What is the difference between `GROUP BY` and `SUMMARIZE`? I’m familiar with SQL, but not the artificial limitations that you’re referring to, and I’d like to better understand.
- michaelmior 3y ago
  I'm guessing they're referring to the fact that typically when you use `GROUP BY` in SQL (aside from aggregate functions such as `ARRAY_AGG`), you're typically not returning a "group" of results, but a summary of the results in the group.
  - mamcx 3y ago
    Exactly.

crazygringo 3y ago

> First the authors show that for certain use cases, a single query is not ideal. Reading it closely, this is what I already disagree with. You do a good job of summarizing their two main arguments, so allow me to rebut: > Either the result set will be larger than it needs to be Yes, they're using the example of repeating information (denormalization), such as multiple courses taught by the same professor. But it's genuinely hard to see this as a drawback -- that's a feature. Data should be stored as normalized as possible, but queries are supposed to denormalize to present information in the desired format. (And if you're dealing with what would be an overly-large amount of repeated values, you just run multiple queries yourself instead of one. And if round-trip latency is some kind of issue with running queries sequentially, you can always issue queries in parallel instead.) > which "discard[s] all schema and relational information on the way." Again, this is a feature. You're not supposed to retrieve all possible relational information in query results. You write your query to retrieve and differentiate precisely what you need and no more. Discarding irrelevant information is a feature, not a bug. More than that -- you want your query to define and adhere to its output format regardless of the underlying database structure, precisely so you can refactor things in the database and rewrite the query but not need to rewrite the code that uses the query results. I guess my overall bafflement is that the things they describe as "not ideal" seem to me like features rather than problems, and these features have been highly beneficial in my practical experience of writing a lot of database-driven apps.

jrumbut 3y ago
> Data should be stored as normalized as possible, but queries are supposed to denormalize to present information in the desired format. The desired format depends on the application. At this point a substantial portion of all SQL queries are generated by (and the results consumed by) ORMs. For that use case having results that include Products and Categories separately (so you can instantiate Product and Category objects) is more useful than a single table.
hrdwdmrbl 3y ago
> And if you're dealing with what would be an overly-large amount of repeated values, you just run multiple queries yourself instead of one. It is a bit annoying to do this though. It would be nice if it was done for me automatically be some db driver. > And if round-trip latency is some kind of issue with running queries sequentially, you can always issue queries in parallel instead. Some languages or frameworks don’t provide great parallel ization features though. I guess at the end of the day if it’s an extension to SQL that you can optionally use, then I would have some situations where I would use it.