Just wondering what's the worst story in a game you've ever had the (mis)fortune to endure. For me, it was probably NFS: Carbon. Sadly, it did have a very very very very very bad plot, in a racing game. Honestly.

Bikini Karate Babes

Final Fantasy 8 --> [SPOILER] "Hey we are all brother!" , "Hey, the bad witch is our mother!" , "Hey, the bad witch is not our mother, is another witch we've never heard about!", "Hey, let's fight my necklace!", "Hey...." [/SPOILER]
I really don't know how bad Limbo of the Lost's plot is bacause I've never played it... though it would probably be terrible.

UT3.
"We fight those guys, and all fighting grounds look EXACTLY like arenas becaaaause ... weeeeeell .... Our imagination and we keep being revived becaaaaaaause ... Yeah, portable respawners and we need to drain our enemies respawners by killing them more than they're killing us. Oh, also, flags are power sources that look JUST LIKE flags."

Agreed. Why Epic felt they had to change Unreal Tournament to suddenly be nothing about a tournament, is beyond me. The weird let's-crowbar-this-completely-unrelated-plotline-into-this decision really detracted from the single player campaign for me.
Two of the worst plots ever are Maniac Mansion and Zak McKracken, if you analyze the plots themselves. However, these are intentionally horrible plots, and fit in perfectly with the games, and are in fact what makes those two games so absolutely brilliant :-D

I was tempted to mention the plot of final fantasy 7 but after a few weeks of playing I never managed to find it...
Probably the worst one is the classic "The aliens are attacking and you're our last pilot!" which appeared in every second SEU in the 8 bit era

Conglraturation.
You prooved the justice of our culture.
Now go rest our heroes.

WHO YA GONNA CALL?!
Actually, I think one of the worst plots for a game was Onechanbara.
also,the last star fighter.

Easy
Assassin's Creed

*poof* Lot of what I said is gone for the moment. Ignore this one.

Enjoyed that game, but I'd have to agree with you...what a bizarre mess of a plotline.

Without a shadow of a doubt it has to be Pariah for me. That game feels like it was made in seven different rooms and only at the last minute were they put together. I would only recommend it if you want to WTF your way through it and come out with a few hours of your life wasted.

Hmm, I came to think of Quake 3, although the story is solid and holds up game-wise, it's not good. (Apparently not many people know there actually is a story at all.)
Some alien race likes to watch matches of life and death between other alien races, and have abducted you to be one of the champions for their entertainment. They also have resurrection technology (or something), so don't think you can just take the easy way out and die.
I'd quote the manual, but I can't be bothered.