Blasters are the easiest to get right, as all that is required are flashy AOE and projectile spells. However, my biggest gripe is that most designers tend to start with an extremely basic fireball as the first ability. It typically is a very basic projectile spell, but most designers handle it poorly.
The way most basic fireball spells are designed they simply toss a flame at the target which disappears on impact. The problem with this is that it isn't satisfying in the slightest.
That is a satisfying fire spell, notice how it has a sort of breath and release to the animation. Similarly, the thunder spell is a viceral snap. Comparing that animation to the more recent Fire Emblem Awakening magic animations makes me quite disappointed.
On the more insidious side of things, curse spells tend to be very... disappointing in general. Usually this is because the effects are very boring, simple debuffs most of the time. Maybe a damage over time ability. In a lot of single player RPGs they tend not to be a viable build by themselves. Either because bosses are flat out immune to their effects or the effects that you do get are completely negligible in a boss fight.
Take a poison spell, in most RPGs this spell tends to do damage that would be dangerous to a player, but not any monsters you would run into. It is especially useless against bosses since, presuming you can actually inflict poison on that boss, the damage is miniscule compared to the boss's health pool.
However, curse spells bring us effects like this. Which are hilarious.
It is very rare for curse spells to be viable, much less fun to use. However, I have seen bits of hope here and there. Pokemon, even though it's system isn't magic is a very good example of how status and debuff spells should operate. Poison damage scales with the HP of the target. Paralysis and confusion can be used to set up other strategies that take a while to implement.
Also of note are healing and Buff spells, but I rarely see games that mess these up. Healing is easy, lots of light effects and HP regen, either instant or over time, and you have a pretty good base. Buffing effects simply make other classes more effective. For more effect have the spells apply some sort of visual effect on the target, such as making the target bigger or giving it an aura of some sort.
As of late, I have been noticing some games have been blurring the line between magic and non-magic effects into a universal classification of "Skills." Particularly, in the JRPG genre. In most cases I tend to find that this makes the magic abilities extremely bland, or maybe it's just that the non-magic effects are outshining them.
My biggest issue is that while certain games have executed certain diciplines of magic well, I have yet to find a game that manages to do them all well. The closest I have gotten is the Etrian Odyssey series.