Actually, the reason Aero can prevent tearing without having to force VSYNC on in an application is because it is a compositing window manager. It draws the desktop and all windows asynchronously with respect to any running application.
That is to say, when you enable compositing in Windows Vista / 7 (it is always enabled in Windows 8), it will draw all of the windows using a copy of the last image that was swapped from back to front. The compositor (Microsoft calls it DWM, Desktop Window Manager) composites everything with VSYNC enabled, and because it draws using a copy of the last fully-swapped front buffer image for each window it never displays partially drawn frames. It does, however, have an unfortunate side effect of preventing really old software that was designed to draw into the front buffer exclusively from working correctly - but modern software does not do this.
In a nutshell, the DWM adds an additional layer of protection against tearing. One that will allow any application running on the system to draw at a rate irrespective of the display's refresh rate and still prevent tearing. On such a system, in windowed mode the only thing enabling VSYNC in OpenGL or Direct3D is good for is actually throttling back CPU/GPU utilization.
This is why adaptive VSYNC was created, the idea there is not to penalize applications that cannot sustain the display's refresh rate by forcing them into a lower factor of the refresh rate (e.g. 60 Hz --> 30, 20, 15, 10, 12, 6, 5, 4, 3, 2, 1) but to limit applications that are drawing faster than the monitor can display images from using an excessive amount of CPU / GPU power.