Bill Russell once joked that "the more time that goes by since I retired, the better I was".
Guys who retire see players in the league who are nowhere near as good as they were, and they naturally think that they can go out there and compete, and they even mentally see themselves doing just that.
In most cases, I actually don't care if a guy wants to come back after "retiring". It's his life. It's his reputation. It's really none of my business what he does with it. In major league sports, the team will tell you when you can't play any more. But hey, if they're willing to sign you and let you play, I say have at it.
A previous comment is 100% correct that boxers are particularly notorious for doing this. To me, THAT'S when it gets sad.
In 1972, just after he came to the Mets, Willie Mays hit a foul ball that my Dad caught at Shea Stadium. You think we cared if he was 41 years old at the time?
We don't own anyone's talents except our own. Because I don't let anyone else tell me what I can and can't do, I recognize that I don't get to make those decisions for anyone else. If you're a pro athlete, your body will (eventually) tell you what you can and can't do.
If a guy wants to put whatever he thinks is his reputation on the line by trying to hang on "too long", it doesn't cost me anything. But for the most part, a guy can play until they find someone who's better that they can replace him with.
Having said that, I am sick of hearing about Favre every year. But that's because of the way he goes about it, not because I think he should or shouldn't retire. I like watching him play.