I can think of a couple of examples, but they definitely fall on the extreme side.

Dark Castle for the Sega Genesis design was horrid. The controls are nearly impossible and the guy will fall over at the most mundane things imaginable. And the rope detection is a gamble each and every time.

Sword of Sodan was so horrid it has a level that walkthroughs recommend you skip because there are pit traps you have to avoid and the enemy always stand right on the other side of them so you can't jump over them, and there's NO way to otherwise tell where they're at. I only got past that level on pure dumb luck. Also, wanna turn around? Hold down B and press the left/right directional buttons. What? Huh? SMURF B!

Superman 64 is a mess. Superman's punches and movement are slow as smurf, making you take plenty of hits before you can even fight back, the hit detection is so bad you can't tell if you're actually doing damage, and everything explodes at the slightest physical contact. Even cardboard boxes.

Broken Helix had horrible controls that kind of remind me of Resident Evil to an extent, but with no running, and just following a guy can be a chore. No, really, there's a plotline where you have to follow marines and stay close to them or else they kill you, and you can get stuck on people and things making it harder than it should be.

So, I guess my answer would be, when the design is so bad it's in your face.