However, what if you didn’t do an auto upgrade and manually upgraded the site? Wouldn’t the problem surface anyways? Better to be aware of it sooner rather than later?

That's exactly my point. If I do a manual upgrade and it breaks, I'll know right away. If WP upgrades itself overnight, I won't know until morning. And then I won't even be certain it was from the upgrade. What if my hosting provider also broke something within the same time interval? Simultaneous issues happen a LOT, and they make it very hard to debug as you chase the entirely wrong lead for hours or days.