Bad production is just that - bad production...producing a result that is way below the standards set in the golden age of recordings. Too much compression, too much limiting, a much too narrow soundstage and plain bad mic'ing produce horrendous results.
I think the epitome of a great album ruined by bad production and engineering is Californication.
Ehh, I'd have to say that it's at least still listenable sometimes. Not the case with (What's The Story) Morning Glory? by Oasis. I can just about take Wonderwall, but Hello (the first track) is horrendous. And WTSMG isn't even pushed to 0 dB... The peaks of Hello are around -3 dB, but the majority (i.e. not including the intro/end) of the song uses all of about 2 dB of dynamic range... It's the worst album I've ever heard.
To the O.P. - You haven't heard bad sound in a Smashing Pumpkins album until you've heard Zeitgeist. It's not exactly a great album, but its mastering (the compression) is among the worst I've ever heard. Mellon Collie is tame in comparison.
NIN's The Slip is realy bad as regards compression as well, but again isn't exactly a "great" album.
All of The Mars Volta's albums (at least through The Bedlam in Goliath) suffer from extreme compression - Bedlam is the worst as it's the most full-out out of all of them.
Audioslave's albums (all of them) also are compressed; they're perhaps one of the most prominent (and talked about) examples after RHCP and Oasis. The song Your Time Has Come is a particularly good example.
None of these groups were short on money. It's a conscious decision to compress it, as others have mentioned as a part of the "Loudness War".
I find that only a short time listening to such albums causes major listening fatigue to me, so that even well mastered music afterwards is painful to listen to. The "always loud" characteristic of compressed music causes this - there's no breaks and no dynamics in the music. It still sounds loud even when you turn the volume down.
On the other hand, even relatively poorly recorded albums like Led Zeppelin II and Cream's albums (for example) don't suffer from compression (at least in older releases). They might not be as detailed, but they're not compressed and don't cause above normal levels of fatigue. They're still plenty easy to listen to.
I will admit, however, that I do gravitate towards better recorded music now. But I'll still listen to poorly recorded albums as long as they're not compressed so much as to fatigue me before I even get to the end of it.