7 ms·
(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:
by 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 agoWell, 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 agoI 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.