Warhorses will often refuse to charge a dense formation of spearmen or bayonets. They'll turn or stop if possible, especially if not well trained. [Here is a good post on warhorse training] (https://www.reddit.com/r/AskHistorians/comments/1d7tu9/what_was_the_training_like_for_war_horses_during/c9nznmj)

However, Infantry will very often waver far before the horses actually make contact. Horses are large and a cavalry charge is extremely intimidating. Since we're on the subject of movies, one movie (silly fantasy though it may be) that gets this right is the [Return of the King] (https://www.youtube.com/watch?v=EmTz7EAYLrs&t=6m2s) In this clip you can see that the infantry starts running and isn't actually wielding their weapons right before contact. This is the way most successful cavalry charges end, but if the infantry doesn't break, it will usually defeat the cavalry.

The Infantry breaking from contact is much more likely to occur if an Infantry unit can be attacked from an unexpected direction. Since Cavalry are faster, they can often maneuver to flank infantry. Where that's impossible, disciplined cavalry can try feigned retreats and try to lure infantry into breaking ranks, as happened at the Battle of Hastings.

Generally, a direct charge at a formation like a shield wall, line of spearmen or napoleonic era square is a bad use of cavalry, however a charge at the right moment into a disorganized or demoralized foe will usually result in an overwhelming victory for the cavalry.