Chaser.

I don't think many people play Chaser for its story, but as long as the stinking cutscenes were (and as awesome as they could look sometimes) I couldn't help pay attention to it. I even found it interesting. Until the stupid end. Here's an abridged version of the ending:

Bad Guy Boss: "Oh, you aren't really who you think you are. You are actually a guy who was on our side, but then we did something, so you thought you were on the other side. So since you're really on our side, go ahead and kill the Good Guy Leader, even though you've been helping him for a long time and remember him better."

Guy-who-isn't-really-who-he-thought-he-was: "..." BLAM! Kills Good Guy Leader.

Bad Guy Boss: "Mwahahahaha! We win. Yo. All right, kill the Guy-who-isn't-really-who-he-thought-he-was."

Game over.

Half Life 2 Episode 2 comes to mind. Each time you finish it, you hear Alyx crying while the credits start rolling and you just want to beat down those damn advisors but you can't because there's no freaking sequel! Damn you Gabe!

Risen's final boss battle and ending kinda sucked, compared to the nice Gothic like feeling of the game.

I can forgive Risen's Ending only because there is going to be a Risen 2 and I believe they have learned from there mistakes! At least I pray each night they do :)

I'll add Descent 3 to the list. You play through the whole game (or in my case the whole series) only to have freakin GUIDEBOT kill Dravis!!! :/

Giants doesn't really belong here (because the ending is actually brilliant) but, reading this thread, I can't help but think about the way they pulled it off.