The significance of a realistic and consistent narrative is completely dependent on how much the game itself emphasizes its own story as a means of attracting/enticing the player. If I'm playing a tetris knock-off, you can guess that I'm likely not gonna care squat about whether any of the characters are behaving naturally. It'll just be awkward, and I'll deal with it because I'm more concerned about the blocks and THEIR movements.
The games you mentioned generally try to deliver a "valued" story experience (I say "valued" because the creators developed it with the viewpoint that some of the value you are purchasing when buying the product is a story, in addition to a gameplay experience). In something like those, I would say that it is critical, for the story to be successful, that the story be as coherent and consistent as possible. If you confuse or irritate the player while they are playing a game, then their immersion in the story breaks. The best games are able to maintain an ongoing immersive experience that makes people lose track of time (*cough cough* Skyrim *cough cough*).
That said, if you have a story that fails on occasion in this respect and jolts the player out of the experience so that they can ponder something strange such as you describe, it doesn't necessarily mean that it is a "bad" video game. After all, any of the other components of the game could help it be a positive experience for them: art, music, gameplay, level design, control interface, voice acting, or even just the general plot. Some hiccups every now and then in the story will detract, but not necessarily ruin, the story element of the game.
What WILL be a problem is if there are gaping holes in the story that make you wonder at the motivations of the characters or leave you completely confused as to what/how things are happening. It is here that you will confuse a player, leading to irritation at the story, and a soon-to-be dissatisfied player. To avoid this, make sure that, if you have a story in your game, the gameplay complements the story just as much as the story complements the gameplay. The player shouldn't be doing things that don't make sense within the context of the story. Likewise, the story shouldn't go along paths that don't make sense with what the gameplay entails.
After checking that the two are balanced, you will need to make sure that each individual segment of the game (with story-gameplay-pairs already balanced) are then balanced against each other so that successive tasks make sense in light of the previous tasks. It's necessary to line up the story this way so that the story proceeds in a fashion that follows an easy-to-follow narrative and it's necessary for the gameplay because the nature and difficulty of the tasks presented to the player need to align themselves with both the difficulty curve & the diversity of gameplay experiences the player expects (example: if you have several different activities in the game, don't make the first 3 tasks given to the player all be the same. You will need to showcase all the experiences in store for them so that they will wish to continue).
