Re:The Curious Case of the Aggregation Query
by jhoechtl on Tuesday December 05, 2023 @04:49PM
(#38537408)
> Oracle DB, one of the most used databases.
Is it still? Maybe 20 years ago but it's been a long time that I met an It guy who talked about himself having worked with an Oracle DB recently.
It's all PostgreSQL, MS SQL and MySQL/MariaDB where I work.
Re:The Curious Case of the Aggregation Query
by tsimionescu on Wednesday December 06, 2023 @02:59AM
(#38541493)
Here is an article on Statista claiming it is in fact the most used by quite some margin:
https://www.statista.com/statistics/809750/worldwide-popular...
I have no idea if that is even close to correct, but I am sure it is still one of the most used RDBMS out there, out of pure inertia if nothing else. Plus, from what I've heard from a friend who worked with a few commercial. DBs, it's actually pretty good software, it's dealing with Oracle licensing and outrageous audit practices that is killing it.
I think the most unexpected thing is that the query engine looks into the inner queries to decide whether to return all rows of the outer table or some aggregation of them. If there had been a group by clause, it would have been much clearer that an aggregation must be happening.
Edit: also, the "obvious" logic actually changes completely if you look at the related query, SELECT (SELECT sum(a+x) FROM xx LIMIT 1) FROM aa;
This returns 63, 66, 69. Similarly, sum(1) will return 3, 3, 3.
So, we could very well expect that sum (a) would work like that - it would treat each value of `a` as a constant that it aggregates over all rows of xx, so it would return 3, 6, 9 (the limit is anyway ignored if you are aggregating).