The ghouls, wastelanders and supermutants all die from drinking the water.
As do you, if you drink too much.
People in Megaton get sick from drinking it, then get given aqua pura, then get worse.
It's not immediate, or as big as it should be, but it's there.
You should do it, it's evil, but fun.
And the closest you can get to siding with the Enclave properly.