As a non manga reader I believe the arrows all do the same thing. Bringing out a person's full potential, but I'm guessing if your mind or will isn't strong enough it just kills you.
Also it was kira who gained a new ability because of the arrow. His dad brought it I think and it ended up going up his arm and giving him the ability bites the dust. So I'm guessing if an arrow pierces a person with a stand it would just give them another ability but if it touches the stand itself they would evolve, but if the person doesn't have a strong soul or mind it would just go berserk like chariot requiem.