I can think of a fair few of these. Psychonauts was the first that came to mind. It's a wonderfully imaginative game with a great variety of levels, but there are also sections where the loose controls make things horribly frustrating. The last level in particular is one of the most infuriating parts of a game you'll ever play.
I'll also give special mention to one of my unpopular favourites, XIII. The AI was not great and there were times when the game was finicky about where you needed to stand to trigger the next part of a level, and the checkpoint system wasn't particularly generous either. Even so, at its best, it was a fun, stylish romp with an interesting story and plenty of no-nonsense gunplay that many subsequent shooters lacked. I'm still sad that they didn't make a sequel.