The downward sensors do work, whether over water or over a smooth surface. It's just important to understand HOW they work.The problem arises when there is a sudden change in barometric pressure and the drone can suddenly descend or ascend by a couple of meters. Now normally over land the downward sensors will kick in and avoid a mishap. However over a smooth surface like water we all know that they do not work.
A perfectly smooth surface of water only means the downward facing camera sensor might not be able to fix on a particular feature for accurately holding position when in a hover. But if you're flying over a lake, presumably the drone will have an unobstructed view of GPS satellites, so won't need the downward vision sensor for position holding.
In any case, that sensor is only doing anything useful while the drone is in a hover. It cannot in any way drive the drone into the water while it is in forward flight.
Where the position sensor might get confused is if there are waves or ripples moving across the surface of the water. While in a hover and fixating on a moving surface feature, the drone may try to follow that moving feature. Again, that cannot cause the drone to sink into the water as the vision sensor has nothing to do with holding altitude.
The barometric sensor is the primary sensor used for altitude-hold, and it is sensitive to changes in atmospheric pressure. Your takeoff altitude is initially recorded as zero, but will often register something completely different when you eventually return to land at the same spot.
And yes, air temperature and pressure does change during the day, with land usually warming and cooling faster than large bodies of water. When I sailed on Lake Ontario winds might be dead calm early in the morning. But as the land heated up, warm air rose from the farm fields drawing cooler air onshore from the lake. Later in the day, as the water surface eventually warmed up, the wind would die down completely. Even later, as the land cooled off more quickly, warmer air rising off the lake would pull the cooler air from the land blowing offshore. But that process is very gradual.
I guess it's not impossible, but any sudden temperature/pressure gradient, sufficiently affecting your barometric sensor to drive your drone into the water, is also certain to create a significant sudden wind shear.
In any case, it's not the downward facing sensors that's causing that issue. Whether the surface is perfectly smooth, or rippled and wavy, the infrared height sensors (when in range) bounce a signal off the surface. If the drone senses it is getting too low, too close to the surface, it will automatically increase height. It won't cause the drone to sink into the lake.
That infrared sensor is not effective at high rates of forward speed because the forward pitched angle of the drone's body essentially means the sensor is looking at ground that's already passed behind it. So if you're flying forward at high speed low over water, you risk getting slapped by a wave.
I've seen people theorize that the infrared sensor might be seeing through the surface of clear water and registering its height as measured from the bottom of the lake, rather than from the surface of the water. So if the barometric sensor is somehow causing the drone to sink, the theory is that infrared sensor thinks it's still plenty high enough off the bottom of the lake and doesn't see the surface of the water. Even in that case, it's not the infrared sensor that's pulling the drone down.
I suspect that's nonsense anyway. Infrared light won't penetrate a pane of window glass, so I'm pretty sure it doesn't go through water either.