Vampire: The Masquerade Revised, and the Revised editions of World of Darkness in general, are the biggest actual "heartbreak" in TT RPGs for me.
Not because they were particularly bad, nor that we didn't play them, but because they were just nowhere near as good as we'd hoped. I mean, my brother and I were super-hyped prior to Revised. We thought this would finally be the WoD system that fixed all the little issues and annoyances and even some of the bigger problems, maybe even bring the increasingly-dated-seeming setting up to date a bit, because that's basically what the writers said it would do. We ended up pre-ordering two of the fanciest copies of the corebook set for VtM Revised, at what was great expense for whenever it was (1998 maybe?). And... meh. It didn't fix much at all. It made the setting more boring and dull. It attempted to "crack down" on people "doing it wrong" gameplay-wise, and reinforced that by insisting on various lore changes that made vampires more "walking corpses" (which was very much against the zeitgeist, whereas WW had basically sailed the seas of zeitgeist, and very successfully). Even the art wasn't as good as the 2E line, and had a less consistent vibe.
It was notable that when a decade or so later, the 20th-anniversary editions came out, they basically ignored the lore changes from the Revised editions (for the most part, though a couple made weird decisions of their own), and featured actually-improved mechanics.
So that was the biggest heartbreak, because we were so invested in it being great, and it was profoundly "meh".
Next biggest after that would be Cyberpunk V3, because Cyberpunk 2020 was getting a bit dated by the early '00s, arguably by the late '90s, both in mechanics and in details, and R. Talsorian/Mike Pondsmith seemed weirdly disinterested in doing anything about it. Finally we hear about Cyberpunk V3 and we're cautiously excited (Cybergeneration was kind of cool in a weird way so wasn't really disappointing), but what we actually got? A really "far-out" post-Cyberpunk setting which wasn't either believable, compelling, or connected to our world, with epically bad art (photographs of action figures, literally) and visual design. The mechanics were also not particularly good. Cyberpunk RED pretty much has the setting we were hoping for 15 years ago, i.e. an updated and actually more-playable version of the 2020 setting. It also has not-great rules by 2021 standards, but only mildly so, and if we'd got it in 2005, it'd have been amazing.
Finally, D&D 3E.
That might seem ironic in certain ways, including that it caused this site to exist, but as excited as I was before it came out, as we got closer to release, and post-release, decisions I felt were "bad" just increasingly started to pile up, and of all the editions my group really played, 2E through 5E, it's the ones we spent least gaming time on, by far (even if we including PF1 as the same thing), and the one we had the least actual fun whilst playing, which was largely because of the kind of characters and style of play we had (which tended towards martials and stunt-y).
4E not lasting as long as it could have was a disappointment, but one significantly mitigated by the fact that by then, the indie RPG scene was considerably more exciting, and also 4E itself mitigated it a bit by making it so it was increasingly less fun to play at levels above about 11, as more and more Immediate and Interrupt and Reaction and so on stuff came in and bogged combat back down to 3E levels of time-taken. If they could have kept the fun of 4E 1-10 at all levels I'd have been a hell of a lot more disappointed.
The funniest disappointment will always be Champions: The New Millennium, a FUZION-based re-working of Champions, which superficially seemed like it might be a great superhero game, ditching the problem HERO/Champions had of always feeling like a fairly serious squad-combat game that happened to feature superheroes, but keeping much of the flexibility (it was 1997, cut us a break!), with more accessible mechanics.
Except as it turns out they were just as bad, but in a different way.
We took something like 5 hours to run a combat which, in-setting, took maybe 3 minutes, and just was a spectacularly knock-down drag-out fight. Sure it was four supers vs six serious villains, but good freaking lord. It just went on and on and on endlessly, and the fact that everyone got different numbers of actions at different times in the round (something inherited from HERO trying to "simulate" speedsters - which in retrospect was dumb as hell because that's not how speedsters work in comics) really massively contributed to the bogging down everything. The fight was a hell of thing, but it would be basically one issue of a comic book, if the entire issue was one long fight. It was one of those games where you did like different damage with different modifiers with different moves and stuff and created all sorts of analysis paralysis situations. I'd expected the whole thing to take maybe 2-3 hours tops, including the rest of the adventure and another, smaller fight.
It remains the only time my group has ever said "We are never playing this system again..." after a single session.
