D'oh, that HOM just made me realise: the SKY is an important part of the effect, too! With a sky ceiling, the non-existent upper textures on the "below water" lines become transparent; without a sky, you get a HOM instead. How did I miss that?
Though, that said, you've missed a few key steps, too:
The part about the 1-sided lines is important, as you can't cross a 2-sided line if you're below the floor of the sector that line references. When I said the player doesn't physically interact with the "appearance" sectors, I meant "if you construct the lines properly". If there's an underwater cave that you want the player to be able to enter, there must not be an "above water" line in front of the cave.
Now, to connect the "real" sectors with the "appearance" sectors, 2-sided linedefs are traced in front of every 1-sided line of the "real" water sector (sector 0), connecting to the sector only at the vertices of the "real" cave sectors (use a map editor or IDDT and zoom in to see these lines and how they connect).
Without the "hole" sector, there isn't any place where the player can actually enter the water (and note that the "below water" 2-sided lines must go AROUND the hole, not inside it).
Finally, there is a square "hole" sector attached to the ledge (sector 1). All the lines of this sector except for the "ledge" line itself ARE self-referencing linedefs, referencing sector 0 (the "real" water sector). Since these lines are self-referencing, they do not affect the appearance of the effect in any way, and only serve to allow you to fall down into the underwater area, because otherwise the "underwater" sector would block you due to its low ceiling (do IDDT and run into the water while viewing the automap to see how you get blocked).
With those points in mind, it should be obvious that the water sector cannot be completely surrounded by another sector (in fact, I'm not even sure if it can be made to work properly if the water sector has any 2-sided lines that can be viewed from the above water "ledge" - note that the example map has both the cave and the ledge on the sound side of the room, so that neither can be seen from the other).