Already mentioned, but anyway: Oblivion. While I would not call it unplayable, most of the balancing was horrible in release state. The only problem with modding is: once you get started, you probably spend half of the time modding the game and looking for new mods to install...

Another candidate: Gothic 3. Extremely buggy at release, but by now, the community has taken care of most of them (and made the game pretty good, imho).

My retail copy of Outcast came with a completely broken installer! It wouldn't even install!
I had to download a 1Gb 'patch', which was basically everything the installer would have installed, the disks where then only used for some streamed stuff like the music and CD checks!

I don't speak from experience, but I hear that with the right mods, Master of Orion 3 can be a somewhat decent game.

And the funny thing is... Epic Fail Games can't even admit their mistakes about this game... or about basically everything they released on PC since Gears of War 1.

Gothic 3: Horribly buggy, many things were downright broken, performance was horrendous even on the best of systems (and still is!). But with the handy fan patch, most of those problems goes away.

Vampire the Masquerade: Bloodlines. This game could literally not be finished without cheating on release (there was a place where the game crashed for everyone). With patches it became playable, with the fan mod it became great.

Master of orion 3: Released in a very sorry state, you could literally not lose without trying to do so. With the patch the game became almost playable, with fan mods it became good. The problem these days is finding these fan mods

Fallout 2 anyone? While not a 'bad' game at release, it was extremely buggy.
And not to mention silly things like the missing children in the European release which led to floating voices throughout the game (and they still stole, thus being able to render your game uncompletable).

Every Paradox game is, for all essense and purpose, released at beta stage and reach playable state year to year and half after release (dozen patches and expansion or two later). Same goes for every Bethesda product btw. Why bother with QA when they have plenty of dedicated fans to do it for them for free? ;-p

Really? What do they blame the failure of UT3 on, then? Piracy?

I've sunk many hours into MoO3 and thoroughly enjoy the experience on a regular basis.

MoO3 Mods

Nobody has mentioned so far: Darklands (medieval RPG) and Ascendancy (kind of MoO's clone).

Honestly I can't think of a "bad" game that became a "good" game through any patch or mod. Most of the games listed here were good games already, they just had some significant bugs.

Oblivion would be the closest. Francesco's mod fixes the scaling, Shivering Isles added some much needed pizazz and interesting quests and locations. It's a much better game with those... not sure I would call it a good game though.

The one that really stands out to me is hearts of iron 3. When it was first released it barely work and most of the features were broken.

I can't say for sure, it's been a while, but piracy and Cliff B. telling everyone "PC sucks" have been their major arguments for a while now.

To me, the honest fact is, they've been failing to deliver to the platform for quite a while. When was the last time they released something well polished and without any creepy DRM to the PC? Quite some time ago...