Biggest Twist in an RPG You've Ever Experienced
One of the best twists I've experienced was also one of the most memorable campaigns I've been a part of.
This was a D&D 3.5 E6 campaign where the PCs were part of the city guard, investigating crimes and finding criminals. We had a group of 4-5, but the two massively important characters here were my character - a halfling paladin that was ostensibly working with the guard as a representative of the church in any law-enforcing organization (in reality, none of the other paladins liked him and wanted him gone) - and an orc fighter that joined 2-3 sessions into the game. Classes ended up not mattering all that much since it morphed into freeform where we'd roll dice occasionally to pretend we were still playing D&D.
My character had decent stats all the way around, but I played him as highly impulsive and he liked to think aloud, even the weirdest ideas. For example, he claimed orcs must be vampires because they're both light-sensitive and swap days and nights from other races when he first met our new orc guard. Or that he would become a demigod (presumably serving the Goddess of Life), because that's what all paladins should strive for. To note, he never said he was thinking things or that things must be like this other thing, he'd just say "this is how things are."
The most out-there case that ended up being true based on my guess was that a murder-victim was actually put into a coma years ago by his younger brother, who wanted the inheritance, and only recently had he died of natural causes and a household servant had stumbled upon the body while he was attempting to dispose of it. This was a schlocky primetime TV kind of fantasy-CSI game.
Eventually, things started getting weird. Politics with the church and the Red Elves and the Orcs were getting in on things and my character grew suspicious of the leader of the paladins since he was doing surreptitious things. One night when half the members couldn't make it, the GM let me do my investigation with my mentor - a dwarven paladin - and my mount - Butch the riding dog - as the other two players who showed up. We found the leader of the paladins with a bunch of other people in a weird sex cult with this massive human-sized baby. My character figured something evil and malicious was going on and, after just a bit of urging from the other players playing NPCs, my character murdered the demonic baby.
It turns out that the baby was actually the mortal form of the Goddess of Life and by murdering her I cut off all the divine casting (possibly in the world, but definitely in the city). Priests from other gods could cast spells fine, but paladins were completely shot because paladins only served the Goddess of Life. For some reason my healing still worked, though. And was a lot stronger than before - enough to regenerate lost limbs when I could only cure scrapes before.
Anyway, we end up in the Orc district, which is trying to rebel, and I attempt to quell riots while the rest of my companions investigate what's happening. The leader of the Orcs is actually an ancient vampire and has unleashed this artifact that's essentially an orb of wishes. I get pulled into the conflict when the street falls out from under me and I waste my wish on having the chunk of street become a floating island above the Orc district and that it would become its own sovereign nation, of which I would become king. Some of the orcs that were killed in the conflict floated up along with the land and I moved them into the one house and gave them names and positions of power in my government (Bartholomew a genius diplomat, General Joseph, and Zebediah for a Prime Minister). The GM kept pushing me to see if my character was actually playing with dead bodies (since I was just playing around when I said it... like a lot of the things I said) and I'm the kind of person that doubles down when asked something like that and I never gave an inch. My character was giving these orc corpses positions of power in his government and refusing to acknowledge that they were dead.
The riots got bad and I went down from my new city to help out and it looked like we were going to all die, since the entire district was swarming with vampires (ancient vampire lords tend to have minions). When from the sky comes Bartholomew the Master Diplomat, who calmed everyone down with a speech reminding them that they were all orcs before they were vampires. The speech resolved the conflict, mostly because it was an amazing speech, but also a bit because a lot of the orcs knew him since he was a pretty popular butcher and his name was not Bartholomew...
But it let us confront the vampire lord, and I road him into the ground and burnt him to non-existence with my massive healing power. And end of the campaign.
And after all this happened, the GM informed me (the rest of the group learned as the campaign went on, I was the only one in the dark) that paladins were created through the same artifact that gave us our wishes (via a one-shot in the past we ran early on, which also locked the Goddess of Life in her giant massive baby body), and they all had a bit of ambient wish magic within them. Paladins cannot lie. It is an impossibility.
So when I said that orcs are vampires, all of a sudden all the orcs in the city were also vampires. And they always had been. When I said that my character would become a demigod at the first session, it was inevitable I would kill the Goddess of Life and assume her power (he manipulated me into doing this and had no doubts that I would, and I had no idea). When I refused to acknowledge that dead orcs were dead and instead gave them new identities and skills, they were these new people. When I came up with weird ideas on how a crime was solved, the GM had to bend over backwards and improv that I was right. Without letting me catch on. He did say that my ideas on the culprits of the cases were more often right than wrong, but he refused to tell me any specifics or how far he had to go to make them work when I wasn't.
The only reason the campaign turned the way it did was because I was the only paladin in the setting that wasn't completely stoic and serious, and I was willing to be wrong and didn't know that wasn't possible.