3 ms·

I don't find it that confusing, to be honest. When the article presented the "mysterious" query, I guessed the result correctly. A correlated subquery will alwa

I don't find it that confusing, to be honest. When the article presented the "mysterious" query, I guessed the result correctly. A correlated subquery will always execute the entire expression for each row of the outer query. Since the value of `sum(a)` is known in the context of the outer query, it doesn't really matter where you select it from in the inner query. Therefore, the following queries are all identical: SELECT sum(a) FROM aa; SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa; SELECT (SELECT sum(a) FROM generate_series(1, 1)) FROM aa; To maybe explain why this behaviour makes sense, stop thinking about aggregate queries for a second, and just consider normal expressions. What would you expect the result of the following query to be? SELECT (SELECT a + x FROM xx LIMIT 1) FROM aa; This is obviously a bad query because it uses `LIMIT` without `ORDER BY`, but let's just put that aside. This query should make some sense to pretty much any SQL developer: you take each `a` value from `aa`, and then add the value from the "first" (whatever that means) `x` value from `xx` to it. (In my test on Postgres, this returns [11, 12, 13], but there's obviously no guarantee because we neglected to sort `xx`.) Now, since that was easy, consider this simplification of that query: SELECT (SELECT a FROM xx LIMIT 1) FROM aa; I think that if the previous query made sense, this one should also make sense, as the expression simple has one less term. The result is obviously [1, 2, 3]. So, what should the aggregate return: SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa; The only real difference is that the outer query is now aggregated, and therefore it is implicitly in one big grouping, and therefore returns only one row. So the correlated subquery gets run once, for that one row - what's the value for `sum(a)` in that row, the one row that the outer query returns? Well, 6. Duh.

foldU 3y ago
(I'm the author of the original post [1]) And while I think the rules, in the end, make sense, I think it's not quite as clear cut as you describe, consider: SELECT (SELECT sum(1) FROM xx LIMIT 1) FROM aa; which returns 3 3 3 Personally, I think having the inner aggregation always attach to the nearest `SELECT` would have been an equally valid way of defining how this works, but it just so happens it is not defined that way. [1]: https://buttondown.email/jaffray/archive/sql-scoping-is-surprisingly-subtle-and-semantic/ https://buttondown.email/jaffray/archive/sql-scoping-is-surp...
- indigo945 3y ago
  Well, the "fun" one to consider is IMO not that, but this: SELECT (SELECT sum(a + x) FROM xx LIMIT 1) FROM aa; Making a pretty diagram where all the behaviours from various DBMSs are listed is left as an exercise to the author. :)
  - foldU 3y ago
    I think this one is obvious actually! There's no choice but to aggregate it at the level of the `xx` `SELECT`, no other level has access to the `x` column.
  - MarkusWinand 3y ago
    > Making a pretty diagram where all the behaviours from various DBMSs are listed is left as an exercise to the author. :) It's there already! It's in the chart as footnote "b": Outer reference must be the only argument (doesn’t support F441) That's the one thing SQL Server doesn't eat. Those that are green in the chart work fine in this case.
tsimionescu 3y ago
You say it's obvious, but it apparently wasn't obvious to the developers of Oracle DB, one of the most used databases. 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).
- jhoechtl 3y ago
  > 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.
  - tsimionescu 3y ago
    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-popularity-ranking-database-management-systems/ 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.
da_chicken 3y ago
> I don't find it that confusing, to be honest. I would say it's not all that complicated what is going on. I would say that you can figure it out if you have experience reading queries. I would never say it's not confusing. Just because the query engine can parse and generate an execution plan and you can understand how and why it's doing what it decides to do doesn't mean it's not confusing. "Confusing" doesn't mean "incomprehensible." It is confusing. It's confusing in almost the same way that "6÷2(1+2) = ?" viral equation is confusing [0] or the Fast Inverse Square Root is confusing. This query is unconventional. Not "unconventional" in the sense that it's uncommon (although it is also that). It's unconventional in the sense that it goes against established conventions. Humans don't write queries that way. This query violates the collective query conventions of the SQL community. People don't write queries like this or teach writing queries like this because it's not a particularly useful or portable pattern to put the aggregate value of the outside query in the SELECT clause of an inside correlated subquery. There are better ways to write this query. Because of that, it's not clear what the author's intent was or why they would choose to write it thus instead of, well, nearly any other way that a human would actually choose beforehand. It's so unconventional that the intent is uncertain, and "uncertainty of intent" is another way to say "confusing." Code is not valuable because it executes. Code is valuable because the programmer, the computer, and any future programmer agree on the intent with no uncertainty. That's why over the long term clarity is often more potent than cleverness. [0]: https://youtu.be/4x-BcYCiKCk https://youtu.be/4x-BcYCiKCk

foldU 3y ago

(I'm the author of the original post [1]) And while I think the rules, in the end, make sense, I think it's not quite as clear cut as you describe, consider: SELECT (SELECT sum(1) FROM xx LIMIT 1) FROM aa; which returns 3 3 3 Personally, I think having the inner aggregation always attach to the nearest `SELECT` would have been an equally valid way of defining how this works, but it just so happens it is not defined that way. [1]: https://buttondown.email/jaffray/archive/sql-scoping-is-surprisingly-subtle-and-semantic/ https://buttondown.email/jaffray/archive/sql-scoping-is-surp...

indigo945 3y ago
Well, the "fun" one to consider is IMO not that, but this: SELECT (SELECT sum(a + x) FROM xx LIMIT 1) FROM aa; Making a pretty diagram where all the behaviours from various DBMSs are listed is left as an exercise to the author. :)
- foldU 3y ago
  I think this one is obvious actually! There's no choice but to aggregate it at the level of the `xx` `SELECT`, no other level has access to the `x` column.
- MarkusWinand 3y ago
  > Making a pretty diagram where all the behaviours from various DBMSs are listed is left as an exercise to the author. :) It's there already! It's in the chart as footnote "b": Outer reference must be the only argument (doesn’t support F441) That's the one thing SQL Server doesn't eat. Those that are green in the chart work fine in this case.

tsimionescu 3y ago

You say it's obvious, but it apparently wasn't obvious to the developers of Oracle DB, one of the most used databases. 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).

jhoechtl 3y ago
> 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.
- tsimionescu 3y ago
  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-popularity-ranking-database-management-systems/ 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.

da_chicken 3y ago

> I don't find it that confusing, to be honest. I would say it's not all that complicated what is going on. I would say that you can figure it out if you have experience reading queries. I would never say it's not confusing. Just because the query engine can parse and generate an execution plan and you can understand how and why it's doing what it decides to do doesn't mean it's not confusing. "Confusing" doesn't mean "incomprehensible." It is confusing. It's confusing in almost the same way that "6÷2(1+2) = ?" viral equation is confusing [0] or the Fast Inverse Square Root is confusing. This query is unconventional. Not "unconventional" in the sense that it's uncommon (although it is also that). It's unconventional in the sense that it goes against established conventions. Humans don't write queries that way. This query violates the collective query conventions of the SQL community. People don't write queries like this or teach writing queries like this because it's not a particularly useful or portable pattern to put the aggregate value of the outside query in the SELECT clause of an inside correlated subquery. There are better ways to write this query. Because of that, it's not clear what the author's intent was or why they would choose to write it thus instead of, well, nearly any other way that a human would actually choose beforehand. It's so unconventional that the intent is uncertain, and "uncertainty of intent" is another way to say "confusing." Code is not valuable because it executes. Code is valuable because the programmer, the computer, and any future programmer agree on the intent with no uncertainty. That's why over the long term clarity is often more potent than cleverness. [0]: https://youtu.be/4x-BcYCiKCk https://youtu.be/4x-BcYCiKCk