I voted "Never played a video game that was just a dream", but I have to stress the word "JUST" in that sentence. And before I get to explain myself, I'll have to link to
this small list that really should've been in the OP (thanks, google).
Now...I did play SMB2 (though only a few levels), as well as Link's awakening, so I guess I should explain my answer:
-I've only learned in this thread that SMB2 is actually a dream. Since I've played quite a bunch of other games I never finished, I could've played more games like that. But as it's already mentioned: SMB2 isn't exactly a story-heavy game. And you can toss and turn it how you want, but no matter how lame "it was all just a dream" is to reset the board, non-story driven games don't suffer from it. In fact, if you think about it, there are plenty of game where the story is so tacked on it's funny when you think about it (
notorious example).
-on the other hand, you have dream levels in games like batman: Arkham Asylum. Or in Bart Simpson's arcade game. These sequences are awesome, and anything but tacked on. Heck...I guess the parts of silent hill where you fight enemies are sort of dream sequences. I take it we don't want to get rid of those either, right?
-and then there's Link's awakening. Yes, as a child, it came as somewhat of a surprise when I discovered that in that shrine about five or six dungeons in (or ten hours or so...I wasn't that good at gaming then
), but it's not like there weren't hints just about EVERYWHERE. It didn't made the relationships with the people on the island less engaging afterwards. More the opposite: it made them more valuable exactly because the time I spent with them gave it meaning, no matter if it was a dream or not (of course this would've been totally different if all those hints were absent and the dream-thing was only revealed at the end).
So...in the end, I haven't played a game that turned out to be a dream
in the tacked-on fashion that you describe. I play lots of abstract games that could very well turn out to be dreams. It won't affect my mood, as I'm not roleplaying. And I haven 't played FF X, so I can't comment on that (well...aside from a "it's a final fantasy game...this sort of convoluted soap series bullshit is exactly what you like in this sort of game...right?")
ending note: say...isn't the entire assassin's creed series build into dreams? The cutscenes somewhat try to camouflage it by throwing difficult words and technology around, but it basically comes down to that.