The only things that ever drove me into an absolute rage in a game concerned its story, specificly disappointments with an initially promising one. When it comes to puzzles, I really mind, when they're overly cliched and the gameplay becomes a boring formality. Also, what always gives a strong vibe of amatourish, unfriendly design, is a complete lack of hints (spoken, written, drawn or animated) for some rather obscure puzzle.
There were some absolutely maddening gameplay ideas in an adventure game called The Prince and The Coward (aka Galador). The worst one demanded clicking the mouse buttons consecutively for about ten minutes to pull some object out. If you lost the proper rhytm, you had to start over. A true nightmare
.