I've ran some tests on how the PROMOTION_SEE_INVISIBLE_SUBMARINE works:
CiV 1.0.0.621 (Default XML values)
View of the WorldBuilder Scenario
Conclusions
Any naval unit can see Submarines if they are in any adjacent hex.
Any land unit can see Submarines if they are in any adjacent hex.
Notice that when the land unit is not adjacent, the sub cannot be seen (also applied to naval units that don't have PROMOTION_SEE_INVISIBLE_SUBMARINE)
Units with PROMOTION_SEE_INVISIBLE_SUBMARINE can see subs up to their maximum sight radius (default for Destroyers is 5; Subs 2)
Suggestion #1
Remove PROMOTION_SEE_INVISIBLE_SUBMARINE from all units other than submarines. They will be able to detect subs out to their sight radius (default 2). All other units (land and naval) will need to be in an adjacent hex.
Suggestion #2
Remove all sight promotions from the Destroyer (I.e - PROMOTION_EXTRA_SIGHT_III). The destroyer will now detect subs at 2 hexes or less (controlled by PROMOTION_SEE_INVISIBLE_SUBMARINE). If you want them to see subs farther, add an entry in CIV5Units.XML under BaseSightRange and set it to whatever number you want them to see. Also, the sight promotion on the Caravel would need to be lost with upgrade.
Suggestion #3
Give the Subs PROMOTION_EXTRA_SIGHT_I allowing the subs to see 3 hexes and attack 3 hexes. Use this with suggestion #2 and Subs can see subs out to three hexes, Destroyers can see them at 2 hexes, all other units must be adjacent to the Sub to see it.
Once the Sub is seen, it remains seen until it moves again. This is why cities can see Subs when the subs are next to them (anything adjacent to a sub can see it)
At a minimum:
The PROMOTION_EXTRA_SIGHT_III should be removed from the destroyer.
PROMOTION_EXTRA_SIGHT_II (Caravel) should be lost with upgrade.
Adjacent Units should not be able to see the Sub if they don't have PROMOTION_SEE_INVISIBLE_SUBMARINE (especially non-DOMAIN_NAVAL units)
Units without PROMOTION_SEE_INVISIBLE_SUBMARINE should only be able to detect the sub when they try to enter its hex.
PROMOTION_SEE_INVISIBLE_SUBMARINE should allow exploration of rival territory without an open border agreement.
CiV 1.0.0.621 (Default XML values)
View of the WorldBuilder Scenario
Spoiler :
Conclusions
Any naval unit can see Submarines if they are in any adjacent hex.
Spoiler :
Any land unit can see Submarines if they are in any adjacent hex.
Spoiler :
Notice that when the land unit is not adjacent, the sub cannot be seen (also applied to naval units that don't have PROMOTION_SEE_INVISIBLE_SUBMARINE)
Spoiler :
Units with PROMOTION_SEE_INVISIBLE_SUBMARINE can see subs up to their maximum sight radius (default for Destroyers is 5; Subs 2)
Spoiler :
Suggestion #1
Remove PROMOTION_SEE_INVISIBLE_SUBMARINE from all units other than submarines. They will be able to detect subs out to their sight radius (default 2). All other units (land and naval) will need to be in an adjacent hex.
Suggestion #2
Remove all sight promotions from the Destroyer (I.e - PROMOTION_EXTRA_SIGHT_III). The destroyer will now detect subs at 2 hexes or less (controlled by PROMOTION_SEE_INVISIBLE_SUBMARINE). If you want them to see subs farther, add an entry in CIV5Units.XML under BaseSightRange and set it to whatever number you want them to see. Also, the sight promotion on the Caravel would need to be lost with upgrade.
Suggestion #3
Give the Subs PROMOTION_EXTRA_SIGHT_I allowing the subs to see 3 hexes and attack 3 hexes. Use this with suggestion #2 and Subs can see subs out to three hexes, Destroyers can see them at 2 hexes, all other units must be adjacent to the Sub to see it.
Once the Sub is seen, it remains seen until it moves again. This is why cities can see Subs when the subs are next to them (anything adjacent to a sub can see it)
At a minimum:
The PROMOTION_EXTRA_SIGHT_III should be removed from the destroyer.
PROMOTION_EXTRA_SIGHT_II (Caravel) should be lost with upgrade.
Adjacent Units should not be able to see the Sub if they don't have PROMOTION_SEE_INVISIBLE_SUBMARINE (especially non-DOMAIN_NAVAL units)
Units without PROMOTION_SEE_INVISIBLE_SUBMARINE should only be able to detect the sub when they try to enter its hex.
PROMOTION_SEE_INVISIBLE_SUBMARINE should allow exploration of rival territory without an open border agreement.