The following contains spoilers for Fate/Zero and discusses lore from Fate/Stay Night.
When audiences were introduced to the Holy Grail War in 2004's Fate/Stay Night, it was the fifth war that had happened in Fuyuki, Japan, where it is always held (except when it isn't). And the promise is always the same: defeat the other six Masters and their Servants, and you will win the Holy Grail and grant whatever wish you desire.
Yet despite the myriad spinoffs, prequels, and the alternate universe takes on the franchise, the end of the Grail War rarely ends with someone granting a wish. It's typically a little more complicated, but with five grail wars in the original timeline, there had to have been someone who got their wish granted.
The Origins of the Grail War
The goal of the Holy Grail War is just one expression of a much larger part of Kinoko Nasu's fantasy universe as told through Fate, Tsukihime, and Garden of Sinners. In this world of modern magic hidden behind the scenes of the real world, all magi possess the same goal: to reach The Root, another name for the Akashic Records, a record of all things in existence. The Grail War is a ritual meant to reach the Root.
So when the war started, it wasn't actually a war at all. The First Grail War of 1810 was a meeting of three powerful mage families: The Tohsakas, the Matous, and the Einzberns, all familiar names to those who have played/read/watched Fate. They met to perform a ritual in order to reach the Root together but discovered that there could only be one winner.
So there was no technical winner of the first "war," save for the outside magi that were brought in to conduct the ritual, who granted their own wishes while the families argued. The ritual would recommence in 60 years, and it was an even bigger disaster. The Second Grail War of the 1860s was an unsupervised bloodbath that resulted in no winner once again.
Enter, The Church
Once it was clear that the Grail War couldn't function unsupervised, the mages turned to the Holy Church, an enemy of the magi, who supervised to keep an eye on things. The threat of the Grail's complex inner machinations is too dangerous not to keep a watchful eye on. As such, The Third Holy Grail War of the 1930s is considered the first true Grail War.
And even then, it was a disaster, as the Einzberns were so worried that they would lose that they manipulated the ritual to summon Angra Mainyu, a god of the Iranian faith of Zoroastrianism. For those who are out of date with their middle-eastern religious lore, it basically meant that they summoned an evil spirit to be their servant, and it messed everything up.
By the time the Grail War was over, Angra Mainyu has been absorbed back into the grail once all was concluded and corrupted the entire thing. So if you've ever wondered why Servants in Fate stories that are historically evil are still referred to as "Heroic Spirits" it's because the Grail was corrupted.
Enter, Kiritsugu Emiya
In 1994, the Fourth Holy Grail War is held, and so begins the events of Fate/Zero, where most anime audiences either got into the series or came off the heels of Fate/Stay Night. And while either Zero or Stay Night serves effectively as a reveal of the Grail's true nature, Zero is not only set first but hammers home the tragedy of the War so well.
Kiritsugu fought hard to defeat the other masters and his arch-rival Kirei Kotomine, but when he finally won the Grail, it wasn't at all like he imagined. His altruistic wish of ending all conflict on earth proved impossible, at least how he imagined it. The Grail revealed that the wish could not be achieved through a method that Kiritsugu couldn't himself imagine.
The Grail would make his wish a reality, but only by killing to a degree that would be impossible for a normal flesh-and-blood human. Kiritsugu, having fought so long for nothing, decided to deny the Grail and try to destroy it, to which it responded in anger by destroying a portion of Fuyuki. And so once again, the war ended incomplete and in failure.
The Purpose of the Grail
Is it even worth reciting how the Fifth Holy Grail War went? With that story, there are three different paths to take, and it still doesn't change the fact that the Holy Grail is a lie; a monkey paw doled out to offer a shortcut to those that have contributed their pound of flesh. And despite being called a wish-granter, that is sort of a false advertisement.
As was said at the beginning, the purpose of the Grail War was to perform a ritual that would allow mages to reach the Root, something every mage in the world strives towards. The Servants in Fate always talk about their own wishes, but it never matters because they aren't meant to achieve their wishes. They are meant to die as sacrifices for the Grail.
Even if the wish-granting device wasn't a cosmically terrifying scam, the lore of Fate/Stay Night proves beyond a shadow of a doubt that none of the participants are capable of playing fair. Every single Fate story has some sort of manipulation of the rules, like Assassin from Stay Night being a servant of Caster, whose Noble Phantasm literally is called "Rule Breaker."
And by the end of every Fate story, it's never really about what the Grail truly is or what it offers, but instead what it takes from the characters; and what the characters take away from it. No matter what, by the end, the heroes rarely ask for their wish to be granted, because it simply isn't worth it. Better to destroy it than to risk feeding it more.