I mean, what players ever go into a game with the goal of making their characters fail?
I think the point is that win/lose isn't the way a lot of people look at RPGs.
Many players just want to play a character and have a good time doing so. They don't particularly want to "win the game" and maybe even don't necessarily care about how successful they are so long as the character is fun to play (which, depending on the RPG, may require a certain level of success, or not).
They're neither playing to win, nor playing to fail. This isn't uncommon either - it's a huge proportion of people playing RPGs. Many games are designed in such a way, too, that your character inevitably failing, in some serious ways, at times, is a big part of the game. This is particularly true with modern RPGs - PtbA and Resistance-based ones for example, it's very significant.
Your entire post shows a somewhat myopic/grognardian approach to RPGs, though, and perhaps a very limited experience of RPGs with modern design principles. You're being skeptical about and surprised by stuff that's absolutely routine in a number of RPGs. You don't have to take people's word for this stuff working - you could easily go find someone playing these sort of RPGs and see that it works.
Still puts a player into a position of trying to kill off her own character, though, which seems utterly counterintuitive vis-a-vis the player's primary goal, that being advocacy for the PC.
This is sort of a good example of the myopia (sorry to use this word, but all the other ones I can find seem worse, as they make intentional, whereas this seems accidental) or limited experience here. A player's primary goal isn't necessarily "advocacy for the PC". A player's
primary goal, across my entire time playing RPGs, has almost always been "having fun". Only some players operate as basically an attorney trying to get their character the best possible deal continuously, because that's their fun. Many players, because they are there to have fun, may be quite happy to see dire consequences, even death, befall their PC, if that contributes to the overall fun of the game (especially if making/getting a new character doesn't suck). I actually think this attitude is not uncommon in older-school RPGs too, where some people happily shrug off their PCs dying, and don't necessarily do everything they can to stop it.
I mean, just look at the recently released (and excellently-designed)
Heart RPG. The top-tier upgrades for your character, in that, the "Zenith" abilities, pretty much all take your character out of the game permanently. Some are even purely negative from the character's perspective, like they just die horribly, not even in burst of glory or something (though that is also usually an option, depending on the class). They will likely be cool, and memorable, though, as horrible as they are for the PC.
Further in Heart, your character cannot die unless you say they die. They can become completely unplayable (due to piling up Major Fallout, which is very inconvenient, sometimes even crippling), sure, but they won't die until you say so, because you can only sustain Major Fallout from the dice, to get the Critical Fallout (which is how you likely die, if it's not from using a Zenith ability), you have to decide to suffer it - which removes two Major Fallouts. Sometimes you'll survive Critical Fallout, but not very often (and not usually without help).
Don't get me wrong - I totally understand "zealous advocate for your PC" as an approach. It's kind of how I play D&D myself when I'm a player. But it's not how I play all RPGs, and I can't even remember a time when I wasn't willing to see bad things happen to my PC if it was cool overall. Indeed many of my fondest memories of RPGs involve pretty horrible things happening to my PCs, but which were extremely funny or advanced the story, and many of my worst experiences have been with players (or myself!) operating as excessively zealous advocates for their characters, and the absolute worst experiences all involve players who were "playing to win", in the sense that they played the RPG we were playing exactly like it was a non-RPG game, with a win/lose condition, and they would "win" at all costs, even if it meant ruining the game for absolutely everyone else involved. That's the classic determinant of what a "munchkin" was, too - that they thought RPGs had "winners" and "losers", and they were determined to be the "winner", and the rest of the PCs and the DM would be the "losers".