The best way of dealing with huge AI stacks is, not surprisingly, to smash the opposition before they've had a chance of getting a vast army together. Not that that helps in this instance.
As for artillery of all sorts, I too have noticed that enemy artillery withdraws fairly often but mine never do unless the unit being attacked was weaker than mine and duly got beaten down to the point where one more hit from my unit would have brought the target below the damage threshhold - and my unit won that combat round. There is no damage threshhold for an attacking artillery piece, so in every fight the attacker can be killed but the defender cannot be.
It is absurd that if, for example, you are attacking a strong unit with a catapult, the combat odds show something like "Retreat odds 99%". In fact, this means that your catapult has only 1% chance of winning and withdrawing, and a 99% chance of being killed.