"A stupid person can make only certain, limited types of errors; the mistakes open to a clever fellow are far broader. But to the one who knows how smart he is compared to everyone else, the possibilities for true idiocy are boundless."
Often suffer some impediment, or endure some prejudice, to the point where being dramatically and demonstrably more awesome than everyone else in their field is a necessity if they're going to be seen as a success at all.
Sousuke Aizen from Bleach is considered the biggest mastermind in the series, capable of playing the entirety of Soul Society in his palm. He enjoys using his cunning to manipulate everyone, but despises Kisuke Urahara who is perhaps the only person who can match him in intelligence.
Sasuke Uchiha from Naruto is a child prodigy and one of the few survivors of the all-powerful Uchiha clan. However, when his brother, Itachi, trashes him showing that he isn't as powerful as he thought, and even Naruto, who had always been inferior to him, manages to almost defeat him, Sasuke's confidence and inferiority complex kick in. Because of his wounded pride, he defects.
Light Yagami is impossibly clever, charming and manipulative...but for all his great plans, he ends up falling victim to his own pride too often.
L also has traits of this. He's confident enough in his abilities that he's willing to get very close to a serial killer that can kill supernaturally.
Lelouch in Code Geass is a brilliant strategist and turns out to be a gifted leader, but the higher he aims, the more he is prone to his goals going horribly wrong.
Seto Kaiba in Yu-Gi-Oh! has great confidence in his skills and is a genius who can create impossible advanced gadgets while he's still a high schoooler. He starts the series as the undefeated champion of Duel Monsters, but his arrogance earns him a beating by The Power of Friendship. He never quite abandons his pride through the series.
Sora from .hack// is at the maximum level possible in The World, has the maximum possible stats, and goes around killing people for fun. Much of his arrogance is probably due to his age. Apart from that, he also openly manipulates everyone and is essentially the most obvious sufferer of Chronic Backstabbing Disorder ever. He has information sources no-one else has and is basically invincible in any of the fights he gets in, constantly killing BT. He gets called on it, but it never hampers him until finally motivated into doing something somewhat heroic (he didn't realize he couldn't get away) and taunting the big bad, at which point she turned him into a Sequel Hook and the show ended.
Taikobo from Hoshin Engi. He's a brilliant strategist who once managed to save an entire village by getting them drunk so they couldn't fight an army that came to capture them and then killed the leader, causing the army to scatter. He's famous for manipulating most of the cast with ease...but the first time he met Dakki, he ended up being enslaved and forced to watch members of his clan get thrown in a pit filled with crocs and snakes, one of them calling him pathetic. His ego went down in size after surviving that.
Linus seems to have this trait in Peanuts. He often explains things using scientific facts and theories (most of which are true), quotes philosophers and the Bible, and often compares simple things to famous works of art. On the other hand, there are times when he acts naive or downright foolish; his well-known belief in the Great Pumpkin is mocked by the other characters, even by his usually-tolerant friend Charlie Brown.
Films — Animation
Megamind. Megamind's intelligence is hyper-advanced compared to Earth standards, he's a genius inventor, and his hobby is creating grandiose revenge schemes against the kid who bullied him in elementary school. Worth mentioning that these schemes don't just fail, they fail.
Films — Live-Action
Mark Whitacre, the title character of The Informant!, is an accomplished scientist who speaks several languages and sorely overestimates his own prowess when he gets between his company's corrupt leadership and an FBI probe. Not only that, but it turns out he's been embezzling millions from the company and spinning outrageous lies to make himself look good, both in the company and in his personal life. Not that he isn't brilliant (he earns two PhDs while in prison), but he's determined to succeed big and when that fails, he fails big.
Katharine Parker in Working Girl, a high-ranking business woman that speaks fluent German and views herself as a trailblazer for women in the business world, with a giant head to match. When it's discovered that she stole a brilliant idea from her equally intelligent secretary, she's promptly (and satisfyingly) fired and disgraced.
The Vorkosigan Saga': The titular Miles Vorkosigan, as illustrated by the following quote from Mirror Dance
"My game plan all my life has been to demand acceptance of this," a vague wave down the length, or shortness, of his body, "because I was a smart-ass little bastard who could think rings around the opposition, and prove it time after time."
Moist von Lipwig of Going Postal and Making Money is a con man turned government official, who runs his government offices as though they were successively more complex con games. Which of course, in a very real sense, they sort of are.
There's a phrase that appears in Discworld novels fairly often (though it's considerably older) that actually describes this: "So sharp he kept cutting himself, as my grandmother used to say."
Ponder Stibbons as well; his impediments are the rest of the staff.
The Klatchian mastermind behind the international incident in Jingo may qualify as this; he is certainly clever, and his plan would have worked very well, apart from one small problem: his opponent is Vetinari. As a result of this little oversight, his failure is truly monumental and extremely humiliating.
The cleverness of these characters actually provides a good contrast with Vetinari, who is indisputably Discworld's premiere Magnificent Bastard. On the very rare occasions when he does make a mistake, Vetinari always recovers and learns from them. Also, he knows better than to push his luck, (his family motto is translated as "If it ain't broke, don't fix it"), and has thus so far avoided any spectacular cock-ups.
There's also mention of a dwarf who asked his king for an impossibly vast reward similar to the wheat and chessboard problem. The Dwarf King, upon realizing how vast such a reward would be, threatens to execute the dwarf who is "too [dwarven expletive] clever by half." The dwarf hastily amends his reward to "all the gold he can carry". The king agrees to this, after breaking one of the dwarf's arms.
And also Opal Koboi, who in the fourth book has her ears surgically rounded and a human pituitary gland inserted into her head so she can pass for human - but it also drains her fairy magic, making it so that she inadvertently uses the last of it to convince an old Italian widow that she is her daughter and she works with her on the farm. When Opal realizes her mistake and tries to backtrack, it fails so miserably that she's actually happy to get arrested by the LEP a week later.
Locke from The Lies of Locke Lamora and its sequel by Scott Lynch. He spends his life running elaborate (and usually successful) cons on nobles while posing as a petty thief. The end of the second book covers a massive failure; he's spent the entire book on a plot to rob a casino, and it goes off flawlessly — except that the paintings he steals are fakes, put out for the express purpose of being stolen.
Kvothe from The Name of the Wind by Patrick Rothfuss. A child prodigy, he talks his way into his world's premier university at the age of fifteen, after having spent three years as a beggar, and promptly antagonizes both one of the masters and the wealthiest and most politically connected student in the university. Between that and his perpetual poverty, he spends most of his time doing absurd things (learning an entire language in a day and a half, getting certified as a musician on a lute with a broken string) just to keep his head above water.
Tyrion Lannister. As a dwarf, quite literally... And unlike some on this list, he's fully aware that he's pushing his luck in most of the stunts he pulls.
Petyr "Littlefinger" Baelish is described using this exact phrase by Ned Stark. He is, however, an unusual example of this trope because (so far) he has gone from strength to strength without anything more serious than some minor setbacks, to which he quickly adapts.
Grand Admiral Thrawn of the Star Wars Expanded Universe, especially The Thrawn Trilogy. He'd probably claim that he's not arrogant, and it's true that he has no qualms about accepting a good idea just because it's not his, but he definitely has an ego underneath his self-control. He's an alien in a xenophobic Empire whose talents caused him to get that rank. To a lesser extent, Talon Karrde.
In the Star Wars Wraith Squadron trilogy both of the computer hacker characters fall to this somewhat. Grinder falls to this in that Face catches him pulling pranks as he was the only one who could have defeated the security on Face's X-wing. However when in the field, he is quite cautious, unlike his replacement. Castin falls to this in a much worse fashion when he opens an access panel without scanning it first, triggering a hidden layer. Grinder had previously dealt with a similar system and had the foresight to scan it before opening it. This ultimately led to his death.
Dumbledore is also qualified for this trope. He is one of the smartest and most powerful wizards in the setting, and he knows it. However, Dumbledore is reformed version of this trope. He used to be arrogant and controlling, until an unfortunate accident lead him to realize what he was becoming. At that point, he decided to settle down and become a teacher, because he felt he could not be trusted with any real power. During the story, of course, we do get to see him affording himself more power to bring down Voldemort.
Dumbledore: Being, forgive me, rather cleverer than most men, my mistakes tend to be correspondingly huger.
Voldemort is a half-example- he's a magical genius, yes, but he tends to generalize this and assume it means he's smarter than everyone at everything. Given that he is in actuality not even remotely competent at long-term planning, he manages to get all the downsides of this trope without any of the benefits.
Douglas Adams' Dirk Gently. In a scene where he tries to outwit the local Inspector Lestrade, and fails miserably, the inspector sensibly points out that Dirk may be really smart, but the weakness of really smart people is that they assume everyone else is stupid.
Foxface in The Hunger Games manages to survive without killing a single soul, simply stealing food and hiding. It bites her in the ass when she steals some berries Peeta had picked without either of them realizing that they were poisonous.
Or DID it bite her in the ass? In the movie especially, there are hints she committed suicide on purpose and covered it up so that her family back home didn't get in trouble with the Capitol. Foxface: cunning to the end.
Katniss actually wonders if Foxface is the most intelligent out of all the tributes. As the Games go on and she realizes that Foxface has lasted so long without a direct confrontation against anyone, Katniss wonders if Foxface, not the intimidating Thresh or the totally batshit Cato, is the real danger.
Kendra in Beastly. She has a history of using her magic to punish rotten people like Kyle, but it bites her in the ass when it attracts too much attention and gets her in trouble with other witches. While her grand plan to improve Kyle via the curse she puts on him does work perfectly (it's designed so that he really can't break it without improving himself), it's also what convinces the other witches to banish her from ever going home. Leastwise, until Kyle accidentally puts a loophole into the spell that lets her go home after he breaks the curse. In the book Bewitched, Kendra apparently had a knack for this even before she had a lot of experience as a witch.
Themistocles Papadapoulos in the bridge book series Bridge In The Menagerie. He delights in deception plays, which frequently confuse opponents into making the one play that can defeat his contracts or ensure theirs when he's defending. One chapter of the second book has an entire section on him called "Too Clever By Half."
Steerpike in the Gormenghast books. He's extremely clever and a master Manipulative Bastard, but his first real failure comes from arrogance, overplanning, and underestimating his opponent.
Rodney McKay from Stargate Atlantis is one of the smartest humans from earth. Despite everyone in the Stargate program being top in their fields, he's the only one to make such a huge deal out of his intelligence. And on number three... while he regularly makes astounding accomplishments while under threat of imminent death, his biggest failure? Blew up a solar system (well, five-sixths of a solar system), and almost destroyed two universes. His pride is so great that he often refuses to work with other scientists on the team because he's convinced they'd just slow him down. This gets pointed out magnificently in an episode where he's working with Real Life celebrity scientists Bill Nye and Neil DeGrasse Tyson, or rather, refusing to work with them. This gets taken to a literal level when an Ascension machine rewrites his genome. He creates a new form of math just to keep up with his new discoveries. After being forced to choose between ascending or dying, he invents a cure for himself, returning himself back to "normal" genius levels, not realizing he was in spitting distance of ascending. For a kicker, all his notes and his new mathematics are so complex that even he can't figure out what they meant, making virtually everything that happened completely moot. Did get a nice Deus ex Machina out of it for the next season opener, though.
Samantha Carter from Stargate SG-1 isn't arrogant, but she knows how smart she is, is constantly being expected to do more and more impressive things to save humanity's collective asses, usually succeeds at saving the day but occasionally has some pretty spectacular screw ups, and gets discriminated against for being female and human by Ba'al and the free Jaffa.
Dr. Nicholas Rush in Stargate Universe is, quite possibly, even more arrogant than McKay. At the very least, he loves to put other scientists down. At the same time, he sees Eli as a protégé of sorts and isn't as hard on him as on others. In fact, on at least one occasion, he told another crewmember that Eli is actually smarter than him.
This guy, he knew that he wasn't accepted by the staff, he didn't even try. He didn't dress well. He didn't pretend to be one of them. The people that ran that place, they didn't think that he had anything they wanted. Except when they needed him. Because he was right. Which meant that nothing else mattered. And they had to listen to him.
Sheldon Cooper from The Big Bang Theory is a brilliant theoretical physicist. He's also insufferably arrogant and ignorant of basic social interaction. At least once the other characters acknowledged that if he wasn't Leonard's roommate they wouldn't hang out with him.
Doctor Who: The Doctor has a tendency to drift in and out of this trope, possibly more so in his third incarnation. The Tenth Doctor is especially given to telling people how clever he is.
Villainous example: Orta from the Star Trek: The Next Generation episode "Ensign Ro". Extremely good Bajoran terrorist with many impossible victories against the Cardassians, but his failures cost him his right eye and the ability to speak without a voice synthesizer. Over the course of the episode, Picard and Ro find that Orta did not make a strike they were investigating, because he didn't have the resources to do so (his freighter could only move at half impulse, for example)...because his rep was such that others were terrified in dealing with him.
Sikozu of Farscape definitely fits this trope, especially given her arrogance over her high intelligence.
Sherlock Holmes from the BBC's modern-day adaptation, Sherlock, is a perfect example. Yes, he's very, very clever; but it does have its disadvantages in that he can't "turn it off" and stop being an Insufferable Genius, even when doing so would be much more prudent. (Not a million miles from The Doctor — which, given that the show is scripted by Steven Moffat and Mark Gatiss, is...not that surprising, really).
Russell Hantz, for example, has some savviness as to the mechanics of the game, finding Immunity Idols before receiving any clues to their location for example - but lost, three times, for being completely insufferable, not grasping that you have to avoid getting on the other players' bad sides. (The third time he played, his tribe was aware of his previous two times and made a point to throw him out fast so they wouldn't have to deal with him.)
"Boston" Rob, on both this show and The Amazing Race is another great example. He definitely has a talent for this stuff, but... well, on Race he decided to screw with other players by making them think there was an earlier flight. While he gloated about sending them into a panicked search for a nonexistent flight, they found one.
Jimmy McNulty from The Wire had to drop out of college due to his girlfriend becoming pregnant. He became a detective who has no respect for his commanders and that'll do anything to get a case solved.
On LeverageStarter Villain Victor Dubenich is this in the pilot. The entire plan depends on his ability to realize that they are coming after him.
In "The Gold Job" Hardison makes this mistake as well. As he is planning his clever system for forcing the mark to pay for their client, he makes the mistake of causing them to question all of the hoops they've had to jump through during the con. [[Aesop Nate explains]] to him that he always avoids this by having a relatively simple backup plan.
Dr Ty Wilson from Monday Mornings has nearly all traits of this archetype. Only thing which doesn't quite fit is that he's not insufferable in the least. He has sweet bedside manners and he's also very nice to his colleagues, which makes him popular. He's an extremely smart and brilliant neurosurgeon with magical hands. He knows it, and doctor Hooten often calls him on his medical arrogance, especially his reluctance to consult with his colleagues who as as brilliant as himself. note Seriously, what a hospital! Apparently, Chelsie General has at least four neurosurgeons who are all the world class top. The series kicks off with a patient dying on his table. It's rare for him and he feels badly, but even more so when he learns that he didn't check all relevant medical history of the patient's father. He screwed up, big time, and has to deal with his Heroic BSOD.
The eponymous character in Marlowe's Doctor Faustus is one of the best examples of this trope. His own brilliance in all things academic (and belief that he can do even more than he has) lead him to explore Things Man Was Not Meant to Know. The results are predictable.
The Onion Knight in Dissidia: Final Fantasy. He's quite clever, and knows it (and will remind Terra bout his intelligence at every turn). He easily fits parts 1, 2, and 3. Especially 3. You want a spectacular failure? How about causing Terra to go out of control, let her get brainwashed, beat her up when you can't think of any other way out of it, and let her get kidnapped? His Destiny Odyssey is all bout him being knocked down a serious peg, and learning to listen to his heart, not just his head.
Fallout New Vegas has Mr. Robert Edwin House, President, CEO and Sole Proprietor of New Vegas. He's a Properly Paranoid genius owner of a Mega Corp who firmly believes Democracy Is Bad (not entirely untrue, considering the circumstances) and defended Las Vegas from nuclear attack to rebuild it to a point of glory, but is incredibly arrogant and refuses to believe that he could be wrong. He also thinks there's no question that he should be the sole autocrat of New Vegas.
Mass Effect 2 introduces Miranda Lawson: as she and her sister were Designer Babies engineered by their megalomaniacal Truly Single Parent to be Born Winners, Instant ExpertTV Genius barely begins to describe her capabilities. Lawson drops in on Shepard's resurrection without any knowledge of the process and becomes the project's leader through sheer ability in less than a week. Of course, being unfamiliar with failure, she never sees her mistakes coming: her statement that "any Biotic could be a Barrier Warrior" turns out to be completely in error - it takes a Psycho Prototype or a Cool Old Lady to do it - she gets an ally killed if her advice is followed. She is actually cursed with being aware of this trope, resulting in an inferiority complex - she attributes all her successes to her father's design, and only takes credit for her failures.
Hatoful Boyfriend: Holiday Star has signs of this in the young aristocrat Shirogane Sakuya. He's very smart, always arrogant, blind to any flaws he might have... his more aware brother once considers getting into a room Sakuya's renovated and equipped with security, which Sakuya'd proudly boasted was impenetrable.
But this is him I'm dealing with. It'll look like sneaking in is going to be hard, but knowing him he'll have overlooked something obvious...
It turns out Sakuya rigged the door extensively, but completely neglected the windows. This despite him being a flight-capable bird, in a school full of other flight-capable birds.
Suspiria, Insufferable Genius mage prodigy from Flipside. She really is a phenomenally powerful mage, but given her youth, she lacks both the experience and stamina of other mages of her rank, making her a much less formidable opponent than she should be. This has bitten her in the ass twice, in-story (the first with tragic consequences, the second costing her the other main characters' good will and respect and any sympathy the former granted her).
Vaarsuvius is the resident Squishy Wizard and needs to be smart. Also, V's an Elf, so there's the arrogance. The demonic contract plotline manages to skate on the very edge of the "spectacular failure" point without quite falling in.
Oracle: Yes, you've certainly managed to cunningly outsmart yourself at the very least.
Also Nale. He may be smarter than Elan, but he's definitely not as smart as he thinks he is. For example, Nale had his succubus femme fatale disguise herself and send the party on a dangerous quest to recover star metal. He assumed that the star metal would have been recovered as "everyone" has known about its existence for a great deal of time. However, it hasn't been recovered, and after it is found the only result is Roy, an enemy of Nale's, having the star metal used to forge an Infinity+1 Sword.
Many devisors and gadgeteers in the Whateley Universe get this, but the biggest of all must be Jobe Wilkins, Prince of Karedonia. A literal child prodigy even before he started breaking the laws of reality — and a first-class Jerkass — he sets about making a nanotech formula to transform anyone into his ideal wife. And then he injectshimselfwithit.
The title character of Invader Zim is an Evil Genius, smarter than all the other invaders, but his massive ego and faulty programming prevent him from taking over Earth.
In Iron Man: Armored Adventures, Teenage Tony Stark has many of the problems of his other incarnations (keeping secrets from his friends, arrogantly fiddling with technology he really shouldn't be and some outright hypocrisy) with the problems of a super genius who's been home-schooled all his life and suffering from the loss of his only parent. For example, he creates a really good computer virus that devours data like a swarm of digital locusts but unfortunately merges with a swarm of nano-machines to become the very hungry Technovore monster. When Tony screws up he screws up phenomenally.
In a Halloween episode for The Simpsons, Kang and Kodos abduct them in their flying saucer to live in luxury. Lisa snoops around the ship and finds a book titled "How to Cook Humans". Offended at the notion, the aliens blow some dust off the book's cover revealing the title to be "How to Cook For Humans". To which Lisa points out that there is still dust on the book, with the title now "How to Cook Forty Humans". Kang (or Kodos) then demonstrate that there is yet still more dust on the book's cover, finally revealing the book's full title "How to Cook For Forty Humans" (the Simpsons were gluttons).
Marge: Now you know what we mean when we say you're too smart for your own good?
Young Justice: Nobody could say Amanda Waller is an incompetent warden... but at the end of "Terrors", The Alcatraz falls under the control of the Light, there was an almost succesful Great Escape, and she is replaced with Dr. Hugo Strange, one of their agents.
During a flashback on Hey Arnold!, Grampa Phil recounts how while serving in WWII, he was captured by a Nazi panzer brigade. The Nazi captain (Major!) decided that the bad meat Phil was carrying to a dumping ground was good because Phil warned the Nazis not to eat it in an absurdly long and one-sided I Know You Know I Know. As Phil's narration puts it;
"That's when it hit me! I realized that if I let him think he was smarter than me, I could make him do anything I wanted."
Galileo appears to have been one of these (see his entry under Instructional Dialogue), assuming Simplicio really was a caricature of the Pope.
Even before he earned the attention of the Inquisition, he alienated the University of Pisa (which prompted his relocation from Venice to Florence—he needed new patronage) and pushed matters so hard that even the powerful Medici family began to find him a liability. The man just didn't know when to keep his mouth shut.
You might find this in education, when the instructor uses the Socratic Method—feigning ignorance of the subject and asking questions of the students to draw out the correct answer. The intelligent or well-read students will often try to bring out information which, while interesting, either doesn't get to the heart of the matter or assumes that the central question has been answered when it hasn't. This typically leads the instructor to shut the student down—or try to, as such students tend to be stubborn. This is particularly common in American law schools, where the Socratic method is standard.
Free advice: There's a reason that Socrates' most famous statement is "I know that I know nothing."