Nirth: "It takes away what little artistic merit games have, it ruins the immersion of a game"
You must lack the skill to choose the right mods which may not be strange at all in case you usually ignore them. When was the last time you tried one?
TaiPhoon: The lack of immersion part comes from the fact you realize that part of the experience is not as intended as their a fan has tampered with it. As for the artistic merit part, image if the mona liza was panted over, the painted over part would be the mod. The fact that someone else can come by and paint over it and call that "the definitive version" or "it's incomplete with out it" fucks with any kind of claim to art. (compatibility mods and emulators excluded).
http://en.wikipedia.org/wiki/L.H.O.O.Q.
Your mona lisa example is a bad one. A mod or a derivative work can have its own meaning separate from the original one.
Also, there are from mods to mods, mods that add, mods that detract, mods that ACTUALLY MAKE THINGS playable, such as the bugfixes for Saints Row 2 and Gentlemen of the Row. Those in no way detract from it, and in cases, they actually make something playable.
Also, I've never heard on any community that HL2 needs a mod to be good. Ever. Hell I've seen people bashing A SHITLOAD of them for various reasons, but never seen one touted as a "must get".
On the other hand you mentioned Troika games. Vampire? Yeah, that one definitely needs the mod since it was literally broken. 10 years of fan work and it works. Temple of Elemental Evil? It adds to it. Do not know if it was broken beforehand, but Circle of Eight makes it a pleasant experience. Arcanum? Yeah. That too needs a mod to be properly enjoyed. The main difference is that those mods were meant to restore/fix.
Hell, KOTOR2's restoration mod. That one was rushed to hell, but the mod tries to make it closer to the original devs' vision.
What about dev released mods? J.E. Sawyer released an optional mod for New Vegas that apparently makes it a bit closer to his original vision, and David Gaider's Throne of Bhaal: Ascension mod expanding on things he did not have time to implement before release. Bah.