A character who does things that don't make any sense, just because it's a "good" or "evil" thing to do. It's not that they've lost their goal — they're actually true to their good or evil alignment, but at the cost of every survival mechanism developed by humanity.
This goes way beyond being a Slave to PR or Card-Carrying Villain. Nor are these characters always Knight Templars. A Knight Templar is not necessarily Lawful Stupid, although they are always Lawful Neutral or Lawful Evil - with fanatical devotion to the word of law and absolutely no concern for the spirit.
For more on Lawful Stupid and Chaotic Stupid, check out the LJ post by The Ferrett in which he introduced the terms. However, note that his definition of Chaotic Stupid is closer to our usage of Stupid Evil.
Alignments covered as sub-tropes include: