Lots of games over the years have given me "WTF were they thinking" moments...
First candidate WC1 the Kurasawa 2 mission. I think that is legendary in gaming circles as one of the next to impossible levels to complete in a game. Fortunately Origin allowed you to get back on the winning track by other means.
Next up also WC, this time WC3 where the game baby-sat you with some easy opening missions, then all of a sudden it's shoot down this stealth missile which travels faster than your ship and is invisible 90% of the time. That was nearly a game stopper until I figured you could eject, get picked up and lose the mission but save the ship.
GTA SA the first time I encountered the bouncing car mission. I really thought is this it, stuck 5% in and no means of getting round it. A couple of the later timed missions in SA also gave a few hair wrenching moments.
Oblivion when you levelled up just to find yourself facing tougher enemies, or more of them.
Any of the COD games including (new) Wolfenstein when your steady, ranged approach is abruptly terminated by a spammed grenade.
Far Cry 2 when your steady, ranged approach is abruptly terminated by the gun snapping in your hand. What do I use now, harsh language?
The above just a snapshot of what can quickly turn a thoroughly enjoyable game into a frustrating non-entertaining nightmare. IMHO any game which introduces an element or mission which may cause players to get stuck early on needs to have a built in workround, so you can do a different mission or approach a different way.