Why are some people turned on by the thought of their significant other cheating on them?
You don't say that you're turned on by the thought of your significant other cheating on you, so I'll direct my answer to the "some people" that you speak of. These folks, who are not you, are lucky because there is a wealth of fascinating theories on the topic.
This particular kink is called cuckoldery, courtesy of female cuckoo birds known for laying eggs in other birds' nests. Initially, the term "cuckold" was used to describe men with adulterous wives, particularly those who were duped into raising other men's offspring – a timeless, deep-seated fear that has been immortalized in the work of literary greats like Shakespeare and D.H. Lawrence. The meaning has since evolved to include those who are turned on by their partner's infidelity. Fast-forwarding to the Internet age, you have "cucks" who actually advertise for and seek out partners -- sometimes referred to as "bulls" in this sexual subculture -- for their wives. Believe it or not, this genre of porn is currently "the second most popular heterosexual interest in English-language search engines," according to the bible on such things, Sai Gadaam and Ogi Ogas' "A Billion...
So, that answers the underlying question of whether the desire is abnormal, but you're interested in the "why" of this kink. The most popularly cited explanation is sperm competition. The idea is that the sight of one's wife with another man serves as an unconscious biological trigger that creates a sense of sexual urgency, results in more passionate, longer-lasting sex and causes the cheated-upon spouse to produce more sperm – all evolved responses that up his odds of impregnating her. A related theory is that the penis functions as a plunger, displacing rival sperm. As such, this line of thinking goes, it would be beneficial in evolutionary terms for a man to become aroused by his partner's infidelity. As a review of a relevant text in the journal Evolutionary Psychology puts it, "intra-vaginal battles demand men to become aroused to situations that are actually unpleasant for them, for instance the suspicion of their partner’s infidelity." In other words, men "may become very sexually aroused at the idea of their partner having sex with someone else, even though they would strongly avoid such a situation."
There are likely other things at play here too, like eroticized fear. Dan Savage has speculated that "cucks" are merely sexualizing their fear of infidelity in the same way that a gay man might fantasize about sex with a homophobe. In a column on the topic, he wrote, "While most of us learn to live with and occasionally conquer our fears without eroticizing them, a number of us respond to sexual fears or traumas by incorporating them into our erotic imaginations." Speaking of sexually charged fear, many "cucks" fixate on "bulls" that are bigger, stronger and more well-endowed. This in part explains the popularity of profoundly politically incorrect – even for porn! – cuckoldery featuring African-American "bulls," given racial stereotyping of black men's sexual prowess. As Savage writes, "Whitey fears big black dick, rampaging Mandingos, white women coming down with jungle fever and getting their chifforobes busted up."
David Ley, author of "Insatiable Wives: Women Who Stray and the Men Who Love Them," lists a number of other potential factors, like voyeurism, taboo, bisexuality, masochism and misogyny. At my request, he offered up a gem of wisdom from his extensive digging: "Ultimately, I believe that the wife-sharing fantasy is powerful and prevalent because it taps into and triggers many basic components of male sexuality, and reveals many powerful dynamics of male-female relationships and sexuality."
Ley does offer a few words of caution, though, about turning the fantasy into reality. "This is such a challenging, taboo and complex sexual lifestyle that I caution men who have this fantasy that it is often far more difficult than they can imagine," he wrote to me by email. "Most wives simply will not go along with it, in part due to their fears of being labeled a slut, or losing their marriage, or their concerns over what these fantasies reveal about their husband's view of them." Interestingly, he notes, "I've also seen couples explore aspects of this, without actually engaging in sex with other men. Some couples go to bars, separately, and the husband watches other men try to pick up his wife, until finally, the husband sweeps her away." Not that this would be relevant information to you, dear reader. I merely offer it up to "some people."