5 ms·
I'm struggling to understand the purpose of this. If I understand it correctly, for a query with joins, then instead of returning a single combined set of resu
by crazygringo 3y ago
I'm struggling to understand the purpose of this.
If I understand it correctly, for a query with joins, then instead of returning a single combined set of results with the information you need, it returns multiple sets of results based on each separate table.
But I don't understand why that would ever be desirable.
I always write my queries to return the information I need, per whatever "key" I need, in each row. Splitting that up in multiple sets of results is not helpful.
Or if I really need multiple sets of data, I write multiple queries.
So I really don't get what this is for. It's like it takes one of the biggest features of databases -- joins -- and removes it? Why?
- sargstuff 3y ago(humor) if system not set up appropriately, expanded long form version of shell fork bomb[0] that drains bank account per pay per amount of resources used. [0] https://www.cyberciti.biz/faq/understanding-bash-fork-bomb/ https://www.cyberciti.biz/faq/understanding-bash-fork-bomb/
- irrational 3y agoJokes on them. My bank account is already empty.
- sargstuff 3y agoNot if switch to using boolean logic. It's at max capacity then.
- cmrdporcupine 3y agoReading the abstract... it feels to me like the author doesn't know what a relational database is. Chris Date does discuss the concept of nested tuples / relation-valued tuples of some kind in his books as an extension to the relational model. But that's something else entirely.
- sargstuff 3y agoMethod(s) for turning dbms into a replacement for OS might be applicable here.
- randomdata 3y agoMy interpretation is to enable a query to return many relations in order to overcome the high latency associated with most RDBMBes that often prevent using relational databases in a "theoretically pure" way. It's a hack, but possibly a better hack than the hacks we currently resort to.
- cmrdporcupine 3y agoThere's already a way to return tuples from multiple relations from a single query. Multiple ways in fact. Both unions and joins.
- randomdata 3y agoThis is to have multiple relations returned. Traditional unions and joins only see one relation returned. You can pack multiple relations into one relation using unions/joins and then unpack it again in the client to ultimately achieve the same effect, which is a workaround that some utilize, but that is really hacky (to be clear, unions and joins are not hacks when used for their intended purpose, but the data packing is). The proposed would be much less hacky - although still far from an ideal database that has no latency constraints that force deviation from what is "pure". But that’s engineering for you.
- oleg_antonyan 3y agoIt could be useful with ORM. For example, in Rails ActiveRecord instantiates the class for each row. But when you need to write complex query with many joins and instantiate AR models tree from it becomes either cumbersome or leads to n+1 queries. Still pretty niche usecase, but looks interesting
- julian37 3y agoRight, but fetching rows along with a bunch of related rows from other tables isn't niche, it's literally the _raison d'etre_ of GraphQL (simplifying a bit because GraphQL types don't necessarily map onto db tables.) It's something lots of apps need all the time.
- tadfisher 3y agoStatic-language clients need to create a class (or collection of classes) for each GraphQL query, or forgo type safety (typically just null-safety, but I suppose you could treat each result as a dictionary type). Creating just the schema-level types, with relationships modeled separately, does throw out some of the benefits ("just the data you need") but simplifies the client, moreso if the client may perform multiple complex queries on the same data.
- ako 3y agoYou can do that in Postgres, either return nested json structures, or nested arrays. For an example see: https://shusson.info/post/building-nested-json-objects-with-postgres https://shusson.info/post/building-nested-json-objects-with-...
- keithnz 3y agoso, if you want to join two things, a parent and children, you get duplication of parent details eg, select P.name, C.* from Parent P inner join Child C on C.Parent_id = P.Id where P.Id = 1234 so from what I can tell is this idea would have a result set of a Parent table and child tables based on the join and projection with no duplication? At least that's what I understand it to be. That seems useful.
- ianburrell 3y agoAlso, it is possible to manipulate the joined data. What does this do with a SUM or MAX? Or calculation that involves both tables?
- default-kramer 3y agoFirst 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 agoThis 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 agoYes, 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 agoHow 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.
- jtriangle 3y agoSometimes you just want the data to do with as you please and don't want to bother dealing with relational database overhead (and instead prefer to deal with that overhead yourself). Not really a new idea, just new to SQL databases. Usually if you find yourself needing to dump a whole ton of data, you don't use SQL, you use any number of other database solutions that are better suited to that model. So I suppose this is really meant to give you a little bit of that for when you need it.
- shrimpx 3y agoI guess it's kind of like doing all the things a join query would do, like mapping rows together, filtering, sorting, and grouping, but the final output is denormalized into several "tables" without duplication. From what I gather, the main "win" is the size reduction in the result set, which can impact network and memory usage. Yes you can do this yourself by running multiple queries, one on each table. Presumably by paying the latency of multiple round trips.
- nhinck2 3y agoIf I wanted to get some patients who had either condition x y or z and likely they had more one of those conditions, this would be a useful way to return the data. The list of patients remains distinct but which of the conditions they had would still be referencable. It's a nice shortcut but not world changing.
- solumunus 3y agoThe purpose is performance. Let's say you're querying orders, and each order has a customer name in another table that you want to pull through. If you do a join and include the customer name in the result set it's going to amount to a lot of repeated data that has to be sent across the network. Instead, you could receive two result sets, one with only the relevant customers and the other with the orders containing only the customer ID. The amount of data getting sent across the network will be massively reduced and you can still reference the customer names at the other end.
- amne 3y agoThis sounds like something the DB driver should be able to do and hide it, at least by default, from the user.
- peterlada 3y agoHave you tried reading the paper or at least the twitter thread? There are 8 problems listed there that will help you understand the probablement and the elegance of this proposed solution via the SQL standard.
- sumtechguy 3y agoI may be misremembering this but I think ODBC does allow for more than one table to be returned at a time? But it has been nearly 30 years since I messed with that API. I think it has to do with stored procedures and that they sometimes can return more than one table. Maybe someone with more recent exp in it can chime in.
- _a_a_a_ 3y agoNot sure if ODBC supported it, but Microsoft added at some point MARS - multiple active results sets. I don't know anything about them though.
- koromak 3y agoI can imagine a use case. For complicated queries, you end up performing a Cronenberg on your data in order to smash it all together with large join chains, or even creating new json columns that effectively return other "tables". If its performance critical, this is often the best option. For most queries, a single table makes perfect sense. But in the real world things aren't usually so neat.