The Curious Case of the Aggregation Query

Re:The Curious Case of the Aggregation Query by foldU on Tuesday December 05, 2023 @11:35AM (#38533140)

(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-surp...

[ Parent | on HN ]

Re:The Curious Case of the Aggregation Query by indigo945 on Tuesday December 05, 2023 @11:39AM (#38533187)

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. :)

[ Parent | on HN ]

Re:The Curious Case of the Aggregation Query by foldU on Tuesday December 05, 2023 @11:40AM (#38533214)

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.

[ Parent | on HN ]

Re:The Curious Case of the Aggregation Query by MarkusWinand on Tuesday December 05, 2023 @11:44AM (#38533276)

> 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.

[ Parent | on HN ]