Comment by foldU | Hacker News Reader

Comment by foldU | Hacker News Reader

Parent Full thread

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

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

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.

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

Reply on news.ycombinator.com