"Everything must be a mode of the major scale" is a very rigid and narrow model of harmony, and it doesn't work when exposed to actual music.
Let's relax the concept of mode beyond "modes of the major scale", by making the following additional definitions. I'll call it Real Mode to distinguish it from the narrower idea that's better suited for simple exercises on theory classes.
- a Real Mode can be any set of intervals around a tonic note. It doesn't have to be found by rotating the major scale.
- Real Modes can be partially mixed to create new combinations or mixtures so that e.g. the intervals for degrees 1-5 come from one Real Mode and degrees 6-7 come from another Real Mode
- a Real Mode doesn't have to be completely defined, i.e. you don't have to know all the degrees for sure
- a Real Mode can be transitional, i.e. the feeling and set of intervals doesn't have to stay the same for the whole duration of a piece, it may last only for a brief moment
With these refinements we can say that the Brain Damage song is doing some modal mixing. It is making changes to the intervals around the tonic, the changes don't last very long and the scale degrees at each moment aren't completely explicated, so it leaves room for imagination.
If we further relax the concept of "mode" so that we don't have to have exactly seven notes per octave, then we have a practically applicable model of harmony that can be used for looking at actual music.
But the above is more like a theoretical description of what and how the song writers did. What comes to harmonic resources, the origins and inspiration of the music, I'd say that the list of ingredients probably has some blues. Nobody broke any laws there, just conventions and expectations.
For part 1 of your question, I'll interpret the question as "why does making changes to the harmony sound fascinating". I don't think I've ever heard any real explanation for this, that's just the way it is. When something about the harmony stays in place and other things move, it makes you follow what's happening and re-think the situation. Music is about finding a balance between the expected and the unexpected. Familiar, but not too familiar.