Perhaps the water in the Mushroom world is far more polar than our water, allowing it to hold up incredibly large objects. In vapor form, the water's polarity allows Mario to sort of hover, but while in liquid form, the polarity breaks the surface tension, preventing Mario from floating? That's as logical and scientific as I can come up with.