Trespasser for me too.
When it was released, Trespasser promised a lot more. I'm sure you're aware the developers disabled code and didn't have the time to fix many bugs. Trespasser was primarily a product of hype...JP was the biggest film of the moment and the developers wanted to capitalise on the IP. But they had a limited time to do that.
Anne's single arm was only present because they couldn't code two arms. That and her breast make interesting perspective, kind of the direction Mirror's Edge goes in almost a decade later, and the surroundings are an uglier version of Far Cry. Those are actually fine in my opinion.
The biggest problem for me is they didn't get the dinosaurs right. I've heard what their vision was and it sounded great, and that's the point - Trespasser was a huge disappointment when you were there at the time of release, hearing about these wonderful things that never materialised. As it stands, it's an interesting curio to play, and not a terrible game, but it is a game that never lived up to its promise.
The Gamasutra retrospect is a good indictment of Trespasser from one of its own developers, especially page 4.

well, maybe cnc3 is just 'too futuristic' for me. at first i found dificulties to get used to Tiberian Sun (which i love today). first cnc have rather 'normal' units. sure there was sci-fi tibertium (whole cnc is built around tiberium) but there was so many 'normal' units in Tiberian Dawn.
so - if cnc3 went maybe a little bit too far [again - for me], then i could never get used to red alert 3 (talking tanks in the tutorial was first big - WTF)

I know I mentioned it before but I have to reiterate: Blood Bowl
I've not been so disappointed in a game since C&C Generals. I've tried to like it, I really have but its just all round shoddy, obvious dice roll fudging as a form of difficulty, every team using the same mostly ineffective keepaway tactics, no modification of the players appearance for mutations or even team specific colours...
Oh yeah, and no tournaments over xbox live...
Fucking awful, ebay can have my copy, might be lucky and get $50 for it

Whats so bad about Blood Bowl? Im actually thinking about getting it

Well the 360 version pretty much lacked everything I wanted. This is a combination of a moderately shoddy game and profound disappointment. Fuck, I BOUGHT a mate a 360 so we could have online tournaments with the group of us who used to play physically so the whole lack of online tournaments thing was kind of like a kick in the bollocks

I don't know about Xbox version but in the Pc version the dices are random, you can see the mutantions on players (ex: second head), and change their colour. but yeah the AI sucks on the PC too.
also there has been a lot of fixing lately so most skills are working as intended.

Three games?
Dont hate me for this.. its just my personal opinion..
World Of Warcraft
Any Halo.... repetitive on the FPS genre, play one, play all
And this might be an odd one out.... but Kirby 3 f or the SNES.. you know, where you don't eat shit just ride these weird animal things and it looks like it was all drawn with a crayon? Yeah.... Imma fan of the Kirby franchise... but that one for the SNES.... sucked.

Oh yes. That game really was one I had high hopes for and turned out a huge dissapointment. It took a game that was really great with ever changing surroundings and then made a sequal that is a repetitive MMO wannabe game. It seems to try to be like Fallout 3 or Oblivion but ends up even more repetitive.
I did not mind having fights that were almost the same in Farcry since it was still progress and new surroundings, but in Farcry 2 you get to go past the same checkpoints with the exact same enemies and vehicles several times. That is more than just annoying. Way to screw up a really good game. One of the best and most surprising games I have seen so far actually.
2. game although it is not out yet: Starcraft 2. They removed LAN and makes multiplayer an online only experience. For this I just can't buy the game and support the deterioration of the game industry, especially such a good brand. Seems to me that WoW has made Blizzard greedy, or maybe it was just the change of people in charge.
3. game: Simon the Sorcerer 3d. I still think about playing the game through, but it really was one of the worst cases of making an adventure game into 3D in my experience. So far to me the only not dissapointing 3D adventure games are the ones Telltale Games is putting out. But as for the rest, Monkey Island 4, Simon the Sorcerer 3D, Broken Sword 4 (dont remember the 3rd one but I do not remember being annoyed by it being 3d for some reason.
What I really hate about adventure games going 3D is how it just makes for a huge waste of time walking around instead of just puzzle solving. I guess Indiana Jones did okay, but never got around to playing through any of them other than Fate of Atlantis. My brother did though, and they do seem like fun.

An addendum to my previous list, as this one is disappointing, but not for the game itself. The game I am referring to is Kane and Lynch. I actually really enjoyed the game, but I was only able to beat it once. Why? Because the PC version was a buggy mess that was NEVER patched, not once, despite numerous crashing bugs. No matter what system I tried it on, the game crashed randomly every 10-30 minutes. It was the lack of support from both Eidos and IO Interactive (A shame since I really liked their company until this) that ultimately caused the DVD's fate in the enclosed image.
I'm probably going to rebuy it for PS3, but I shouldn't have had to if they would have just released a patch for the game. But no, over 2 years later and NO patch whatsoever for a broken game.

Freelancer, Microsoft's attempt at an outer space combat game. Crap story, crap combat, crap simulation of space. Done so much better in several other games.
Privateer 2. Really expected more after playing all of the WC games and the first Privateer.
Heroes of Might and Magic, not sure which one. Some things should not be turned into strategy games, and this is one of those things.
Age of Empires #16,984. At some point, "more of the same" becomes repetitive. Maybe not a most disappointing game, but a most disappointing purchase. By that, I mean that I should have known it was going to be the same old thing but with better graphics, some new nationalities and units, and a few tweaks to the mechanics, so I was disappointed with myself that I didn't figure that out before shelling out good money for a game I'd essentially played 3 times over with earlier versions..
Empire Earth. An RTS that encourages one to develop templates to win: build this unit at 0:00. Build this building at 0:30. Research this tech at 0:45. If your opponent had a better template at the beginning then it was very tough to catch up later on. Just didn't have fun with it at all.
MOO3. Take everything fun out of the original and replace it with a spreadsheet. And then take everything fun out of spreadsheets and you're left with MOO3.
Of those 6, I'll pick MOO3, HOMM whichever #, and Empire Earth, with Freelancer getting Dishonorable Mention.

This one didn't make my top 3 in the end, but I wanted to throw it out there anyway as a runner-up, just because no one has mentioned it yet.
Blaster Master: Blasting Again on the Playstation. As a kid I LOVED Blaster Master on the NES. It's still a great game and a lot of fun to play, even if it hasn't aged as well as some of its contemporaries. I didn't even know there was a sequel / remake on the PSX until I saw it in the store for $10. I got excited and picked it up, and only got more excited on the way home. When I started playing it, though, it was... not very good. All the ideas for a great Blaster Master sequel were there, but the execution fell terribly short.
I've played worse games, but not very many that were so disappointing. I ended up trading it in for something else after a few days.

Dunno if it is already mentioned, but for me the most disappointing was Dungeon Keeper 2, since the original was sheer brilliance and the best game till date imo. Btw, when are we going to see Dungeon Keeper on GoG?

Spore takes the top spot easily. It was supposed to be the "best game evar" and feature deep gameplay spanning billions of years of your creatures' legacy. Instead, it's a flexible creature creator with a handful of shallow games to import said creatures into.
Prince of Persia 2008 is #2. I loved Sands of Time and looked forward to a game that could combine its amazing platforming puzzles with an open world setting and a gorgeous art style. Well, I got the open world and the gorgeous art style, but the gameplay basically consisted of pointing the Prince in the right direction and tapping the jump button.
#3... I know I'm going to get murdered for this, but, in my humble opinion, Beyond Good & Evil is probably the most overrated "underrated" game of all time. Don't get me wrong, the game does deserve some praise. I agree with the game's fans that it has an excellent presentation; the graphics, score, and story are all superb. The problem was that the gameplay was too simplistic for my taste and felt like a vehicle for the game's other elements. I did enjoy the game enough to finish it, but the gameplay didn't exactly thrill me. It's not a bad game, just one that I don't think lives up to the hype, at least not in terms of its supposedly amazing gameplay. Oddly enough, I did really dig the photography sidequest. That's how Wind Waker's "Nintendo Gallery" should have been done.
Oblivion gets honorable mention for being poorly optimized, having a dull world in comparison to Morrowind, and some other assorted gameplay problems. I honestly do (vastly) prefer Oblivion's gameplay in some ways (combat and stealth are much better than in Morrowind), and the world isn't terrible or anything, but Cyrodiil will just never capture my imagination the way Vvardenfell did. Vvardenfell felt rich, textured, and organic, whereas Cyrodiil feels... game-y, for lack of a better word. "Oh, here's Big Town. And Snow Town. And Port Town. And some boring little inns and villages that all look the same are scattered in between. Yawn." And let's not forget the critter leveling, which, though more user-friendly than Morrowind's system, really kills the sense of realism. I do like the idea of respawning creatures and loot in caves, but it would be nice if the new creatures were actually stronger than the old ones. Give me a remake of Morrowind on a better-optimized version of the Oblivion engine and I can die a happy gamer.
Sims 3 also gets honorable mention. Once again, I prefer the actual gameplay of TS3 to its predecessor, but the "lightening" of the content for the sake of releasing it as DLC bugs me to no end.

Doom 3 - Lacked the fun of the originals and the pace was destroyed by the constant 'oh look another health pack, I bet a monster will spawn behind me when I get it... quelle surprise it has!' gameplay. The weapons were rubbish (worst shotgun in a FPS for about 10 years - has a 4 foot effective range) and the AI abismal. It gets better in the Hell part but I haven't bothered to complete it even after 5 years.
Ultima 9 - Originally was a 3D isometric game featuring the Avatar saving the day again with a party of adventurers --- yet ended up delayed constantly and being a bug filled game that was overly simplistic and had no party members. What a poor and undeserved way for the Avatar to end his adventures.
Lords of Midnight: The Citadel. The third game in the LoM series, the originals being based on the humble ZX Spectrum microcomputer back in the early 1980s. The game could have been a massive epic akin to the Lord of the Rings (which it was heavily influenced by), and it was certainly huge just like the original games were, yet it was just awful. You had a huge huge 3D world that was pointless to navigate, no sound effects, hundreds of characters controlling armies that did nothing, the worlds worst and inane combat/battle system and some stupid prison break plot that took up all your time and was nonsense. The original games were classics, pushing the huimble 8-bit machine to the limit, and yet still had charm and playability, whereas this PC adventure was a bug filled, soulless piece of crap. Fans of the originals are still hoping that someday a proper decent sequel to end the trilogy is created, although if it is even remotely as bad as this game I hope it never happens.