Summary
I don't think 'fun' is essential, but I think that 'worthwhile' is.
Detail
I don't want to waste two to eight hours of my life doing something which is worthless - like watching a movie that was at the mid-point between "ok, I guess" and "so bad it's good", I never want to think "well, that's two hours of my life I'll never get back".
Sure, most of the time a worthwhile game is also a fun game. Whether it be a beer and pretzels, laugh a minute fantasy, or angst ridden post-apocalyptic horror - you can still laugh about it with friends after the session. Occasionally though, you come across a game that is not fun, leaves a really bitter taste in your mouth, but which ultimately you feel was worth time.
Example from personal experience
For me, that was a game based on the Salem Witch trials. It was a freeform ('theatre style' LARP IIUIC) for around 40 players, intended to be as close to historically accurate as possible. Part of the game contract was that, even more than usual, people try to stay in character and put aside modern mores and values, in a serious attempt to think and react as genuinely as possible to the situation at hand.
The results were scary, very scary.
As in real life, it started off with just a few silly girls making trouble, but it quickly snowballed into suspicion, recrimination, accusation and counter-accusation which spread through almost everyone in the 'village'.
As one of the elders, and one of the three judges at the trial, I felt under intense pressure to conform, even though I was written to be the most rational thinker of the three. I presided over a mockery of a trial, where we let the 'spectral evidence' stand despite churchmen from outside the village trying to persuade us otherwise, I allowed 'witnesses' to be tortured in my presence by one of my fellow judges, and I worried that one word out of place could have me hauled before the bench myself.
Ultimately we rendered a very similar judgement to that which historically occurred. We didn't contrive this result, it wasn't consciously engineered to fulfil character goals, we didn't even have goals listed on our character sheets that might make the historical outcome more likely, the decision just evolved out of a situation which was expertly set up.
No-one could call this a 'fun' game. It was truly harrowing, it made me question what it is to be "the only sane person in the asylum" and wonder how far I would go to fit in if my life depended on it.
I can't say that I took any profound psychological insights from the game, but it did make me think. Nor is it the sort of game I'd want to play every week, or even every year, but an occasional game that really has such an intense negative emotional impact can be good to remind us that we mostly live pretty happy, uncomplicated lives.
For those who are interested, the game was called Delusions of Satan and I played it at a Gencon UK, in the mid noughties. It was written and run by Mystery In Mind, who provide their own summary of the game on the past events page of their website.
Sadly Mystery In Mind is no more, but their wabsite was archived at the Wayback Machine. Links have been updated.